By / Comments (37)

It may seem a trivial matter, but getting your Office applications set up correctly before you start working is a smart move. Think about all the times you have had to change formatting options on existing workbooks and tally up that time — it adds up.

Tech tips: 3 ways to make life easier in Excel.

Here are three tips that will enable you to reset Excel 2010’s default formatting. Set them once, and never again.

1. Setting Formatting Options for Workbooks, Part 1

Excel does not offer many options that allow you to set formatting defaults for your workbooks. However, you can work around this by modifying the formatting in a blank workbook, then saving it as the default template.

  1. Open Excel to a blank workbook.
  2. Format the blank file with all options desired. For example, set margins, cell color formats, or set up a header or footer. Make sure to remove any values you entered in cells to test formatting unless you want them to appear in every blank workbook.
  3. Once your changes are made, click on the File tab and choose Save As.
  4. From the “Files of type” drop-down list, select “Excel Template (*.xltx)” and change the file name to “Book.”
  5. Set the “Save in” location to the XLSTART folder. This folder is typically located in a path similar to C:Program Files/Microsoft Office/Office14/XLSTART. (Check out TechRepublic’s tip for quickly finding the XLSTART folder.)
  6. Click Save.
  7. Quit and re-open Excel. The blank workbook should contain the formatting you previously set.

2. Changing the Default Font for New Workbooks

We all have our quirks; that’s what makes us unique. One of these things can be the font you choose as your default. To change the default font used in new workbooks in Excel, follow these steps:

  1. Click on the File tab.
  2. Select Options at the bottom left.
  3. Under General options there is a section for “When creating new workbooks.”
  4. Here you can select the option to change the font and font size for all new workbooks.
  5. Click OK.
  6. Close and re-open Excel.

After you restart Excel, any new workbook you create will be set for the new font properties you selected. Note that existing workbooks will not be affected.

1. Setting Formatting Options for Workbooks, Part 2

Why does Excel always open up with three blank worksheets? If you’ve found yourself continually deleting those extra two, stop already. Excel offers a way for you to specify the defaults for new workbooks, which includes number of worksheets. Follow the steps below:

  1. Click on the File tab and choose Excel Options.
  2. In the General group, navigate to the section titled “When creating new workbooks.”
  3. You can change the number of sheets created in a new workbook by changing the “Include this many sheets” option.
  4. Click OK after making your selections.
  5. Close and re-open Excel.

Excel will use your options from now on when creating a new workbook, saving you time when setting up each new file.

For more on these and to find other tips, visit our tech tip library. Have any Excel or general Office tips to share? Post them in the comments section.

Tips are compiled and written by Vitalyst staff.

