Creating Custom Templates

The book.xltx and sheet.xltx 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.xltx, the workbook’s default name is Sales Report1.xlsx. 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.

If your template contains macros, it must be saved as an Excel Macro-Enabled Template, with an XLTM extension.

To create a custom template, you simply need to build a new workbook, add any headings, formatting, and formulas you desire, and then save it as a template. You can start this process from scratch by opening a new, blank workbook, or you can refine an existing built-in template. Either way, you should follow the same process of perfecting your workbook until it's ready for template status. Here are some tips:

Clear out the junk

Your template should be a blank form politely waiting for input. Clear away all the data on your template, unless it's generic content. For example, you can leave your company name or the worksheet title, but it probably doesn't make sense to have sample numbers.

Assume formulas won't change

The ideal template is one anyone can use, even Excel novices who are too timid to edit a formula. If you have a formula that contains some data that might change (for example, the sales commission, interest rate, late fee, and so on), don't type it directly into your formulas. Instead, put it in a separate cell, and use a cell reference within the formula. That way, the person using the template can easily modify the calculation just by editing the cell.

Don't be afraid to use lists and outlining

These features are too complicated for many mere mortals but they make spreadsheets easier to use and more powerful. By putting these advanced frills into the template, you ensure that people can use them in their spreadsheets without having to learn how to apply them on their own.

Turn off worksheet gridlines

Many templates don't use Excel's gridlines. That way, it's easier to see custom borders and shading, which you can use to draw attention to the important cells. To turn off gridlines, select View => Show/Hide => Gridlines.

Add the finishing touches

Once you have the basicstitles, captions, formulas, and so onit's time to create a distinct look. You can add borders, change fonts, and inject color. (Just remember not to go overboard with cell shading, or the output may be impossible to read on a black-and-white printer.) You may also want to tweak the paper size and orientation to ensure a good printout.

Delete extra worksheets and assign good names to the remaining worksheets

Every workbook starts with three worksheets, named Sheet1, Sheet2, and Sheet3. The typical template has only one worksheet, and it's named appropriately (such as Expense Form). For information about deleting and renaming worksheets.

Consider adding custom macros to make a really slick spreadsheet

For a real treat, you can build a toolbar with custom macros (and even attach them to custom buttons) that perform related tasks. For more information about how to build macros (and even attach them to custom buttons) that perform related tasks.

Saving your custom templates

To save a workbook as a template, choose Office button => Save As and select Template (*.xltx) from the dropdown list labeled Save As Type. If the workbook contains any VBA macros, select Excel Macro-Enable Template (*.xltm). 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, choose Office button => Open to open and edit the template.