Using Template Files to Store Formatting

A template is a model that can serve as the basis for new worksheets. A template can include both data and formatting information. Template files are great timesavers. They're also an ideal way to ensure a consistent look among reports, invoices, and other documents you tend to create repeatedly. Figure 9-64 shows an example of a template for an expense report. This worksheet would make a good template because expense reports are used repeatedly, but each time you'll want to start with a fresh, clean copy.

Figure 9-64: This template file serves as the basis for creating new expense reports.

On the CD You'll find the  Expense Report.xltx file in the Sample Files section of the companion CD.

The advantages to using templates are standardization and protection. It is difficult to overwrite the original accidentally, because when you save a new template-based workbook for the first time, you must supply a new name for it. This way you can repeatedly create new workbooks with the same appearance without worrying about overwriting the original.

To create a template file, follow these steps:

1. Open the workbook you want to use as a template.

2. Click the Microsoft Office Button, Save As, and supply a file name.

3. Choose Excel Template (*.xltx) from the Save As Type drop-down list, and click Save.

When you choose the Excel Template format in the Save As dialog box, Excel switches to the Template folder and saves your new template there. This is the location that ensures your template is always available when you click the New command on the Microsoft Office Button and double-click My Templates.

You can use any Excel workbook as a template, even if you did not save it in template format. When you installed Excel, a folder named Templates was installed on your hard disk in the following location:

• Windows Vista: C:\Users\<your name>\AppData\Roaming\Microsoft\Templates

• Windows XP: C:\Documents and Settings\<your name>\Application Data\ Microsoft\Templates

When you open a template, a fresh copy of the workbook is created, and the copy is given a temporary name consisting of the original file name plus a number. If the template file is named Expenses, for example, the working copy is named Expensesl.

Inside Out-Full Disclosure

Microsoft Windows tries to keep secrets from you-for your own good, of course. But fearless readers of this book need no such accommodation, so here are a couple of actions you can take to make life a little easier. First, let's show hidden files and folders. If you go looking, the Template folders described in this chapter can be hard to find, because they are in locations Windows likes to keep hidden from view. To make them more findable, click the Windows Start menu, Control Panel, Appearance And Personalization, Folder Options, and on the View tab, select Show Hidden Files And Folders. Second, let's display all the file extensions. The old-style MS-DOS extensions used to be three characters in length and now can be four, such as .xltx for template and .xlsx for regular workbook. These may be "retro," but they also still helpful, letting you tell at a glance in which format a file was saved. To make extensions visible, clear the Hide File Extensions For Known File Types check box, which is also on the View tab in the Folder Options dialog box.