37 Comments
  • Michelle Kennedy

    Fabulous tips! I have a question though. I’ve done the above for 2010. When I start Excel, my template pops up (bravo!). While I’m still in Excel, I’d like another workbook, and so I go to File>New>Blank Workbook, and it pops up with the same old 3 sheet template with none of my colour settings. ARGH! This is frustrating to say the least.
    I know I can Ctrl+N and get my template, or go to File>New>My Templates, but that’s not what I want. I figure I should be able to change what template Excel pulls from to get a New Workbook while Excel is open.
    Please help!
    Thanks – Michelle

    Reply

    • admin

      admin

      Michelle,

      My apologies that this didn’t get posted sooner. Try the following and let me know how it works — otherwise, we can try something else:

      Create the workbook that you wish to use as a default.

      When the new default workbook is set up to your preferences, choose the File tab or Microsoft Office Button and then Save As | Excel Workbook.
      In the Save As dialog box, choose the Save As Type drop-down list, and select Excel Template (*.xltx).

      Name the file as “book.xltx” (without the quotes)

      The file needs to be saved in your XLSTART directory which is on your local C: drive. The location of this directory varies depending on your version of Windows and Microsoft Office; search your hard drive for the folder.

      After you save the template file, you can close it.

      Close Excel.

      Start Excel to see your new workbook.

      Now, every time you start Excel, the new blank workbook will be based on the template you created. In addition, when you click the New toolbar button (or press [Ctrl]+ N), the new workbook will be created from your template.

      If the XLSTART directory is on a network, you may not have permission to save files. Instead, you can create a startup directory on your own system with any name you want and store the book.xltx file in this new alternate startup directory. The directory name you choose doesn’t matter, but you will need to tell Excel where it is.

      To save your default workbook in an alternate directory:

      Create a new folder on your C: drive where you will store your book.xltx file.

      In Excel 2010, pick File | Options, and then click the Advanced category. In Excel 2007, click the Microsoft Office Button, choose Excel Options, and then choose the Advanced category.

      Under the General section, type the full path of the folder that you want to use as the alternate startup folder in the At startup, open all files in box.

      If a workbook with the same name is in both the XLSTART folder and the alternate startup folder, the file in the XLSTART folder opens.

      Caution: Because Excel will try to open every file in the alternate startup folder, make sure you specify a folder that contains only files that Excel can open and only files you want to see every time you start Excel.

      Reply

  • Dawn McCreary

    @Michelle Kennedy
    Michelle, did you ever find a resolution for your File / New question in Excel 2010. I’m having the same issue. I can make my template work for Book 1 or if I use Ctrl + n or if I add the new icon to the quick access toolbar. I also want it to work when my users click File then New. I hope you get this message and have a solution. I’ve been racking my brain for a week now.

    Reply

    • admin

      admin

      Dawn,

      My apologies that this didn’t get posted sooner. Try the following and let me know how it works — otherwise, we can try something else:

      Create the workbook that you wish to use as a default.

      When the new default workbook is set up to your preferences, choose the File tab or Microsoft Office Button and then Save As | Excel Workbook.
      In the Save As dialog box, choose the Save As Type drop-down list, and select Excel Template (*.xltx).

      Name the file as “book.xltx” (without the quotes)

      The file needs to be saved in your XLSTART directory which is on your local C: drive. The location of this directory varies depending on your version of Windows and Microsoft Office; search your hard drive for the folder.

      After you save the template file, you can close it.

      Close Excel.

      Start Excel to see your new workbook.

      Now, every time you start Excel, the new blank workbook will be based on the template you created. In addition, when you click the New toolbar button (or press [Ctrl]+ N), the new workbook will be created from your template.

      If the XLSTART directory is on a network, you may not have permission to save files. Instead, you can create a startup directory on your own system with any name you want and store the book.xltx file in this new alternate startup directory. The directory name you choose doesn’t matter, but you will need to tell Excel where it is.

      To save your default workbook in an alternate directory:

      Create a new folder on your C: drive where you will store your book.xltx file.

      In Excel 2010, pick File | Options, and then click the Advanced category. In Excel 2007, click the Microsoft Office Button, choose Excel Options, and then choose the Advanced category.

      Under the General section, type the full path of the folder that you want to use as the alternate startup folder in the At startup, open all files in box.

      If a workbook with the same name is in both the XLSTART folder and the alternate startup folder, the file in the XLSTART folder opens.

      Caution: Because Excel will try to open every file in the alternate startup folder, make sure you specify a folder that contains only files that Excel can open and only files you want to see every time you start Excel.

      Reply

  • Kurt

    I agree with Michelle. I would like to hit the new workbook button (or insert a worksheet) and get the formatting I desire. I was able to do this in Excel 2007 – but not in 2010.

    Reply

    • admin

      admin

      Kurt,

      My apologies that this didn’t get posted sooner. Try the following and let me know how it works — otherwise, we can try something else:

      Create the workbook that you wish to use as a default.

      When the new default workbook is set up to your preferences, choose the File tab or Microsoft Office Button and then Save As | Excel Workbook.
      In the Save As dialog box, choose the Save As Type drop-down list, and select Excel Template (*.xltx).

      Name the file as “book.xltx” (without the quotes)

      The file needs to be saved in your XLSTART directory which is on your local C: drive. The location of this directory varies depending on your version of Windows and Microsoft Office; search your hard drive for the folder.

      After you save the template file, you can close it.

      Close Excel.

      Start Excel to see your new workbook.

      Now, every time you start Excel, the new blank workbook will be based on the template you created. In addition, when you click the New toolbar button (or press [Ctrl]+ N), the new workbook will be created from your template.

      If the XLSTART directory is on a network, you may not have permission to save files. Instead, you can create a startup directory on your own system with any name you want and store the book.xltx file in this new alternate startup directory. The directory name you choose doesn’t matter, but you will need to tell Excel where it is.

      To save your default workbook in an alternate directory:

      Create a new folder on your C: drive where you will store your book.xltx file.

      In Excel 2010, pick File | Options, and then click the Advanced category. In Excel 2007, click the Microsoft Office Button, choose Excel Options, and then choose the Advanced category.

      Under the General section, type the full path of the folder that you want to use as the alternate startup folder in the At startup, open all files in box.

      If a workbook with the same name is in both the XLSTART folder and the alternate startup folder, the file in the XLSTART folder opens.

      Caution: Because Excel will try to open every file in the alternate startup folder, make sure you specify a folder that contains only files that Excel can open and only files you want to see every time you start Excel.

      Reply

  • melissa

    In 2010, I cannot get the path and the file name to work in the footer in the book.xltx file. Only the file name. Has anyone figured this out?

    Reply

    • Jen Sweeney

      Jen Sweeney

      Melissa,

      To be clear, are you looking to have the path and file name show up in every new workbook you create? If so, here are the steps:
      – Open a new workbook.
      – Make your modifications. To add path and filename to footer, to to INSERT | HEADER/FOOTER. In the Navigation section of the ribbon, select the Go To Footer button.
      – With your cursor in the footer, go to the HEADER & FOOTER TOOLS/DESIGN tab, which only appears when you have selected the header or footer, and click the dropdown arrow under FOOTER on the very left of the ribbon.
      – Choose the option for the entire path, which should start with “C:/” (without quotes)
      – Once you have made all the changes to your workbook, go to FILE | SAVE AS. Save the file as BOOK.XLTM, and save it in the following location:
      C:\Users\\AppData\Roaming\Microsoft\Excel\XLSTART
      – Quit Excel and re-open it. Every new workbook should now include the path and file name.

      If you have trouble finding the XLSTART folder, type the path in a Notepad file first, then copy and paste it into the Save As window.

      Good luck, and thanks for reading!
      Jen

      Reply

  • Tom

    Hi All,

    Has anyone had any luck on finding a way to alter the look of the file/new/workbook.

    I have set up several workbooks with custom grid lines and every time I use split screen, the default (New Workbook Sheet) turns all the modifications back.

    I haven’t been able to find where Microsoft hides the default “New Workbook” template.

    Thanks,

    Reply

    • Jen Sweeney

      Jen Sweeney

      Hi Tom,

      I’m not sure that I understand the issue. Have you set up your default workbook with the gridlines, and when you use split screen it wipes out the gridlines? Can you explain exactly what you are trying to do?

      Thanks,
      Jen

      Reply

  • Bonnie

    I have my default font set to Arial 10 (and have had it set that way for the appx 2 years I’ve had this computer). I can go to File | Options and confirm that this is still the setting.

    However, when I copy cells from one spreadsheet to another, the pasted cells switch to Calibri 11, even if both files are in Arial 10. This drives me crazy! I know it takes two seconds to switch it back, but I shouldn’t have to! I should never see Calibri at all, unless I’m opening a file created by someone else in that font!

    When I go to Font and click on Normal Font, it switches to Calibri 11. (And of course, there isn’t a Default button to push on the Font menu, to say that I want the current settings to be the default.) This suggests to me that there is some “default font” setting somewhere that is overwriting what is listed as the default font in File | Options. I can’t find it, though. Any thoughts? Where else can I look for hidden default font settings?

    Thanks! 🙂

    Reply

    • Jen Sweeney, Vitalyst

      Jen Sweeney, Vitalyst

      Hi Bonnie and thanks for reading the blog. I fully understand how a glitch like that can drive you nuts. Sure you could change it back every time, but why should you have to? The settings should work as intended, and I will do my best to get to the bottom of it.

      I need a few details first: Which version and Service Pack of Office? Which OS?

      Thanks,
      Jen

      Reply

  • Rachel

    Thanks for the instructions, they’ve really helped with new workbooks I’m creating. Is there any way to get the settings (particularly print margins) to stay when opening a workbook created by someone else? I always have to reset the margins so the file prints correctly in my office and I’d really like to cut out that step, it takes up so much time day after day.

    Reply

    • Jen Sweeney, Vitalyst

      Jen Sweeney, Vitalyst

      Hi Rachel,

      Thanks for your question. I checked into it and found that to control settings like print margins on workbooks created by others, you will need a macro. If you are a Vitalyst customer, you can call one of our Excel VBA consultants who will gladly help you get one set up. If not, you can try this tutorial from Microsoft — “Get in the Loop with Excel Macros“.

      Good luck!
      Jen

      Reply

  • TG

    Excel 2010 user here, tried to change my default formatting and the file that opens when I start Excel has my new format but if I try to open a new blank workbook, that opens with the old format. I can open my templates, personal templates but that is extra steps – any suggestions? Thanks!

    Reply

    • Jen Sweeney, Vitalyst

      Jen Sweeney, Vitalyst

      Hi TG: Thanks for your question. I’m not sure why it’s not working for you — which OS are you using? (8, 7, Vista, XP?) And please tell me the steps you took.

      Jen

      Reply

  • Mike

    Jen,

    I am using Office 2013 on Windows 7.
    I have tried to save book.xltx in both my XLSTART folder (C:\Users\[my user name]\AppData\Roaming\Microsoft\Excel\XLSTART which I found through a search in the VBE Immediate window by entering ? application.StartupPath) — and a custom start up location (set in the Advanced-General settings under Excel Options to C:\Users\[my user name]\Documents\Custom Office Templates).

    No matter what, Excel still does not open book.xltx automatically. I can browse to it and open it and everything works great in the file. But Excel just won’t open it.

    What am I doing wrong??

    Reply

    • Jen Sweeney, Vitalyst

      Jen Sweeney, Vitalyst

      Mike,

      My reply below to Luiggi may be the answer you are looking for.

      Jen

      Reply

  • Peter Andersen

    Hi,

    I have the same problem as many desribe here and else where on the web. When opening Excel my template is opened, but using CTRL+ N my template is not used. I have tried your guide, tried creating a new folder on the C:\ drive to, but no effect?

    Does anyone have a solution? I use Windows 7.

    I read a post where he said it was a language issue. My version of Excel is in danish.

    Regards,
    Peter

    Reply

    • Jen Sweeney, Vitalyst

      Jen Sweeney, Vitalyst

      Hello Peter,

      Which version of Excel are you using? If it’s 2013, see the comment to Luiggi below. Also, the past few versions of Excel have included a few buggy template behaviors, and it doesn’t seem as if they are high up on the fix-it list. They are:

        Start Excel: loads book.xltx

        Press Ctrl+N within Excel: loads book.xltx

        Click New on the Standard Toolbar (Excel 2003 or earlier) or Quick Access Toolbar (Excel 2007 or later): loads book.xltx

        Select Office > New (Excel 2007) or File > New (other versions), then Blank workbook: does not load book.xltx but just a blank workbook

      Hope this helps.
      Jen

      Reply

  • Luiggi Alci

    I would like to know how to do it on excel 2013, I did the same steps as show in this helpful tips but is not working.

    Reply

    • Jen Sweeney, Vitalyst

      Jen Sweeney, Vitalyst

      Hello Luiggi. For Excel 2013, Microsoft changed things up a bit. Here’s a how-to from the horse’s mouth (from Microsoft, that is):

      The blank workbook on the Start screen isn’t based on Book.xltx.

      In earlier versions of Excel, when you saved workbook settings you frequently used in a workbook template called Book.xltx that is stored in the XLStart folder (typically C:\Users\user name\AppData\Local\Microsoft\Excel\XLStart), this template would open automatically when you created a new blank workbook.

      When you start Excel 2013, the Start screen appears and Excel doesn’t automatically open a new workbook. The blank workbook you can click on the start screen is not associated with Book.xltx.

      To set up Excel so it automatically opens a new workbook that uses Book.xltx: Click File>Options, and then on the General tab, under Start up options, uncheck the Show the Start screen when this application starts box. The next time you start Excel, it opens a workbook that uses Book.xltx.

      Hope this helps.

      Jen

      Reply

      • Sherry

        Aha! Thank you!

        Reply

  • Joel

    Thanks for the great tip. I had already set the defaults and they were not working for anything other than the first cell. Oddly they were working when creating a new worksheet. 🙂 The template in the XLSTART folder did the trick nicely. Also if you are running 64bit Windows you might find it in the \Program Files (x86)\ folder.

    Reply

    • Jen Sweeney, Vitalyst

      Jen Sweeney, Vitalyst

      Joel,

      Glad we could help. Happy spreadsheeting!

      Jen

      Reply

  • Roger

    How do I get the Toolbar to remain at the HOME position. In 2010, the toolbar defaults bak to the FILE tab after every completed operation. I have to reclick the HOME tab every time to do repetitive steps on the worksheet. How do I get the HOME tab to remain open as the default area?

    Reply

    • Jen Sweeney, Vitalyst

      Jen Sweeney, Vitalyst

      Hello Roger,

      Thanks for your question. If you haven’t already found the answer, I may have it for you. If I have read your question correctly, I think what’s going on is that your ribbon is minimized, so that after you click to select an item on the Home tab or any other tab, the entire ribbon disappears. If this is the case, just right-click on the Home tab, and click to de-select “Minimize the ribbon” (if minimizing the ribbon was enabled, there will be a check mark to the left of the menu item text). The ribbon, and the tools you are using, will remain visible until you hide them again. (See screenshot at end of this reply).

      If you are ever in a pinch and cannot remember how to get the ribbon back, you can always right-click to get a contextual menu which includes formatting options. Another alternative is to use keyboard shortcuts for formatting. Microsoft has a useful list, which you can find here: http://office.microsoft.com/en-us/support/keyboard-shortcuts-in-excel-2010-HP010342494.aspx

      Hope this helps.

      Jen

      How to keep ribbon visible in Excel 2010)

      Reply

  • Douglas

    I have a 6 year old MacBook Pro with Snow Leopard installed. I work on Excel Spreadsheets on the Office for Mac 2011 installation. Under the Snow Leopard “System Preferences”>”Print & Fax”, I have set the printer that I have as the default printer. On each worksheet that I set up, I set the “Options”.”Page Setup”.”Format For” from “Any Printer” to my own printer – which is listed in the drop down.

    If I change the Page size to a particular size from A4 i.e. A5, having saved the document, subsequent openings of the workbook give me the Format I want for the A5. BUT every time I reopen the workbook, I have to reset the printer to my own printer as the system have reverted to “Any Printer”.

    Is there anything I can do to convince my workheets that I only have one printer and do not want to continually have to reselect it? I get a different print layout with “Any Printer and that is no good when printing my documents.

    Is it possible to change the “Format For” permanently?

    Reply

  • Angelia

    Is there a way to set a default date format in Excel 2010? When I enter a date it is formatted as m/d/yyyy. I always want my dates to be formatted as mm/dd/yy.

    Also, is there a way do disable the F1 button so that Help does not pop-up when you accidentally hit F1? (For some reason, I miss the F2 button quite a bit and end up having to wait on Help to start-up then close it so I can continue)

    Thanks!

    Reply

  • Rosie Fernandez

    Why do I have to reclick the HOME tab every time I do repetitive steps on the
    worksheet, It did not happen before…how can I fix it?

    Reply

    • Jen Sweeney, Vitalyst

      Jen Sweeney, Vitalyst

      Hello Rosie,

      Thanks for your question. I need a little more information in order to answer your question — are you saying that the Home tab options disappear after you perform an action? If so, you probably have the Ribbon collapsed. To fix this, right-click anywhere next to the menu names (Home, Insert, Page Layout, etc.) and select “Collapse the Ribbon” to clear the check mark and enable the Ribbon.

      Show the Ribbon in Excel

      Hope that helps.

      -Jen

      Reply

  • website

    Have you ever thought about publishing an ebook or guest authoring on other blogs?
    I have a blog based on the same topics you discuss and would really like to have you
    share some stories/information. I know my subscribers would value your work.
    If you’re even remotely interested, feel free to shoot
    me an email.

    Reply

  • Sally

    Hi Jen

    I am using Excel 2007 on Window 7. I tried to set file format to be default to Excel workbook but each time I quit and reopen Excel, it will fail back to Excel 97-2003 format.

    Please advise

    Reply

  • Anna

    What about if I wanted to change the default for the Number format. I would like the default for the Number format to have 0 decimals and use 1000 separator (,) every time.

    Reply

  • Danielle

    I’ve got my new Book template. Now when I open Excel, the 3 sheets have the font color that I want (gray instead of black – to save on ink). Unfortunately, any new worksheet I open reverts back to complete black font. How do I change the default New Worksheet template?

    Reply

    • Shaun

      You would need to save a single-tabbed worksheet, with all of the formats you want, as a template. This is similar to the workbook template, but in this case, you need to save the file as Sheet.xltx. That should work after you close Excel and open it again.

      Reply

  • Giuseppe

    hi, great post ! I have another problem … I would like to avoid that my changes on a workbook don’t affect the Others on opening. for example: i build a litte project, on opening my workbook i run a vba code to customize my workbook, hiding commandar bar and others, set size etc. so. when if i open another workbook it has the same changes, also if I coled my workbook that i customized. Excel semms to rembere the last changes and applies them to Others workbook. how can I avoid this ? have you any idea. thank for your attention.

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *