Excel 2010: The Missing Manual

15.1. Understanding Templates

Before you begin using and creating templates, it's important to understand what they really are. Many template novices assume that templates are a special sort of file that's similar to Excel spreadsheets. However, the crafty individuals who created Excel actually designed template files so that they're exactly the same as spreadsheet files. (Whether this feature is a brilliant masterstroke of simple, elegant design, or the product of terminally overworked programmers is up to you to decide.)

If you use a tool like Windows Explorer to look in a folder that contains spreadsheet files and template files, you'll notice that templates always have the extension .xlt instead of .xls. For example, Invoice.xlt might be a template file for creating invoices, while Invoice-01.xls would be an actual invoice. However, you can put the exact same type of data in both files: elaborately formatted worksheets, formulas, numbers , text, and so on.

Based on this similarity, you might wonder why you should bother using templates at all. The difference is in how Excel handles templates. Here are some of the differences:

• Templates are usually stored in a special folder, so that the latest version of the template is always easily accessible.

• It's almost impossible to accidentally overwrite a template file. That's because Excel automatically creates a new worksheet when you select a template (more on how to actually use templates in a moment).

• Spreadsheet experts often create templates brimming with advanced Excel features (things like data validation tools, which you'll learn about later in this chapter, and fancy formula design). These features help you prevent errors (like entering incorrect data or entering it in the wrong place) and provide additional guidance to others who use the template.

Finally, it's easy to distinguish template files (the blueprints) from real spreadsheets because of the different file extension they use. The icon also looks slightly different.

Most organizations and businesses maintain a group of templates that define things like standard layout and formulas for common types of spreadsheets such as invoices and expense reports . Some organizations host these templates in a central location on a network file server or Web server (the two best options), or just distribute them to each employee who needs them.

So how do you put templates to work? The trick is to understand your options when creating them.