Working with Workbook Templates

A workbook template is basically a workbook that contains one or more worksheets set up with formatting and formulas and ready for you to enter data and get immediate results. A workbook template can use any of Excel’s features, such as charts, formulas, and macros. Excel includes templates that automate the common tasks of filling in invoices, expense statements, and purchase orders. You can also download several more templates from the Internet. You can also create your own templates from scratch or from an existing workbook.

Creating a workbook template

To save a workbook as a template, follow these steps:

1. Click the Office button and then choose Save As.
2. In the Save As Type drop-down list, select Excel Template.
3. If you want to save the template in a subfolder of the Templates folder in Windows XP: Excel displays the Templates folder in the Save In drop-down list. Select the subfolder in the Save In drop-down list.
   If you want to save the template in a subfolder of the Templates folder in Windows Vista: Click Browse Folders (if the Folders window isn’t displayed) and click Folders to display the Folders window if necessary (the Templates folder is automatically selected after Step 2), and then select a subfolder.
   To create a new folder in the Templates folder in which you can save the template, click the Create New Folder button in the Save As dialog box and give the new folder a name.
4. In the File Name box, enter a name for the template, and then click Save. Excel saves templates with an .xltx file extension. If your template contains macros, Excel gives you the option to save the template without macros or to save in a format that supports macros (.xltm).

To prevent overwriting the template file when you create a new workbook from a template, always save your templates in the Templates folder or a subfolder within the Templates folder.

Creating a workbook from a template

To create a workbook based on a template, just select the template and click Download. Depending on the size of the file and the speed of your Internet connection, it can take anywhere from a few seconds to a minute or more. What you do next depends on the template. Every template is different, but most are selfexplanatory. Some workbooks require customization. Just replace the generic information with your own information.

It’s important to understand that you’re not working with the template file. Rather, 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 available in the My Templates category in the New Workbook dialog box, so you don’t need to redownload it if you want to re-use the template.

This particular workbook has a few simple formulas that perform calculations using the data that you enter.

A workbook created from an memo 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.

The workbook memo template, after entering some information.

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. To open a template, choose Office button => Open (not File => New) and locate the template file (it will probably have an XLTX or XLT extension). Template files, by default, are stored in:

C:\Documents and Settings\\Application Data\Microsoft\Templates

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.

To create a workbook from a modified template, you must select the template from the My Templates section in the New Workbook dialog box. Clicking My Templates displays the New dialog box. Just select the template and click OK.

The above screenshot shows difference between .XLT icon and .XLTX icon.