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 special type of workbook that's used as the basis to create other workbooks. This chapter discusses some of the templates available from Microsoft and 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.
Exploring Excel Templates
The best way to become familiar with Excel template files is to jump in and try a few. Excel 2013 gives you quick access to hundreds of template files.
Viewing templates
To explore the Excel templates, choose File ⇒ New. The template thumbnails displayed on the screen that appears are just a small sampling of those that are available. Enter a descriptive word, and search for more.
Note
The searching is done at Microsoft Office Online, so you must be connected to the Internet in order to search for templates.
For example, enter invoice, and click the Search button. Excel displays many more thumbnails, and you can narrow the search by using the category filters on the right.
Figure 8.1 shows the results of a template search.
Figure 8.1
The New page in Backstage view allows you to search for templates.
Note
Microsoft Office Online has a wide variety of templates, and some are better than others. If you download a few duds, don't give up. Even though a template may not be perfect, you may be able to modify a template to meet your needs. Modifying an existing template is often easier than creating a workbook from scratch.
Creating a workbook from a template
To create a workbook based on a template, just locate a template that looks like it might do the job, and click the thumbnail. Excel displays a box with a larger image, the source for the template, and some additional information. If it still looks good, click the Create button. Otherwise, click one of the arrows to view details for the next (or previous) template in the list.
When you click the Create button, Excel downloads the template and then creates a new workbook based on that template.
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.
Note
It's important to understand that you're not working with the template file. Instead, you're working with a workbook that was created from the template file. If you make any changes, you're not changing the template — you're changing the workbook that's based on the template. After you download a template from Microsoft Office Online, that template is saved for future use (you won't have to download it again). Downloaded templates appear as thumbnails when you choose File ⇒ New.
Figure 8.2 shows a workbook created from a template. This workbook needs to be customized in several areas. But if this template will be used again, it's more efficient to customize the template rather than every workbook created from the template.
If you want to save the newly created workbook, click the Save button. Excel proposes a filename based on the template's name, but you can use any filename you like.
Figure 8.2
A workbook created from a template.
Modifying a template
A template file that you download is just like a workbook file. You can open a template file, make changes to it, and then resave it. For example, with the invoice template shown in Figure 8.2, you may want to modify the template so that it shows your company information and logo 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 for editing, choose File ⇒ Open (not File ⇒ New) and locate the template file (it will have an .xltx, .xltm, or .xlt extension). When you open a template file by choosing File ⇒ Open, you're opening the actual template file — you are not creating a workbook from the template file.
One way to find the location of your downloaded template files is to look at your trusted locations list:
1. Choose Tools ⇒ Options. The Excel Options dialog box appears.
2. Choose Trust Center, and click the Trust Center Settings button. The Trust Center dialog box appears.
3. In the Trust Center dialog box, choose Trusted Locations. You'll see a list of trusted locations. Downloaded templates are stored in the location described as User Templates. If you want to modify (or delete) a downloaded template, this is where you'll find it.
On my system, downloaded templates are stored here:
C:Users<username>AppDataRoamingMicrosoftTemplates
Understanding Custom Excel Templates
So far, this chapter has focused on templates that were created by others and downloaded to your computer. 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 entering the header and footer doesn't take much time, 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.
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.
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 one worksheet, the worksheet has gridlines, the page header and footer are blank, text appears in the font defined in the default Normal style, 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 to change Excel's workbook defaults:
1. Open a new workbook.
2. Add or delete sheets to give the workbook the number of worksheets that you want.
3. Make any other changes that you want to make, which can include column widths, named styles, page setup options, and many of the settings that are available in the Excel Options dialog box. To change the default formatting for cells, choose Home ⇒ Styles ⇒ Cell Styles and then modify the settings for the Normal style. For example, you can change the default font, size, or number format.
4. When your workbook is set up to your liking, choose File ⇒ Save As. The Save As dialog box appears.
5. Select Excel Template (*.xltx) from the Save As Type list. If your template contains any VBA macros, select Excel Macro-Enabled Template (*.xltm).
6. Enter book for the filename.
Caution
Excel will offer a name, such as Book1.xltx. You must change this name to book.xltx (or book.xltm) if you want Excel to use your template to set the workbook defaults.
7. Save the file in your XLStart folder (not in the templates folder that Excel proposes).
Tip
One way to find the location of your XLStart folder is to look at your trusted locations list. Choose Tools ⇒ ⇒ ⇒ Options to display the Excel Options dialog box. Then choose Trust Center, and click the Trust Center Settings button. In the Trust Center dialog box choose Trusted Locations, and you'll see a list of trusted locations. The location for your book.xltx file is the location described as User StartUp. On my system, the folder is
C:Users<username>AppDataRoamingMicrosoftExcelXLSTART
8. Close the file.
After you perform the preceding steps, the new default workbook is based on the book.xltx (or book.xltm) workbook template. You can create a workbook based on your template by using any of these methods:
• Press Ctrl+N.
• Open Excel without first selecting a workbook to open. This option works only if you disable the option to show the Start Screen when Excel starts. This option is specified in the General tab of the Excel Options dialog box (choose File ⇒ Open to display the Excel Options dialog box).
Note
The book.xltx template is not used if you choose File ⇒ New and choose Blank Workbook from the list of templates. That command results in a default workbook. I'm not sure if this is a bug or if it's by design. In any case, it provides a way to override the custom book.xltx template if you need to.
Creating a worksheet template
You can also create a single sheet template named sheet.xltx. Use the same procedure described for book.xltx. The sheet.xltx template is used when you insert a new worksheet.
Editing your template
After you create your book.xltx template, you may discover that you need to change it. You can open the template file and edit it just like any other workbook. After you make your changes, save the file to its original location and close it.
Resetting the default workbook
If you create a book.xltx file and then decide that you'd rather use the standard default settings, simply delete (or rename) the book.xltx template file. Excel then uses its built-in default settings for new workbooks.
Creating custom templates
The book.xltx template discussed in the preceding section is a special type of template that determines default settings for new workbooks. This section discusses other types of templates, referred to as custom workbook templates, which are simply workbooks that you set up as the basis for new specific types of workbooks.
Creating a custom 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.
Note
You could, of course, just use the previous month's workbook and save it with a different name. This is prone to errors, however, because you easily can forget to use the Save As command and accidentally overwrite the previous month's file. Another option is to choose File ⇒ Open, and choose Open as Copy in the Open dialog box (this command appears when you click the arrow on the Open button). Opening a file as a copy creates a new workbook from an existing one, but it uses a different name to ensure that the old file is not overwritten.
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's created from a template, Excel displays the Save As dialog box so that you can give the workbook a different name if you want to.
A custom template is essentially a normal workbook. 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.
Note
If your template contains macros, it must be saved as an Excel Macro-Enabled Template, with an .xltm extension.
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.
Using custom templates
To create a workbook based on a custom template, choose File ⇒ New, and then click Personal (below the search box). You'll see thumbnails of all your custom worksheet templates. Double-click a template, and Excel creates a workbook based on the template.
Getting 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, font size, 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 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 more.
Of course, you can 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.