Chapter 8. Using and Creating Templates

IN THIS CHAPTER

Understanding Excel templates

Working with the default templates

Creating custom templates

A template is essentially a model that serves as the basis for something else. An Excel template is a workbook that's used to create other workbooks. This chapter discusses some of the templates included with Excel and also describes how to create your own template files. Creating a template takes some time, but in the long run, doing so may save you a lot of work.

8.1. Exploring Excel Templates

The best way to become familiar with Excel template files is to jump in and try a few. Excel 2010 gives you quick access to hundreds of template files.

8.1.1. Viewing templates

To explore the Excel templates, choose File New to display the Available Templates screen in Backstage View. You can select a template stored on your hard drive, or a template from Microsoft Office Online. If you choose a template from Microsoft Office Online, you must be connected to the Internet to download it.

The Office Online Templates section contains a number of icons, which represent various categories of templates (see Figure 8.1). Click an icon, and you'll see the available templates. When you select a template thumbnail, you see a preview in the right panel.

Figure 8.1. The New page in Backstage View displays template categories.

8.1.2. Creating a workbook from a template

To create a workbook based on a template, just locate the template and click the Create button on the right. If the template is on Microsoft Office Online, the Create button is replaced with a Download button.

What you do next depends on the template. Every template is different, but most are self-explanatory. Some workbooks require customization. Just replace the generic information with your own information.

Figure 8.2 shows a workbook based on an invoice template. This particular workbook has a few simple formulas that perform calculations using the data that you enter. Figure 8.3 shows the workbook after it has been customized a bit, and some items added. Notice that formulas calculate the subtotal, sales tax, and total.

Figure 8.2. A workbook created from an invoice template downloaded from Microsoft Office Online.

If you want to save the workbook, click the Save button. Excel proposes a named based on the template's name, but you can use any name you like.

Figure 8.3. The workbook, after customizing it and entering some information.

8.1.3. Modifying a template

A template file is just like a workbook file. As such, you can open a template file, make changes to it, and then resave the template file. Looking at the invoice template shown earlier in this chapter, you may want to modify it so that it shows your company information and uses your actual sales tax rate. Then, when you use that template in the future, the workbook created from it will already be customized.

To open a template, choose File Open (not File New) and locate the template file (it will have an .xltx or .xlt extension).

The location for template files varies. To find out where Excel stores template files, open a new workbook and choose File Save As. In the Save As dialog box, choose Excel Template (*.xltx) from the Save as Type drop-down list. Excel will activate your template folder. Make a note of this location, and click Cancel to close the Save As dialog box. On my system, templates are stored here:

C:Users<username>AppDataRoamingMicrosoftTemplates

When you open a XLST (or *.XLT) template file, you are opening the actual file — you are not creating a workbook from the template file.

Figure 8.4. The New dialog box displays downloaded and custom templates stored on your hard drive.

8.2. Understanding Custom Excel Templates

So far, this chapter has focused on templates that were created by others. The remainder of the chapter deals with custom templates — templates that you create.

Why create custom templates? The main reason is to make your job easier. For example, you may always like to use a particular header or footer on your printouts. Consequently, the first time that you print a worksheet, you need to spend time entering the header and footer information. Although it isn't a lot of work, wouldn't it be easier if Excel simply remembered your favorite page settings and used them automatically?

The solution is to modify the template that Excel uses to create new workbooks. In this case, the modification consists of inserting your header into the template. Save the template file using a special name, and then every new workbook that you create (including the workbook created when Excel starts) has your customized page settings.

Excel supports three types of templates, which I discuss in the following sections:

• The default workbook template: Used as the basis for new workbooks.

• The default worksheet template: Used as the basis for new worksheets inserted into a workbook.

• Custom workbook templates: Usually, these ready-to-run workbooks include formulas, but they can be as simple or as complex as you like. Typically, these templates are set up so that a user can simply plug in values and get immediate results. The Microsoft Office Online templates (discussed earlier in this chapter) are examples of this type of template.

8.2.1. Working with the default templates

The term default template may be a little misleading. If you haven't created your own template files to control the default settings, Excel uses its own internal settings — not an actual template file. In other words, Excel uses your template files to set the defaults for new workbooks or worksheets, if these files exist. But if you haven't created these files, Excel is perfectly happy to use its own settings.

8.2.1.1. Using the workbook template to change workbook defaults

