Templates

A template is a pre-defined workbook (with one or more worksheets) that can be used to help you create your final workbook.
Using templates that contain pre-formatted worksheets can save you a lot of time.
Every time you select (File > New) a template is used to create the blank workbook.

Important Facts About Templates

An Excel template has the file extension (".xltx").
Excel templates with VBA code create workbooks which have the same VBA code. This is different to Word.
A template can contain a single worksheet
A template can contain multiple worksheets
You can use templates in insert worksheet(s) into existing workbooks

Workbook Names

The default workbook name is the name of the template with a number appended to the end.
If you create a new workbook from a template called MyTemplate.xlt then this workbook will have the default filename of MyTemplate1.xls.

Advantages of using a Template ?

Templates can save you a lot of time so it is worth taking a few minutes to look at the ones that are built-in.
A template can help you create workbooks that are consistent and can help you to customise your workbooks for specific tasks.
If you save a workbook as a template you can create a new workbook containing the same formatting and styles by opening the template.
They provide a way of ensuring consistency among your reports.

What can a template include ?

When you save a workbook as a template all the information is saved within the file.
Formatting - These worksheets normally contain formatting and formulas, so they are "ready to use" immediately.
User Defined Functions -
Formulas -
Macros -
Custom Toolbars -

When you use the (File > New) dialog box to open a template you are actually opening a copy of the original file.
Before you open template you can see a preview to get some idea of the appearance of the workbook.

Where can I access Templates ?

There are several places you can use templates.
Remember that a template can also be used to insert worksheets into an existing workbook.
You can display the Templates dialog box from a variatey of different places

Worksheet Tab

(Worksheet tab)(shortcut menu, Insert)
Worksheet tab shortcut menu, Insert)(2003)

On My Computer

(File > New(on my computer)(2003)

On my computer - Displays the "Templates" dialog box letting you type in a URL of the website you want to download a template from.

All the default templates will appear on the General tab
All user-generated templates are normally stored on the General tab

From Windows Explorer

It is also possible to use the right mouse button to select an existing workbook and by selecting "New" you will create a new workbook based on that template.

On My Websites

On my Web Sites - Displays the New from Templates on my Websites dialog box letting you type in the URL of the website you want to download a template from.
SS

Use an existing workbook as your template

Using Existing Workbook

When you create a workbook Excel just creates a copy of the template, leaving the original template unchanged.

From existing workbook - Displays the "New from Existing Workbook" dialog box allowing you to open a copy of an existing file. When you save this workbook the Save As dialog box is displayed and a number is appended to the end of the file name.

Double click on the file or select the file and press Open/Create
You can double click the filename to create a copy of the template.

How to Create a Template

If you have a particular workbook that you want to use as a skeleton or template for other workbooks you can save it as a template.
You can save yourself a lot of time by saving your favourite workbooks as templates.
Open one of your workbooks. Remove all the numbers but leave the formatting and formulas intact.
The next time you want to use this workbook you just need to fill in the numbers.
Select (File > Save As) and select "Template (".xlt") in the save as type drop-down box.
Templates will automatically be saved in the default Templates folder.

The folder will automatically change to your default templates folder when you select this file format.
Select this folder or a subfolder. You can easily create a new subfolder within this folder.

You can only have a single worksheet in your workbook before saving it as a template ?? CHECK !
It is possible to change the directory used for your templates (Tools > Options) or you can specify an additional folder (although you have to use/ have it displayed the Office toolbar ??/)
SS

If you save a workbook into the following directory it will automatically become an installed template:
SS of folders

Your Templates Folder

Any templates you save in the default folder will automatically appear on the General tab.
Any templates you add to this folder will automatically appear on the General tab.
365 -
2019 -
2016 - C:\Users\"user name"\App Data\Microsoft\Templates\
2013 - C:\Documents and Settings\"user name"\Application Data\Microsoft\Templates\
This folder location has nothing to do with the xlstart Directory.

Accessible from the General Tab

Saving templates in your default templates folder will mean they appear automatically appear under the "General templates" folder.
If you want to display an additional tab in the (File > New) dialog box then create a subfolder in the Templates folder.
SS

Creating a New Tab

The name of the folder becomes the title of the new tab.
You can quickly create a new subfolder using the folder in the top right corner.
You can create subfolders within the template folder to store additional templates.
Any templates saved in the templates folder will appear on the General tab.
Any templates saved in subfolders will be displayed on additional tabs.

Saving Workbooks (.xls, .xlsx) and Templates (.xlt, xltx)

You do not have to save your workbooks as templates (".xlt") in order to use them as templates.
Any workbooks that are saved in the templates folder can be used as templates.
They don't have to be saved in the template format.
SS of both .xls and .xlt displayed on the tab. Highlight the difference in icons.
When you select a regular workbook a copy of the workbook is opened and a number is appended to the end of the file name.

Office Wide Templates

The templates folder is actually a Microsoft Office templates folder and is also used in Word and PowerPoint as well.
Any files saved in this folder (or any subfolder) will be automatically displayed in the correct application.
For example you can could create a subfolder called Better Solutions which contains all the templates for Excel, Word and PowerPoint.
The tab is only displayed in Excel if there are files in this folder with the following file extensions (.xls, .xlsx, .xlt, xltx).

Creating Workbooks based on Templates

When you create a new workbook based on a template you are not opening the original template.
The workbook that is created is a completely separate file that just contains everything you put in the template.
The default file name associated with ANY template is Book#
The original template filename does not appear in the title bar.

To prevent over-writing 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.

Important

To display a picture of the first page of a template in the Preview box of the Templates dialog box (General Templates..., New Workbook task pane), click Properties on the File menu, click the Summary tab, and then select the Save preview picture check box.
You can create a new workbook based on an existing workbook (if you do not want to re-save it). This will create an un-named workbook which you can then save with a different file name.
Your custom folder will appear as a tab in the Templates dialog box only is there is a file corresponding to that particular application.
what happens if you save them in your xlstart ???
Remember that Excel does not distinguish between upper case and lower case in file names.
You must always create a new template using (File > New) and only use (File > Open) when you want to change the original template.