Pin Me

Microsoft Excel: Control Settings For Every New Workbook and Worksheet

written by: Mr Excel•edited by: Tricia Goss•updated: 7/10/2008

Problem: Every time that you start a new workbook or insert a new worksheet, you always make the same customizations. These may include setting print scaling to fit to one page wide, setting certain margins, adding a “Page 1 of n” footer to the worksheet, as shown in Fig. 75, making the heading row to be in bold text, and so forth.

  • slide 1 of 2

    See all Microsoft Excel tips

    Strategy: There are two files that 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.xlt or sheet.xlt. Any time that you choose either the New Workbook icon, as shown in Fig. 76, or the Insert Worksheet command, the new book or sheet will inherit the settings from these files.

    Follow these steps to create book.xlt.

    1) In Excel, open a new blank workbook by using the New icon in the Standard toolbar.

    2) Customize the workbook.

    Feel free to make adjustments to any of the following:

    • File – Page Setup

    • File – Set Print Area

    • Cell Styles

    • Format – Cells

    • Data – Validation settings

    • Number and type of sheets in the workbook

    • Window view options from the Tools – Options – View tab

    3) Decide where you want to save the file. This can be either in the XLStart folder (generally C:\ProgramFiles\Microsoft Office\Officenn\XLStart) or in the Alternate Startup folder. (Find or setup an Alternate Startup folder on the Tools – Options – General dialog.)

    4) Select File – Save As. In the Save As dialog, Save as Type dropdown, choose Template, as shown in Fig. 77.

    5) Browse to the XLStart folder (see Step 3).

    6) Save the file as Book.xlt

    Result: All subsequent new workbooks will inherit the settings from

    this file.

    Additional Information: You can also set up a workbook with one worksheet. Save this workbook as Sheet.XLT. All inserted worksheets will inherit the settings from this file.

    Summary: Rather than constantly setting the same settings for all new workbooks, save your favorite settings in either book.xlt or sheet.xlt in the XLStart folder. All new workbooks will inherit these settings.

    See all Microsoft Excel tips

  • slide 2 of 2


    Fig. 75Fig. 76Fig. 77