Every new workbook that you create starts out with some default settings. For example, the workbook has three worksheets, the worksheets have gridlines, the page header and footer are blank, and text appears in the fonts specified by the default document template. Columns are 8.43 units wide, and so on. If you're not happy with any of the default workbook settings, you can change them by creating a workbook template.

Making changes to Excel's default workbook is fairly easy to do, and it can save you lots of time in the long run. Here's how you change Excel's workbook defaults:

If you insert a new worksheet into a workbook that's based on the book.xlxt template, the new worksheet will not use any customized worksheet settings specified in the template (for example, a different column width). Therefore, you may also want to create a sheet.xltx template (described in the next section), which controls the settings for new worksheets.

If you ever want to revert to the standard default workbook, just delete the book.xltx file.

8.2.1.2. Using the worksheet template to change worksheet defaults

When you insert a new worksheet into a workbook, Excel uses its built-in worksheet defaults for the worksheet. These default settings include items such as column width, row height, and so on.

If you don't like the default settings for a new worksheet, you can change them by following these steps:

After performing these steps, all new worksheets that you insert by using any of these methods will be formatted like your sheet.xltx template:

• Click the Insert Worksheet button (next to the last sheet tab).

• Choose Home Cells Insert Insert Sheet.

• Press Shift+F11.

• Right-click a sheet tab, choose Insert from the shortcut menu, and choose the Worksheet icon in the Insert dialog box.

8.2.1.3. Editing your templates

After you create your book.xltx or sheet.xltx templates, you may discover that you need to change them. You can open the template files and edit them just like any other workbook. After you make your changes, save the file to its original location, and close it.

8.2.1.4. Resetting the default workbook and worksheet settings

If you create a book.xltx or sheet.xltx file and then decide that you would rather use the standard default settings, simply delete the book.xltx or sheet.xltx template file — depending on whether you want to use the standard workbook or worksheet defaults — from the XLStart folder. Excel then uses its built-in default settings for new workbooks or worksheets.

You can also rename or move the template files if you'd like to keep them for future use.

8.2.2. Creating custom templates

The book.xltx and sheet.xltx templates discussed in the preceding section are two special types of templates that determine default settings for new workbooks and new worksheets. This section discusses other types of templates, referred to as workbook templates, which are simply workbooks that you set up as the basis for new workbooks or worksheets.

Creating a workbook template can eliminate repeating work. Assume that you create a monthly sales report that consists of your company's sales by region, plus several summary calculations and charts. You can create a template file that consists of everything except the input values. Then, when it's time to create your report, you can open a workbook based on the template, fill in the blanks, and be finished.

When you create a workbook that's based on a template, the default workbook name is the template name with a number appended. For example, if you create a new workbook based on a template 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.

8.2.2.1. Saving your custom templates

To save a workbook as a template, choose File Save As and select Template (*.xltx) from the Save as Type drop-down list. If the workbook contains any VBA macros, select Excel Macro-Enabled Template (*.xltm). Save the template in your Templates folder — which Excel automatically suggests — or a folder within that Templates folder.

If you later discover that you want to modify the template, choose File Open to open and edit the template.

8.2.2.2. Ideas for creating templates

This section provides a few ideas that may spark your imagination for creating templates. The following is a partial list of the settings that you can adjust and use in your custom templates:

• Multiple formatted worksheets: You can, for example, create a workbook template that has two worksheets — one formatted to print in landscape mode and one formatted to print in portrait mode.

• Style: The best approach is to choose Home Styles Cell Styles and modify the attributes of the Normal style. For example, you can change the font or size, the alignment, and so on.

• Custom number formats: If you create number formats that you use frequently, you can store them in a template.

• Column widths and row heights: You may prefer that columns be wider or narrower, or you may want the rows to be taller.

• Print settings: Change these settings in the Page Layout tab. You can adjust the page orientation, paper size, margins, and several other attributes.

• Header and footer: You enter custom headers or footers in Page Layout view (choose View Workbook Views Page Layout).

• Sheet settings: These options are in the Show group on the View tab, and also on the Advanced tab of the Excel Options dialog box (in the Display Options for This Worksheet section). Options include row and column header, page break display, gridlines, and others.

You can, of course, also create complete workbooks and save them as templates. For example, if you frequently need to produce a specific report, you may want to create a template that has everything for the report except for the data you need to enter. By saving your master copy as a template, you're less likely to overwrite the original file when you save the file after entering your data.