Templates allow you to create blank sheets and blank workbooks that have customised formats as well as customised Page Setup settings, including headers and footers.
A template is a special Excel file type that when opened through Excel, creates a normal sheet or a normal file based on the template file settings.
If you open a template file directly from a folder it will open as a template file and allow you to edit it and save it.
There are three types of template files
- Default sheet template – used when creating a new blank sheet
- Default workbook template – used when creating a new blank workbook
- Custom workbook template – used to create data entry spreadsheet for things like travel re-imbursements or capital expenditure submissions
Excel comes with a number of templates already installed. You can also download free and paid templates from the internet. If you right click a sheet tab and choose Insert the Insert dialog in the image below is displayed.
The Spreadsheet Solutions tab shows the installed templates. The button Templates on Office.com will take you to the Microsoft Office Template website where there are templates for most Office applications.
To create a default sheet or default workbook templates you need to create two separate template files, with specific names. They have to be saved into a specific folder on your system.
The exact location of that folder may vary depending on your Excel and Windows versions. You need to search for a folder on your C drive called XLSTART. Once found, note it’s path.
The path should be similar to C:\Program Files (x86)\Microsoft Office\Office 14\XLSTART
This folder may be protected by administrator access. Hence, we will initially save the template files to the Documents or My Documents folder and later copy them to the XLSTART folder using File Explorer.
There is another dedicated template folder. Again its exact path can vary with different systems but this one is called Templates and is usually a sub-folder of a Microsoft folder. This folder contains data entry templates, rather than blank templates. When you save a file as a template Excel defaults to using this folder so you don’t have to find it.
Create a Default Sheet Template
Open a new workbook and delete all the sheets except one. Create your Custom Number formats and change any Page Setup print settings, including headers and footers. You can also save Styles with a sheet. A Style is a combination of formats that you can create and then use to standardise much of your formatting. There is a large section on the Home Ribbon dedicated to Styles. See blog post on using Excel Styles here.
Once complete, press the F12 function key to open the Save As dialog. From the Save as type drop down select Excel Template. As soon as you do Excel will open the default Templates folder mentioned above. Change the name to Sheet.xltx and navigate to the Documents or My Documents folder and save. See image below. You can try saving to the XLSTART folder though Excel may tell you don’t have Administrator access.
Then use File Explorer to copy the Sheet.xltx file to the XLSTART folder. You need to close and re-open Excel for the new blank sheet template to take effect.
Create a Default Workbook Template
Open a new workbook. If you have created a default sheet template then insert a new blank sheet. Delete the other sheets in the file and add as many new sheets as you want. Save the file as an Excel Template with the name it Book.xltx to the Documents or My Documents folder.
Use File Explorer to copy the Book.xltx file to the XLSTART folder.
You need to close and re-open Excel for the new default workbook template to take effect.
Custom Workbook Templates
If you want to convert an existing file into a custom template file use Save As and save the file as a template into the Templates folder.
When you click the File tab and select New on the left. The My Templates icon will display your customised templates. Other templates are also shown on the right. See image below.
Once you have created a template it is easy to share with other people. They just need to save the template into the correct folder on their system.
Warning: Template files are typically saved to a hard drive folder. To be safe, also copy the files to your server, so they get backed up.