Creating Custom Templates

The book.xlt and sheet.xlt templates discussed in the preceding section are two special types of templates that determine default settings for new workbooks and new worksheets. This section discusses other types of templates, referred to as workbook templates, which are simply workbooks that you set up as the basis for new workbooks or worksheets.

Creating a workbook template can eliminate repeating work. Assume that you create a monthly sales report that consists of your company’s sales by region, plus several summary calculations and charts. You can create a template file that consists of everything except the input values. Then, when it’s time to create your report, you can open a workbook based on the template, fill in the blanks, and be finished.

You could, of course, just use the previous month’s workbook and save it with a different name. This is prone to errors, however, because you easily can forget to use the Save As command and accidentally overwrite the previous month’s file. Another option is to use the New From Existing option in the New Workbook dialog box. This command creates a new workbook from an existing one, but gives a different name to ensure that the old file is not overwritten. When you create a workbook that’s based on a template, the default workbook name is the template name with a number appended. For example, if you create a new workbook based on a template named Sales Report.xlt, the workbook’s default name is Sales Report1.xls. The first time that you save a workbook that is created from a template, Excel displays its Save As dialog box so that you can give the template a new name if you want to.

A custom template is essentially a normal workbook, and it can use any Excel feature, such as charts, formulas, and macros. Usually, a template is set up so that the user can enter values and get immediate results. In other words, most templates include everything but the data, which is entered by the user.

Saving your custom templates

To save a workbook as a template, click File menu and Save As and select Template (*.xlt) from the dropdown list labeled Save As Type. Save the template in your Templates folder—which Excel automatically suggests—or a folder within that Templates folder.
If you later discover that you want to modify the template, click File menu and Open to open and edit the template.

Ideas for creating templates

This section provides a few ideas that may spark your imagination for creating templates. The following is a partial list of the settings that you can adjust and use in your custom templates:

1. Multiple formatted worksheets: You can, for example, create a workbook template that has two worksheets—one formatted to print in landscape mode and one formatted to print in portrait mode.
2. Style: The best approach is to click Formet menu => Styles and modify the attributes of the Normal style. For example, you can change the font or size, the alignment, and so on.
3. Custom number formats: If you create number formats that you use frequently, you can store them in a template.
4. Column widths and row heights: You may prefer that columns be wider or narrower, or you may want the rows to be taller.
5. Print settings: Change these settings in the Page Layout tab. You can adjust the page orientation, paper size, margins, and several other attributes.
6. Header and footer: Use Page Layout View, and enter a custom header or footer.

You can, of course, also create complete workbooks and save them as templates. For example, if you frequently need to produce a specific report, you may want to create a template that has everything for the report except for the data you need to enter. By saving your master copy as a template, you’re less likely to overwrite the original file when you save the file after entering your data.