MAKING EXCEL WORK FOR YOU
© Roy Cox 2005 to 2013
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
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:
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
|The Personal WorkBook|
|Excel Sheets in VBA|
|Excel Macro Recorder|
|Excel Camera Tool|
|Excel Summarise Data|
|Excel CHOOSE Function|