Chapter 6: Understanding Excel Files and Templates
6
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 you save a workbook created from a template, Excel displays
the Save As dialog box so that you can give the workbook a different name if you want to.A custom template is essentially a normal workbook. 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 the user enters.If your template contains macros, it must be saved as an Excel Macro-Enabled Template, with an .xltm extension.
Locking Formula Cells in a Template File
If novices are going to use the template, you might consider locking all the formula cells to make sure
that the formulas aren’t deleted or modified. By default, all cells are locked and can’t be changed
when the worksheet is protected. The following steps describe how to unlock the nonformula cells:- Choose Home ➪ Editing ➪ Find & Select ➪ Go To Special. The Go To Special dialog box
appears. - Select Constants and click OK. This step selects all nonformula cells.
- Press Ctrl+1. The Format Cells dialog box appears.
- Select the Protection tab.
- Remove the check mark from the Locked check box.
- Click OK to close the Format Cells dialog box.
- Choose Review ➪ Protect ➪ Protect Sheet. The Protect Sheet dialog box appears.
- Specify a password (optional) and click OK.
After you perform these steps, you can’t modify the formula cells unless the sheet is unprotected.Saving your custom templates
To save a workbook as a template, choose File ➪ Save As ➪ Browse and select Excel
Template (*.xltx) from the Save as Type drop-down list. If the workbook contains
any VBA macros, select Excel Macro-Enabled Template (*.xltm). Save the template in
your Templates folder—which Excel automatically suggests—or a folder within that
Templates folder.