MrExcel.com - Your One Stop for Excel Tips & Solutions

Control Settings for Every New Workbook and Worksheet

August 23, 2021 - by

Control Settings for Every New Workbook and Worksheet

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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Bank Phrom on Unsplash