Excel templates are a useful way to reuse structure, content, and formatting among many different projects. You can save your own workbooks as custom templates, or use templates from other sources. You can use a template in two ways. You can create a new file based on the template, and you can insert sheets from a template into an existing workbook.
Creating Excel Templates
Creating a template is straightforward. When you have a workbook (one or more sheets) configured the way you want it, simply use Save As, and under Save As Type, select Template (*.xlt). If you are using Excel 2007, you have two template options: Excel Template (*.xltx) and Excel Macro-Enabled Template (*.xltm). If your template contains any VBA that you want to use when the template is used later in a workbook, be sure to select the macro-enabled option.
By default, Excel saves the template in the Template directory, which in Windows XP and Office 2003 is:
C:\Documents and Settings\username\Application Data\Microsoft\Templates
This directory contains Excel templates, as well as Word and PowerPoint templates.
Creating a New Workbook from an Excel Template
There are at least three ways to create a new file based on a template. In all of these techniques, the result is a new file that opens in Excel. This file is exactly like the template, except instead of having the template’s file name (“MyTemplate.xlt”), it is unsaved but has a default name based on the template’s name (“MyTemplate1”). All sheets and code from the original template are at your disposal in the new workbook.
1. You can double click on an Excel template in Windows Explorer, and the new file will open in Excel.
2. You can create a new file in Excel using File menu > New (or in Excel 2007, Office menu > New), using the option to create a new workbook based on an existing workbook. A File Open dialog opens in the Templates directory (see above) where your templates are saved. You can browse anywhere to find your template. You can use a regular workbook as well as an official template ass the basis for your new file.
3. You can create a new file from a template, and Excel 2003 gives you the option to search for a template, use a template from Office Online or from your computer, or select from a list of recently used templates. If you choose the Office Online option, the Template Categories page from Office Online opens in your default browser. If you choose the Templates on this Computer option, a Templates dialog appears with tabs for different directories, opened at first to the Templates directory. Subdirectories of Templates appear on the other tabs. You are limited to the directories available on the tabs.
Inserting New Sheets from an Excel Template
You can insert sheets from a template into the active workbook by right clicking on a sheet tab and choosing Insert. The same Templates dialog as in option 3 above will appear so you can select a template. All sheets from the selected template are inserted in front of the active sheet. Any code contained in the code modules associated with these sheets is copied with the sheets, but workbook or project level code is not transferred into the parent workbook.
Sample Uses for Excel Templates
There are many ways that templates make your spreadsheet life easier. Rather than reconstructing the data range and charts in a workbook, you can save the original workbook as a template, use the template to create a new workbook, and enter new data into the new workbook.
If you create regular reports or analyses, you can create an array of templates, one for each section your analysis or report may contain. As you populate your report, you can add the sheets you need from the available templates, and ignore the sheets you don’t need.
You could use a template to insert boilerplate (copyright notices, contact information, cover pages, etc.) into workbooks.
Often, people use a template to carry out an analysis quickly and easily. Finding an existing template is easier and more reliable than trying to construct a page full of formulas by hand. There are many templates available on the internet.
Sources of Excel Templates
Microsoft hosts a large number of templates on their Office Online Templates pages. A number of other sites have templates available for download.
The Vertex42.com web site hosts a number of nicely made Excel Templates. For example, the Amortization Chart template has a simple data entry area where you can input amount of a loan, the interest rate, and the number of periods over which the loan will be repaid. A table below the input region is populated with data, and a pair of charts show the status of the loan over time.
This example shows how a relatively simple template can be an elegant way to carry out an analysis with a minimum of difficulty.
Rob says
Templates are curiously underused in Excel. I’m not sure why.
I’ve lost count of the number of times people have asked me how to copy sheets and VBA from their application workbook “in order to create a new, blank one based on it”. A template is the ideal solution on these occasions, but it seems rarely to be considered, maybe because creating a new workbook via a ton of VBA code is somehow more satisfying.
sam says
Templates are also a way for creating Data entry forms that can be used to update central databases…(Purchase orders, Sales Orders, Time sheets ect)
In the good old days of Excel 2000 there used to be a built in Addin called Template Wizard with Data tracking…..Instead of developing it further MS decided to remove it from XP onwards. (Its still available as a separate download)
Eric Shilling says
Got here searching for Excel help on the web. Haven’t used templates before, but I made one that shows my data the way I want it. Now I want to load my next raw data file into the template.
I was hoping my template would break up the text strings into the columns defined in my template, but I guess that was a little optomistic.
Is there a way to parse a few 20 character strings in a .csv file into a bunch of shorter strings in separarate Excel columns?
I guess I am trying to avoid having to define the field widths in the text import wizard every time I import a file.
Anyway, this ‘breaking up of strings into fields’ is a part of templates that I haven’t seen much discussed.
Eric
Jon Peltier says
Eric –
This is part of the Text to Columns functionality on Excel’s Data menu. However, if you are splitting into fields based just on the number of characters, Text to Columns may not be the easiest way to handle this task. If you always have the same number of characters in each column, you could simply use formulas to do the splitting. For example, put a string into cell A1.
To get the first five characters from A1 into B1, put this formula into B1:
=LEFT(A1,5)
To get the next three characters into C1:
=MID(A1,6,3)
where 6 is the starting character.
And so forth.
Eric Shilling says
Thanks Jon,
Yes, the Text to Columns function does the job but I don’t know any way to capture or save that specific setup. I’d rather not have to define the column structure to the wizard each time I import a new .csv file.
I think I’ve made a template with text functions that pick up my substrings in each of the right columns. [Saved it into ‘My Templates’].
I’ll try it, but now I can’t grasp how to open the .csv file into the new template. It keeps being opened into the normal Excel template and that kills one of the strings into scientific notation. (The file has several long strings, separated by commas; some are all digits.) I keep thinking it’s going to be intuitive from here on.
Thanks again,
Eric
Jon Peltier says
CSV files just open into flat worksheets. What you do is copy the contents of the CSV file and paste it into the template. If this is not appropriate, a little code can always be used to extract the CSV data, split it, and dump it into the template.
Greg Kinzle says
hey if I have a workbook full of data and make a template out it it keeps all the data in it is there a way to save as a template and not have all the data from the workbook be in there? Then I could start with a new empty workbook
Thanks
Greg
Jon Peltier says
Greg –
You can open the template as a template (not as a new file) by dragging it from Windows Explorer onto Excel. Then edit the template, remove the data or add dummy data, then save it with these changes.
deb says
I keep trying to find out what macro-enabled template means, and if it does mean getting a macro to execute when running a template, how to set that up.
In particular, this is for the sake of doing something you can do straightforwardly in Word, which is to create a date that dynamically responds when you create a document from the template, but then stays put and doesn’t change every time you open the document (in Word, printdate).
After an inordinate amount of looking, a macro seems to be the only way to do that in Excel – but I have yet to get clear how to associate the macro with the template. Can it only be done through writing a small VBA (?) program, or can you insert an existing macro? (I realize that is a small program, but Excel has captured it from keystrokes, I haven’t had to write it from scratch.)
Thanks –
Deb
Jack says
Excel is the way to go for me. I use it to track anything important. Money, business, food, etc.
Jerry Myers says
I created a custom Excel template in Excel 2010 with XP, it is filled with data. How do I save it as Sheet 1 in a Workbook and still have a blank template left over. I have hours and many attempts trying to complete this, seemingly simple, process.. Thank you for your help. Jerry
Jon Peltier says
Jerry –
Did you create a standalone Excel template (an xltx or xltm file)? Or is it a regular Excel workbook (xlsx or xlsm) that you use like a template? I also don’t understand saving a template as Sheet1 in a workbook, or having a blank template left over.
Helal says
Is there a way to apply custom template to an existing report in Excel 2010/2013? another words, I have created a SSRS report but when export it to Excel formatting is changed. I then formatted the report the way needed and saved it as custom Template. I like to be able to apply this template to all reports being exported from SSRS. Any suggestions?
Jon Peltier says
Helal –
Without writing code, there’s no way to apply a template to more than one chart at a time. I don’t know about SSRS, so I don’t know if there’s anything you can do at an earlier stage to make the charts the way you want them.