A key aim for many people trying to understand more advanced Excel techniques is to be able to complete projects more quickly and efficiently. One way of doing this is to use prefabricated templates so that you don’t have to go through the hassle of formatting each individual document you create to fit the style required.
Many Excel templates are available online and you can search for templates in particular categories. Excel also provides a series of prepopulated templates which appear when you create a new workbook.
Creating a file from a template
Going to the FILE tab, New section and choosing the My templates option displays the user templates folder so you can select the file to base our new workbook on:
It’s a good idea to check that external templates operate as you intended and ensure that you understand all the techniques and formulae employed within the template.
New workbooks are based on the template selected but they remain new workbooks in their own right so changes do not directly affect the template – you will be prompted to save the new file as normal.
Creating a new template
Excel templates have an XLTX, XLTM (macro-enabled template) or XLT (previous versions of Office) file extension rather than XLSX. To save a file as a new template for use in the future, choose File, Save As, navigate to the desired folder and from the Save as type dropdown choose one of the extensions denoting a template. The save location changes automatically to the templates folder:
It’s possible to change this default location to one of your choosing by setting the path in Excels Options, Save section.
Default worksheet and workbook templates
You can control the format and content of new workbooks and new sheets by creating default Excel templates.
These are created in exactly the same way as the templates described immediately above, but they need to be saved in a particular location and given particular names. Both need to be saved in Excel’s startup folder. The location of the startup folder can vary but will usually be somewhere in the Program FilesMicrosoft Office or Program FilesMicrosoft OfficeOfficexx folder. You can locate the xlstart folder by choosing the following options:
File, Options, Trust Centre, Trust Centre Settings button, Trusted Locations
In the User Locations section find the Path for:
Excel 2010 (2007) default location: User StartUp
The default workbook needs to be called Book.xltx and the default worksheet Sheet.xltx.
Creating modular workbooks
When you insert a new worksheet, you’ll be able to choose from any existing templates saved in your default templates, or the Workgroup template location. Although these locations cannot be viewed or set directly from Excel, they can be edited in Microsoft Word by going to Word Options, Advanced and scrolling to the General selection almost at the bottom of the list which has a File Locations button.
One approach, to both speed up the creation of workbooks and to create a house style and improve reliability, is to create a ‘library’ of different sheets – either individual sheets, or blocks of sheets, that can be inserted into either a new workbook or an existing one.
For example, if you need to improve the documentation of existing spreadsheets you could right-click on a sheet and choose to insert a new sheet based on a template that is made up of a group of sheets such as:
- Checks and controls
The idea could be extended to ‘modularise’ the creation of workbooks for particular tasks – so you could have forecast P&L account sheets and balance sheets to be added to cashflow forecasts when needed, for example.
Alan Gurney is AAT Comment’s Excel tips writer.