Excel 2010 Bible

Chapter 8: Using and Creating Templates

169

Note
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 File ➪ New, and choose the New from Existing option in the
Available Templates screen. This command creates a new workbook from an existing one, but gives a different
name to ensure that the old file is not overwritten. n

When you create a workbook that’s based on a template, the default workbook name is the tem-

plate name with a number appended. For example, if you create a new workbook based on a tem-

plate 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.

Note

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

extension. n

If novices will 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 cannot be changed when the

worksheet is protected. The following steps describe how to unlock the nonformula cells:

1. Choose Home ➪ Editing ➪ Find & Select ➪ Go to Special to display the Go To Special
   dialog box.


2. Select Constants and click OK. This step selects all nonformula cells.


3. Press Ctrl+1 to display the Format Cells dialog box.


4. In the Format Cells dialog box, click the Protection tab.


5. Remove the check mark from the Locked check box.


6. Click OK to close the Format Cells dialog box.


7. Choose Review ➪ Changes ➪ Protect Sheet to display the Protect Sheet dialog box.
   8. Specify a password (optional) and then click OK.

After you perform these steps, you can’t modify the formula cells — unless the sheet is unprotected.

Locking Formula Cells in a Template File