by Svetlana Cheusheva, updated on
Microsoft Excel templates are a powerful part of Excel experience and a great way to save time. Once you've created a template, it will require only minor tweaks to suit your current purposes and therefore can be applied to different scenarios and reused time and time again. Excel templates can also help you create consistent and attractive documents that will impress your colleagues or supervisors and make you look your best.
Templates are especially valuable for frequently used document types such as Excel calendars, budget planners, invoices, inventories and dashboards. What can be better than grabbing a ready-to-use spreadsheet that already has the look and feel you want and can be easily tailored for your needs?
That's what a Microsoft Excel template is - a predesigned workbook or a worksheet where the main work has already been done for you, saving you from reinventing the wheel. What can be better than that? Only free Excel templates :) Further on in this article, I will point you to the best collections of Excel templates and show how you can quickly make your own ones.
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:
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.
In Excel 2010, you can either:
That's it - the selected template is downloaded and a new workbook is created based on this template right away.
To get a bigger selection of templates for your Excel, type a corresponding keyword in the search bar, e. g. calendar or budget:
If you are looking for something specific, you can browse available Microsoft Excel templates by category. For example, see how many different calendar templates you can choose from:
Note. When you are searching for a certain template, Microsoft Excel displays all relevant templates that are available on the Office Store. Not all of them are created by Microsoft Corporation, some templates are made by third-party providers or individual users. This is the reason why you may see the following notification asking if you trust the template's publisher. In case you do, click the Trust this app button.
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:
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.
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.
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.
Some Excel users might be happy seeing the collection of templates suggested by Microsoft every time they open Excel. But what if you've always wanted YOUR templates and never what Microsoft recommends?
The good news is that the templates you created in earlier Excel versions are still there. Like in previous versions, modern Excel automatically stores a copy of each new template in the default templates folder. All you need to do is to bring the Personal tab back. And here's how:
The easiest way to make the Personal tab appear in Excel is creating a special folder to store your Excel templates.
As you see, this is a very quick and stress-free way. However, it does have a very significant limitation - every time you make a template in Excel, you do have to remember to save it into this particular folder. And this is the reason why I like the second approach better : )
Instead of creating a custom folder to store your personal Excel templates, you can find the one to which Microsoft Excel stores templates automatically and set it as Default personal templates location. Once you do this, you will find all newly created and downloaded templates as well as those you've created earlier on the Personal tab.
Tip. If you have difficulties with locating this folder, click Start and type (or even better copy/paste) the following command in the search box:
%appdata%\Microsoft\Templates
The Template folder will appear in search results, so you simply click on it and copy the path as explained above.
And now, whenever you click File > New, the Personal tab is there and your custom Excel templates are available for use.
It looks like Microsoft received so many complains about a mysterious disappearance of personal templates in Excel, that they took a trouble to create a fix. The fix applies the solution described in Method 2 automatically and is available for download here.
The main advantage of this approach is that it works for all Office applications, not only Excel, meaning you won't need to specify the default template location in each program individually.
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:
Once you've decided on your default template type, proceed with the following steps.
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
The following screenshot demonstrates the creation of the default workbook template:
And now, you can restart your Excel and see if it creates a new workbook based on the default template you've just set.
If you are not sure where exactly the XLStart folder is located on your machine, you can find it in two ways.
In Microsoft Excel, go to File > Options, and then click Trust Center > Trust Center Settings:
Click Trusted Locations, find the XLStart folder in the list and click on it. The full path to the folder will show up underneath the trusted locations list.
Please note that the trusted location list actually contains two XLStart folders:
C:\Users\<User Name>\AppData\Roaming\Microsoft\Excel\XLStart\
C:\Program Files\Microsoft Office\<OFFICE VERSION>\XLSTART
When copying the XLStart folder's path, please double check that you've picked the right one.
An alternative way to spot the XLStart folder is by using the Immediate window in the Visual Basic Editor:
As you see in the screenshot above, this method always returns the location of the personal XLSTART folder.
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.
Note. The downloaded file is a usual Excel workbook (.xlsx). If you'd rather want an Excel template, open the workbook and re-save it as Excel Template (*.xltx).
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.
Now that you know what Microsoft Excel templates are and what benefits they provide, it's the right time to make a couple of your own ones and get a head start with new features and techniques.
Table of contents