Templates included with Office are professionally-designed Excel files that you can adopt and adapt for your own purpose. The benefit of using a template is that it already works well and looks good. This can save a lot of time and give a better result than starting from scratch. Even if you build your own, experimenting with templates is a great way to get ideas to reapply. Try out what you have for free, and to know your base competition.
Use a Template
To use a template, go to File-New and browse the thumbnails. Here in the “back office”, you’ll see select templates at top, and at bottom, tabs for Office-provided templates and Personal templates. A template is a special file type, extension .xltx, that once opened becomes a standard .xlsx file. Thus you are not opening the template, you are applying it to a new workbook.
You can also open a template while in a workbook, without creating a new one. Right-click on the sheet tab and select Insert... to select from templates. Whatever sheets are in the selected template will be inserted into the current file. Interestingly, this method does not allow folder navigation, and includes personal templates in the startup folder but not in the personal templates folder.
Explore!...
As you experiment with various types of templates, you’ll probably see some interesting techniques that you want to adopt yourself. Explore for hidden treasures. Use the Find & Select menu, Alt-H,F,D, to select parts of the sheet with certain features, to check for learning and reapplication opportunities. This can also apply to auditing your own or a shared file.
Select Formulas, Alt-H,F,D,U: while selected, read the formula, then hit Enter to go to the next, and so on. Learn how they’re constructed, and if there are any named references in them.
Select Conditional Formatting, Alt-H,F,D,C: while selected, open the Conditional Formatting Rules Manager, Alt-H,L,R, and scroll through the rules to understand how they are constructed, by value or by formula, and the output formats and ranges.
Select Data Validation, Alt-H,F,D,V: while selected, open Data Validation, Alt-A,V,V, but beware the warning you’ll receive if there are more than one rule in your selection; the only options are to erase all rules or cancel. If this happens, cancel. Select an individual cell, open Alt-A,V,V, and check the box to “Apply these changes to all other cells with the same settings” to select the rule’s range. Review the three tabs to see the rule, the input message and the error message, then cancel. Repeat with other ranges until you understand the Data Validation model.
Name Manager, Ctrl+F3: see what kind of convention is used, scope, fixing type, and if there are any named formulas to be learned.
Templates are a good source of learning, because you can easily enter some data and see the effects. In addition to internal inspection, look at it aesthetically. How do you like it? These are vetted, so they should be fairly attractive. What might you do differently? Keep a healthy sense of confidence that you can do better, and plagiarize at will. If a template is close to what you need, resist the urge to reinvent, or tweak it only as much as needed.
Create a Template
If you find yourself frequently repeating the same steps to set up a sheet, consider creating your own template. Before you start, check your default template folder. Go to File-Options-Save, Alt-F,T,S, and look for “Default personal templates location”. The standard is ...\Documents\Office\ Templates.
To create your own template, start from an existing template or from scratch, and create the workbook template-to-be. Spend extra time on formatting, and add just enough data to demonstrate the functions and formats. Once you are satisfied with it, F12 to save. Open the “Save as type:” drop-down menu, and select “Excel Template (.xltx)”. This automatically selects your template folder. You can also save templates from the File tab, but you will need to navigate to the template folder.
Once you have saved it, close the file and take it for a test drive. Go to File – New, open the Personal folder, Alt-F,N,Y,3, and select the template. See that the file name has a 1 appended to it, and F12 defaults to Excel Workbook.
Two standard templates require special attention. They are normal templates, but have prescribed names and are stored in the startup folder instead of the templates folder. They are completely optional and can be used separately or together.
XLSTART\Book.xltx becomes the default for new workbooks e.g. via Ctrl+N. The new workbook is numbered with the next available number, like Book1.xlsx, as with any template.
XLSTART\Sheet.xltx becomes the default for new worksheets e.g. via Shift+F11. The new worksheet is numbered with the next available number, like Sheet2, etc.
These are personal defaults and like your startup file, are to be shared cautiously. A user should know that they are adopting their own preference, and understand how to revert to Excel defaults by deleting or renaming files in XLSTART.
For your own use, devise a strategy that gives you the most convenience and flexibility. Set up your favorite sheet as the default “XLSTART\Sheet.xltx”, and save other templates in your startup folder as well. This makes them available via right-click on the sheet tab → Insert.
If you want to share a template for repeated use, provide instructions to save it to the user’s default template folder, or share it as a normal workbook, with instructions to convert it to a template by F12 as explained above.
Note the behavior, if you open a template file from recent files, it will reopen as a template file, and default save-as a template file. If you select the template via File-New, or open it via Windows Explorer, the name will have a number appended to it, and save-as will default to Excel Workbook. Be conscious of this when instructing users.
If your template includes macros, then save as “Excel Macro-Enabled Template (.xltm)”. This will subject it to the same macro security as a macro-enabled workbook (.xlsm). Make sure your users are ok with that. Treat your template like an app, and test it thoroughly with enough data to ensure it will perform reliably. Use it yourself many times before you share it.
To edit a template, either open it from recent or pinned files, or open it as a new workbook, make the changes, and use F12 to save it again, overwriting the same template name.
* Note that inserting template sheets includes VBA code that resides in the sheet, but not in other modules. Plan ahead to store code in the sheet if you want to use it as an Insert-Sheet template.
Comments