Microsoft Excel 2016 BIBLE (2016)

IN THIS CHAPTER

1. Understanding Excel templates

2. Working with the default templates

3. 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 2016 gives you quick access to hundreds of template files.

Tip

Examining templates is also a good way to learn about Excel. You may discover some techniques that you can ­incorporate into your own work.

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. Click one of the suggested search terms, or 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 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 for invoice.

Figure 8.1 The New page in Backstage view allows you to search for templates.

Note

Microsoft Office Online has a variety of templates. Some are better than others, so if you download a few duds, don't give up. Even though a template may not be perfect, you may be able to modify it 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.

Figure 8.2 A workbook created from a 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.

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 File 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>\AppData\Roaming\Microsoft\Templates\

After you've made changes to the template, use File Save to save the template file. Future workbooks that you create from this template will use the modified version of the template.

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 three worksheets, 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 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 Browse. The Save As dialog box appears.

5. Select Excel Template (*.xltx) from the Save As Type list. If your template ­contains 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 File 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>\AppData\Roaming\Microsoft\Excel\XLSTART\

8. Close the template 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 Options 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 and 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 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 the user enters.

Note

If your template contains macros, it must be saved as an Excel Macro-Enabled Template, with an .xltm extension.

Locking Formula Cells in a Template File

If novices will use the template, you might consider locking all the formula cells to make sure that the formulas aren't deleted or modified. By default, all cells are locked and can't be changed when the worksheet is protected. The following steps describe how to unlock the nonformula cells:

1. Choose Home Editing Find & Select Go to Special. The Go to Special dialog box appears.

2. Select Constants and click OK. This step selects all nonformula cells.

3. Press Ctrl+1. The Format Cells dialog box appears.

4. Select the Protection tab.

5. Remove the check mark from the Locked check box.

6. Click OK to close the Format Cells dialog box.

7. Choose Review Changes Protect Sheet. The Protect Sheet dialog box appears.

8. Specify a password (optional), and click OK.

After you perform these steps, you can't modify the formula cells unless the sheet is unprotected.

Saving your custom templates

To save a workbook as a template, choose File Save As Browse 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 Templatesfolder — 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 (plus others). 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, and alignment.

· 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.