Customizing Excel defaults with templates

Applies to
Microsoft Excel 2000

Excel recognizes two templates that it uses automatically, even if you don't specify a template to use. These templates allow you to customize new worksheets that you insert and new workbooks that you create.

Customize new worksheets

You can create a template named Sheet.xlt to specify the settings for new worksheets. For example, if you don't want to use gridlines, you must turn them off for each worksheet individually. If you prefer all new worksheets that you insert to have gridlines already turned off, you can create a Sheet.xlt template that has gridlines turned off.

Customize new workbooks

To specify settings for all new workbooks that you create by clicking the New button on the Standard toolbar or by clicking the Workbook icon in the New dialog box, you can create a template named Book.xlt. For example, new workbooks have three worksheets initially, but if you prefer more or fewer worksheets, you can create a Book.xlt template with the number of worksheets you want.

Where to store templates

Depending on how you want to use templates, you can store them in the default locations, in personal use locations, or in shared locations. For more information about the template folders and their locations, type location of settings in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.

Default locations

For Excel to use the default templates Sheet.xlt and Book.xlt, you must store these templates in the xlstart folder. If you later want to stop using either of these templates and resume inserting blank worksheets and creating blank workbooks, delete the Sheet.xlt or Book.xlt file.

Personal use

Put templates you create for your own use in your Templates folder (for the location of this folder, type location of settings in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search). The Templates folder is where Excel puts templates you create if you don't specify a different location.

You can also put templates in your xlstart folder. Templates you put in your templates folder are available immediately, and templates you save to your xlstart folder are available as soon as you close the template, but if you copy a template to your xlstart folder you must exit and restart Excel to make the template available.

Shared use

To make templates available to your workgroup, save them to a shared network folder and then have everyone in the group specify this location as their alternate startup location. To set your alternate startup location, click Options on the Tools menu, click the General tab, and then type the path to the folder in the Alternate startup file location box. After you change this setting or add templates to this folder, exit and restart Excel for your changes to take effect.

 Note   Use the xlstart folder and the alternate startup file location only for templates and for other files that you want Excel to open whenever you run it. Excel makes all templates in these folders available, and tries to open all files of other types that are stored in these folders.

Accessing templates

Templates that you store in your Templates folder, xlstart folder, and alternate startup file location appear on the General tab of the New dialog box. Templates stored in other locations don't appear in the dialog box. Excel opens templates in other locations for edit, instead of creating new workbooks based on them.

 Tip    If you want your personal templates to appear on their own tab in the New dialog box, so that you can access more than one template that has the same file name, create a subfolder of the Templates folder and put your templates in this subfolder. Your templates will then appear on a tab with the same name as the subfolder.

For example, if you create a folder named Corporate in your Templates folder and move your templates into the Corporate folder, the New dialog box will then have a Corporate tab that lists your templates. You can add folders that become tabs only in the Templates folder, not in the alternate startup location.

For information and guidelines about places to store templates, type store templates in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.

How to create a template

  1. Create a new workbook and enter the data, formatting, and settings you want the template to have.
  2. On the File menu, click Save As, and in the Save as type box, click Template (*.xlt).
  3. Type a name for the template and then click Save. Excel automatically puts the template in the right location so you can use it the next time you create a workbook.

How to create a workbook from a template

  1. On the File menu, click New, and then click the General tab.
  2. Click the template you want, and then click OK.

Excel creates a new workbook based on the template and names it using the name of the template. For example, if your template is named Budget, the first workbook you create from the template is named Budget1, the second workbook is named Budget2, and so on. You can save the workbook with a different name like any other workbook.

 Note   If you're familiar with Word templates, you'll find that Excel templates work differently. Unlike Word templates, Excel templates don't remain attached to the workbook, and you can't reattach or reapply Excel templates to existing workbooks. Excel templates only affect a workbook when you create it, and the workbooks you create are independent of the template, and each other, from then on. Once you create a workbook from a template, you can modify any of the data and settings that the workbook inherited from the template, and your changes to the workbook have no effect on the template.

How to change an existing template

To change data and settings in a template, make sure you open the template file, not a workbook based on the template.

  1. On the File menu, click Open.
  2. In the Files of type box, click Templates (*.xlt).
  3. In the Look in box, click the folder that contains the template you want to modify (usually this is the Templates subfolder in your Windows Profiles folder).
  4. Click the template, and then click Open.
  5. Make your changes, and then save and close the template.

 Note   Changes you make to a template don't apply retroactively to workbooks that you've already created from the template. Your changes affect only new workbooks that you create from the modified template.

More information

For more information about creating and using templates, type templates in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.

Excel 2000 provides a set of ready-to-use templates that help you create business forms such as invoices and purchase orders. These templates are available on the Spreadsheet Solutions tab of the New dialog box when you click New on the File menu. The templates are also good examples of how macros and custom toolbars can enhance the capabilities of a template.

The following ready-to-use templates are available:

  • Expense Statement     An expense report form that you can customize for your company.
  • Invoice     A customer billing form that you can customize.
  • Purchase Order     A complete purchase order form that includes blanks for approvals and other data.

For more information about the ready-to-use templates, type spreadsheet solutions in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.