Creating a Default Workbook Template

You can create a default workbook template that defines the formatting or content of the new (blank) workbooks that open after you start Excel. Excel bases every new (blank) workbook that you open on the default workbook template. The default workbook template that you create replaces Excel’s built-in default workbook template.

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.

Follow these steps to create a default workbook template:

1. Create a new workbook.
2. Add or delete as many worksheets as you want to appear in the new workbook.
3. If you want, turn off the display of gridlines.
4. Apply the desired formatting, sheet names, text, styles, and so on.
5. Select a new theme for the template if you don’t like Excel’s default choice.
6. Click the Office button, choose Save As, and select Excel Template from the Save As Type drop-down list.
7. Windows XP: In the Save In drop-down list, locate an xlstart folder. Excel can use more than one xlstart folder and will open all files located in these folders on startup. The xlstart folders normally reside in the following locations: C:\Documents and Settings\Username\ Application Data\ Microsoft\Excel (where Username is your login username) and C:\ Program Files\Microsoft Office\Office 12 folders, respectively. Windows Vista: If the Folders window isn’t displayed, click Browse Folders, click Folders to display the Folders window, and then locate the xlstart folder. Excel can use more than one xlstart folder and will open all files located in these folders on startup. The xlstart folders normally reside in: C:\Users\Username\AppData\Roaming\Microsoft\Excel (where Username is your login name) and C:\Program Files\Microsoft Office\Office 12 folders, respectively.
8. In the File Name text box, type book.xlt.
9. Click Save.

All new (blank) workbooks that you create are now replicas of the book.xlt workbook that you saved.

You can always edit the book.xlt file or delete it if you no longer want to use it.

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 File menu and Save As.
4. In the Save As dialog box, select Template (*.xlt) from the Save As Type box.
5. Enter sheet.xlt 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 s will be formatted like your sheet.xlt template:

• Clicking the Insert Worksheet button (next the last sheet tab)
• 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.xlt or sheet.xlt 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.xlt or sheet.xlt file and then decide that you would rather use the standard default settings, simply delete the book.xlt or sheet.xlt 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.