Cookies in Use Read more: Cookie Button:Get a free "Cookies in Use" button for your website | Attacat of Edinburgh



 © Roy Cox 2005 to 2013

Our Privacy & Cookie Policy

How to use Excel Templates

What is a Template?

A template is a Microsoft Excel workbook with content and formatting that you use as a model to create other similar workbooks. You set options and enter data in a template, and then when you base new workbooks on the template, the new workbooks receive the same settings and initially contain the same data. You can also add VBA code to the Template. Templates save you time when you need to create several workbooks with similar features, and can help you standardise the appearance and content of workbooks used in your company. Because a template provides only the information and settings you want to reuse, it's faster than copying an existing workbook and deleting old information from it. For example, you want everyone in your Company or Department to provide information each month about time spent on individual projects, i.e. a record of time writing costs. You combine this information into a management report, so you want the same information in the same order from each employee. To make this task easy, you can set up a worksheet with a row for each project and columns for each day of the month, apply any other formats you want, add any calculations that you need, protect the Worksheet and then save the workbook as a template.

How to create an Excel Template                                        

  1. Open the File menu and select Save As.
  2.  In the "Save as type" box, click Template (*.xlt).
  3. In the "Save in" box, select the folder where you want to store the template. To create a normal template, select the Templates folder, which should have automatically opened when you chose the Template type in the previous step. You can if you prefer select an alternative Directory
  4. >To make this template the default (so that it's automatically selected), save the folder in the XLStart folder. The XLStart folder should be located in the following place: C:\Windows\Application Data\Microsoft\Excel\XLStart.
  5. In the "File name" box, type a name for the template (like "Monthly Projects template"). Use the name " book" if you want this template to be the default.
  6. Click Save.

Every month, your employees can each create a new workbook from this template, fill in the information, and send it to you. By using the template, everyone automatically gets the information in the right order and format, so it's easy for you to compile your monthly time writing report.

How to Modify a Template

How do templates work?

Templates are a separate Excel file type, with extension .xlt or in the newer version xltx for non macro workbooks or xltm for templates containing macros, that you can specify for Microsoft Excel to use when you create a new workbook. You can use templates to create entire workbooks or to create new worksheets within existing workbooks. Microsoft Excel provides several options for making templates available. You can make a template available as the default, so it's used for every worksheet or workbook you create. If you want to use a template occasionally, you can make it available for your personal use. To share a template with other users, you can locate it in a shared template folder on your network. Once you make a template available, it becomes one of the choices in the New dialog box when you click New on the File menu. When you select a template in this dialog box, Excel then creates a new workbook that automatically duplicates the settings and other contents that you saved in the template. This is only true if you save to the default Directory.

What information can a template include?

Features that you can save in a template for replication to new workbooks include formatting, styles, standard text including page headers and footers, macros, and more.For information about the settings and other information Microsoft Excel can save in a template, type settings saved in a template in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click search.

Creating a template in Excel 2007 or 2010

Because of the new File Formats introduced with Microsoft Office 2007 you need to select the correct type of Workbook to save as in Excel 2007. This applies to creating an Excel 2007 Template. When you have finished designing your Workbook. Follow these steps to save your new file as an Excel 2007 Template:

  1. Click on the  to display the available
  2. In Excel 2010 the File option returns
  3. Choose the Save As option
  4. Choose the Other formats option
  5. Choose the Save As option to open the Save As dialog box
  6. Click on the Save as type option to open the drop down list
  7. Scroll through the list to find the template options

Template options in Excel 2007 & 2010

There are three templates that can be created in Excel 2007:

If your template contains macros then choose the Macro Enabled Template (*.xltm) option. If you plan to use your template with older versions of Excel select Excel 97 - 2003 Template (*.xlt) option. For all other templates  choose the Excel Template (*.xltx) option. Once saved, your template is stored in the templates folder as described previously