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. In the Save As Type drop-down list, select Excel Template.
2. 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.
3. 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).

You can also save a template in an earlier file format. In Step 2, select Excel 97-2003 Template from the Save As Type drop-down list box. 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

If you create a new workbook that you based on a template, Excel creates a copy of the template in memory so that the original template, on disk, remains intact. The default workbook name is the template name with a number appended to it. For example, if you create a new workbook based on a template by the name of Report.xltx, the workbook default name is Report1.xlsx. The first time that you save a workbook that you create from a template, Excel displays the Save As dialog box so that you can give the file a new name. To create a workbook from a template, follow these steps:

Click the Office button and then choose New. Excel displays the New Workbook dialog box. Select a template category from the list on the left side of the dialog box. The choices are as follows:

Blank and Recent:
This is the default category. From here you can select Recently Used Templates. Select a template and click Create to open a copy of the template file.

Installed Templates:
This category displays a gallery of templates installed in your system. Select a template and click Create to open a copy of the selected template.

My Templates:
This category contains templates you previously saved in the Templates folder or in a subfolder in the Templates folder. Click My Templates to display the New dialog box. Templates you create in the Templates folder appear in the My Templates tab. If you saved Templates in one or more subfolders in the Templates folder, the folder names appear as tabs in the New dialog box. Select a template from a tab and click OK. Excel opens a copy of the template.

New from Existing:
This category allows you to use any workbook as a template or to use a template file that’s not in the Templates folder. Click New from Existing to display the New from Existing Workbook dialog box. Navigate to the folder containing the file you want to use as a template, select the file in the folder, and click Create New. Excel opens a copy of the file.

Microsoft Office Online:
If you are connected to the Internet, you can select from one of the online categories and Excel will display a list of available templates in the selected category. Choose a template, click Download, and Excel opens a copy of the template.

Save the workbook after you enter the appropriate data in the template copy.

Creating a default workbook template

You can create a default workbook template that defines the formatting or content of the new (blank) workbooks that open after you start Excel. Excel bases every new (blank) workbook that you open on the default workbook template. The default workbook template that you create replaces Excel’s built-in default workbook template. Follow these steps to create a default workbook template:

1. Create a new workbook and add or delete as many worksheets as you want to appear in the new workbook.
2. If you want, turn off the display of gridlines. Apply the desired formatting, sheet names, text, styles, and so on.
3. Select a new theme for the template if you don’t like Excel’s default choice.
4. Click the Office button, choose Save As, and select Excel Template from the Save As Type drop-down list.
   Windows XP: In the Save In drop-down list, locate an xlstart folder. Excel can use more than one xlstart folder and will open all files located in these folders on startup. The xlstart folders normally reside in the following locations: C:\Documents and Settings\Username\Application Data\ Microsoft\Excel (where Username is your login username) and C:\ Program Files\Microsoft Office\Office 12 folders, respectively.
   Windows Vista: If the Folders window isn’t displayed, click Browse Folders, click Folders to display the Folders window, and then locate the xlstart folder. Excel can use more than one xlstart folder and will open all files located in these folders on startup. The xlstart folders normally reside in: C:\Users\Username\AppData\Roaming\Microsoft\Excel (where Username is your login name) and C:\Program Files\Microsoft Office\Office 12 folders, respectively.
5. In the File Name text box, type book.xlt and click Save.

   All new (blank) workbooks that you create are now replicas of the book.xlt workbook that you saved in Step 9.
   You can always edit the book.xlt file or delete it if you no longer want to use it.