Excel: Control Settings for Every New Workbook and Worksheet

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: Every time I start a new workbook or insert a new worksheet, I always make the same customizations, such as setting print scaling to fit to one page wide, setting certain margins, adding a "œPage 1 of n" footer to the worksheet, making the heading row bold, and so forth. How can I have these settings applied to every new workbook or worksheet?

Strategy: Two files control the defaults for new workbooks and inserted worksheets. You can easily customize a blank workbook to contain your favorite settings and then save the file as book.xltx and sheet.xltx. Then, any time you either click Ctrl+N for a new workbook or insert a worksheet, the new book or sheet will inherit the settings from these files. Follow these steps to create book.xltx:

  1. In Excel, open a new blank workbook with Ctrl+N.
  2. Customize the workbook as you like. Feel free to make adjustments to any of the following:
    • Page layout settings
    • The print area
    • Cell styles
    • Formatting commands on the Home tab
    • Data, Validation settings
    • The number and type of sheets in the workbook
    • The window view options from the View tab
  3. Decide where you want to save the file. This can be either in the XLStart folder (generally C:\Program Files\Microsoft Office\Officenn\XLStart) or in the alternate startup folder. (See "œHave Excel Always Open Certain Workbooks".)
  4. Select File, Save As, Other Formats.
  5. In the Save As dialog, open the Save as Type dropdown and choose Excel Template (*.xltx).
  6. Browse to the XLStart folder you specified in step 3.
  7. Save the file as book.xltx.

Results: All subsequent new workbooks created with Ctrl+N will inherit the settings from the book.xltx file.

Gotcha: Excel 1 through Excel 2003 had a "œNew" icon on the Standard toolbar and a "œNew"¦" icon on the File menu. While these icons sound similar, they are very different. The regular "œNew" icon will create a new workbook based on book.xltx. The "œNew"¦" icon leads to a panel where you can select a template from Office Online. This trick will not work with the File, New command in Excel 2010. You have to use Ctrl+N or add the old "œNew" icon to the QAT or ribbon. The problem is worse in Excel 2013, where the Blank Workbook tile offered on the Start Screen is equivalent to "œNew"¦" and will not load Book.xltx.

Additional Details: You should also set up a workbook with one worksheet and save this workbook as sheet.xltx. All inserted worksheets will inherit the settings from this file.

Additional Details: If you regularly create macros, save the files with the .xltm extension instead.

For more resources for Microsoft Excel