Thursday, March 27, 2008

Working with the Default Templates

The term default template may be a little misleading. If you haven’t created your own template files to control the default settings, Excel uses its own internal settings—not an actual template file. In other words, Excel uses your template files to set the defaults for new workbooks or worksheets, if these files exist. But if you haven’t created these files, Excel is perfectly happy to use its own settings.

Using the workbook template to change workbook defaults

Every new workbook that you create starts out with some default settings. For example, the workbook has three worksheets, the worksheets have gridlines, text appears in the fonts specified by the default document template. columns are 8.43 units wide, and so on. If you’re not happy with any of the default workbook settings, you can change them.

Making changes to Excel’s default workbook is fairly easy to do, and it can save you lots of time in the long run. Here’s how you change Excel’s workbook defaults:

  1. Open a new workbook.
  2. Add or delete sheets to give the workbook the number of worksheets that you want.
  3. Make any other changes that you want to make, which can include column widths, named styles, page setup options, and many of the settings that are available in the Excel Options dialog box. To change the default formatting for cells, choose Home => Styles => Cell Styles and then modify the settings for the Normal style. For example, you can change the default font, size, or number format.
  4. When your workbook is set up to your liking, choose Office button => Save As.
  5. In the Save As dialog box, select Template (*.xltx) from the box labeled Save As Type. If your template contains any VBA macros, select Excel Macro-Enabled Template (*.xltm).



  6. Enter book for the file name.
    Excel will offer a name such as Book1.xlt. You must change this name to book.xlt (or book.xltm) if you want Excel to use your template to set the workbook defaults.
  7. Save the file in your \XLStart folder.
    The \XLStart folder may be located in either of these directories:

    C:\Documents and Settings\\Application Data\Microsoft\Excel\XLStart
    C:\Program Files\Microsoft Office\Office12\XLStart


  8. Close the file.

After you perform the preceding steps, the new default workbook is based on the book.xltx (or book.xltm) workbook template. You can create a workbook based on your template by using any of these methods:

  • Press Ctrl+N.
  • Open Excel without first selecting a workbook to open.
  • Choose Office button => New and choose Blank Workbook.

If you insert a new worksheet into a workbook that’s based on the book.xlxt template, the new worksheet will notuse the customized settings specified in the template. Therefore, you may also want to create a sheet.xltx template, which controls the settings for new worksheets.

If you ever want to revert to the standard default workbook, just delete the book.xltx file.

Using the worksheet template to change worksheet defaults

When you insert a new worksheet into a workbook, Excel uses its built-in worksheet defaults for the worksheet. These default settings include items such as column width, row height, and so on.

If you don’t like the default settings for a new worksheet, you can change them by following these steps:

  1. Start with a new workbook and delete all the sheets except one.
  2. Make any changes that you want to make, which can include column widths, named styles, page setup options, and many of the settings that are available in the Excel Options dialog box.
  3. When your workbook is set up to your liking, choose Office button => Save As.
  4. In the Save As dialog box, select Template (*.xltx) from the Save As Type box.
  5. Enter sheet.xltx for the filename.
  6. Save the file in your \XLStart folder.
  7. Close the file.
  8. Close and restart Excel.

After performing these steps, all new worksheets that you insert by using any of these methods will be formatted like your sheet.xltx template:

  • Clicking the Insert Worksheet button (next the last sheet tab).
  • Choosing Home => Cells => Insert => Insert Sheet.
  • Pressing Shift+F11
  • Right-clicking a sheet tab, choosing Insert from the shortcut menu, and choosing the Worksheet icon in the Insert dialog box.

Editing your templates

After you create your book.xltx or sheet.xltx templates, you may discover that you need to change them. You can open the template files and edit them just like any other workbook. After you make your changes, save the file, and close it.

Resetting the default workbook and worksheet settings

If you create a book.xltx or sheet.xltx file and then decide that you would rather use the standard default settings, simply delete the book.xltx or sheet.xltx template file—depending on whether you want to use the standard workbook or worksheet defaults—from the XLStart folder. Excel then uses its built-in default settings for new workbooks or worksheets.

You can also rename or move the template files if you’d like to keep them for future use.

No comments: