Making the Most of Templates

As you know, each workbook you create in Excel is based on a template. If you create a blank workbook, Excel uses a skeleton workbook template that gives you complete freedom of movement but doesn't save you any time. By contrast, if you use a template that comes complete with contents and formatting, you may need to fill in only a small amount of information to produce a complete workbook.

As you saw earlier in this chapter, Excel comes with a good variety of templates, and you can access many more on the Web by opening the Online Templates category in the Excel Workbook Gallery dialog box. But to save the most time, you'll almost certainly need to create your own templates, either from scratch or by customizing existing templates.

To make the most out of templates, follow these suggestions:

• Assess your needs carefully. Examine the workbooks that you and your colleagues create most often—sales analysis, project planning, budgets, invoices, timesheets, or whatever it may be. Figure out which of these workbooks you and your colleagues could create more quickly and easily using a template.
• Base the template on an existing workbook or template. If you have an existing workbook that contains much (or even some) of the information the template will need, open a copy of that workbook, and then save it as a template. (You'll learn these two moves later in this section.) You may be able to save further time and effort by copying and pasting items from other existing workbooks or templates into your new template.
• Make the template as complete as possible. Needs will vary, but you'll normally want to do the following:
  • Insert the number of worksheets most workbooks based on the template will need.
  • Name the worksheets. Color the worksheet tabs if this will help you pick out key tabs quickly.
  • Set up each worksheet with standard text and formulas.
  • Format cells with validation to ensure users enter suitable values.
  • Apply conditional formatting to make extreme values stand out.
  • Add comments to cells to explain what information is required and what is optional.
• Automate the template with macros. To help users enter and manipulate data swiftly and accurately, you may need to add macros to your templates. See Chapter 14 for information on creating and using macros.

Creating a Template Based on an Existing Workbook

To create a template based on an existing workbook, follow these steps:

1. Open Excel if it's not already running.
2. Click the Open button on the Standard toolbar (or press Cmd+O) to display the Open: Microsoft Excel dialog box.
3. Navigate to the folder that contains the existing workbook, and then click the workbook.
4. Click the Open pop-up menu, and then click Copy instead of the default selection, Original.

   NOTE: Opening a copy of the existing workbook helps you avoid overwriting it by mistake if you forget to use the Save As command to save it under a different name. Using the Save As command could hardly be easier—but it's equally easy to get distracted before you give the command.

5. Click the Open button to open a copy of the workbook.

You can now save the template as described in the next section.

Saving a Template

To save a template, you use the Save command as usual, but you need to choose the best template format for your needs and pick the right folder to save the template in.

To save the active workbook as a template, follow these steps:

1. Choose File Save or press Cmd+S to display the Save As dialog box.
2. Open the Format pop-up menu, and then click Excel Template (.xltx).

   NOTE: If you need to store macros in the template, choose Excel Macro-Enabled Template (.xltm). If colleagues using Excel 2004 (Mac) or Excel 2003 (Windows) or earlier versions will need to use the template, choose Excel 97–2004 (.xlt) in the Format pop-up menu; this template format can contain macros if necessary.

3. Choose the folder in which to store the template:
   • When you choose one of the Template formats in the Format pop-up menu, Excel automatically changes folder to your My Templates folder.
   • If you plan to share the template with your colleagues, you may want to store it in a folder on the network instead.
4. Click the Save button.