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 want to start with a fresh, clean copy.

This template file serves as the basis for creating new expense reports.

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

Note

You’ll find the Expense Report.xltx file with the other examples on the companion Web site.

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 File tab, 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 Templates folder and saves your new template there. This is the location that ensures that your template is always available when you click the New command on the File tab and click My Templates.

When you install Excel, a folder named Templates is installed on your hard disk in the following location:

  • Windows 7, Windows Vista C:Users<your name>AppDataRoamingMicrosoftTemplates

  • Windows XP C:Documents and Settings<your name>Application DataMicrosoftTemplates

When you create a new document by clicking the File tab, New, and then selecting one of the many templates available,, 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 Expenses1.

Tip

INSIDE OUT Full Disclosure

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 Templates 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, and Folder Options; then, on the View tab, select Show Hidden Files, Folders, And Drives. 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 templates and .xlsx for regular workbooks. These may be “retro,” but they are also still helpful, letting you tell at a glance in which format a file is saved. To make extensions visible, clear the Hide Extensions For Known File Types check box, which is also on the View tab in the Folder Options dialog box.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset