Excel templates: how to make and use

What is Excel template?

An Excel template is a predesigned sheet that can be used to create new worksheets with the same layout, formatting and formulas. With templates, you don't need to recreate the basic elements every time as they are already integrated into the spreadsheet.

In an Excel template, you can use save the following settings:

• The number and type of sheets
• Cell formats and styles
• Page layout and print areas for each sheet
• Hidden areas to make certain sheets, rows, columns or cells invisible
• Protected areas to prevent changes in certain cells
• Text that you want to appear in all workbooks created based on a given template, such as column labels or page headers
• Formulas, hyperlinks, charts, images and other graphics
• Excel Data validation options such as drop-down lists, validation messages or alerts, etc.
• Calculation options and window view options
• Frozen rows and columns
• Macros and ActiveX controls on custom forms

How to create a workbook from an existing template

Instead of starting with a blank sheet, you can quickly create a new workbook based on an Excel template. The right template can really simplify your life since it makes the most of tricky formulas, sophisticated styles and other features of Microsoft Excel that you might not be even familiar with.

A great lot of free templates for Excel are available, waiting to be used. To make a new workbook based on an existing Excel template, perform the following steps.

1. In Excel 2013 and higher, switch to the File tab and click New and you will see many templates provided by Microsoft.
   

   In Excel 2010, you can either:

   • Select from Sample templates - these are basic Excel templates that are already installed on your computer.
   • Look under com Templates section, click on some category to view the templates thumbnails, and then download the template you want.

   

2. To preview a certain template, simply click on it. A preview of the selected template will show up along with the publisher's name and additional details on how to use the template.
3. If you like the template's preview, click the Create button to download it. For example, I've chosen a nice mini calendar template for Excel:
   

   That's it - the selected template is downloaded and a new workbook is created based on this template right away.
   

How to make a custom Excel template

Making your own templates in Excel is easy. You start by creating a workbook in the usual way, and the most challenging part is to make it look exactly the way you want. It is definitely worth investing some time and effort both in the design and contents, because all formatting, styles, text and graphics you use in the workbook will appear on all new workbooks based on this template.

Once you've created the workbook, you just need to save it as a .xltx or .xlt file (depending on your Excel version) instead of usual .xlsx or .xls. The detailed steps are:

1. In the workbook you want to save as a template, click File > Save As
2. In the Save As dialogue, in the File name box, type a template name.
3. Under Save as type, select Excel Template (*.xltx). In Excel 2003 and earlier versions, select Excel 97-2003 Template (*.xlt).

   If your workbook contains a macro, then choose Excel Macro-Enabled Template (*.xltm).

   When you select one of the above template types, the file extension in the File Name field changes to the corresponding extension.
   

   Note. Please be aware that as soon as you choose to save your workbook as Excel Template (*.xltx), Microsoft Excel automatically changes the destination folder to the default templates folder, which is usually

   C:\Users\<User Name>\AppData\Roaming\Microsoft\Templates

   If you want to save the template to some other folder, remember to change the location after selecting Excel Template (*.xltx) as the document type. At that, regardless of what destination folder you choose, a copy of your template will be saved to the default templates folder anyway.

4. Click the Save button to save your newly created Excel template.

Now, you can create new workbooks based on this template and share it with other users. You can share your Excel templates in many ways, just like usual Excel files - e.g. store a template in a shared folder or your local network, save it to OneDrive (Excel Online) or email as an attachment.

How to find custom templates in Excel

It is not a big problem to pick any of the previously used templates in Excel 2010 and earlier versions - simply go to the File tab > New and click My templates.

No one knows why Microsoft decided to discontinue this feature in Excel 2013, but the fact is that My templates do not show up by default.

How to make a default template for Excel

If among your Microsoft Excel templates there is one that you use most often, you may want to make it the default template and have it opened automatically on Excel start.

Microsoft Excel allows creating two special templates - Book.xltx and Sheet.xltx - that are the basis for all new workbooks and all new worksheets, respectively. So, the key point is to decide which template type you want:

• Excel Workbook template. This type of a template includes several sheets. So, create a workbook that contains the sheets you want, enter placeholders and default text (e.g. page headers, column and row labels, and so on), add formulas or macros, apply styles and other formatting that you want to see in all new workbooks created with this template.
• Excel Worksheet template. This template type assumes just one sheet. So, delete 2 of the default 3 sheets in a workbook and then customize the remaining sheet to your liking. Apply the desired styles and formatting and enter the information you want to appear on all new worksheets based on this template.

Once you've decided on your default template type, proceed with the following steps.

1. In the workbook that you want to become your default Excel template, click File > Save As.
2. In the Save as type box, select Excel Template (*.xltx) from the drop-down list.
3. In the Save in box, select the destination folder for the default template. This should always be the XLStart folder, no other folder will do.

   In Vista, Windows 7 and Windows 8, the XLStart folder usually resides in:
   C:\Users\<User name>\AppData\Local\Microsoft\Excel\XLStart

   In Windows XP, it is usually located in:
   C:\Documents and Settings\<User name>\Application Data\Microsoft\Excel\XLStart

4. Finally, give the right name to your Excel default template:
   • If you are making the workbook template, type Book in the File name
   • If you are creating the worksheet template, type Sheet in the File name

   The following screenshot demonstrates the creation of the default workbook template:
   

5. Click the Save button to finish the process and close the window.

And now, you can restart your Excel and see if it creates a new workbook based on the default template you've just set.

Where to download Excel templates

As you probably know, the best place to look for Excel templates is Office.com. Here you can find a great lot of free Excel templates grouped by different categories such as calendar templates, budget templates, invoices, timelines, inventory templates, project management templates and much more.

Actually, these are the same templates that you see in your Excel when clicking File > New. Nevertheless, searching on site may work better, especially when you are looking for something specific. It's a bit strange that you can filter the templates either by application (Excel, Word or PowerPoint) or by category, not by both at a time, and still you should have no problems with finding the template you want:

To download a particular Excel template, simply click on it. This will display a brief description of the template as well as the Open in Excel Online button. As you may expect, clicking this button creates a workbook based on the selected template in Excel Online.

To download the template into your desktop Excel, click File > Save As > Download a Copy. This will open the familiar Windows' Save As dialog window where you select a destination folder and click the Save button.

For more information, please see how to create web-based spreadsheets in Excel Online.

Apart from Office.com, you can find plenty more web-sites offering free Excel templates. Of course, the quality of third-party templates varies and some may work better than others. The rule of a thumb is to download templates only from the web-sites you fully trust.