If you’ve spent the time to create a template in Excel, it’s important to save it in the right format to help protect your work. While the temptation may be to just save it as the default .xlsx or.xlsm format, you’re best off saving it in the correct template format – .xltx or .xltm, and here’s why:
1. You avoid easily overwriting the file
One of the biggest mistakes you can make with a template is not backing it up. As much as you can test a template to make it as error-proof as possible, someone usually will find a way to make it not work the way it was intended. If someone uses the live template file then that’s just asking for trouble. Once data is entered on the file and things are moved around, if something is not working at a later point in time it’s hard to go back and try and figure out what went wrong, especially if the structure or file was changed in some way.
One way I normally mitigate this is by saving the template somewhere else and have someone use a copy of it. If something has gone terribly wrong and formulas have been altered, it’s a lot easier to copy the data back into something that you know was working (the file that was backed up) than it is try and troubleshoot on a modified file where you’re unsure as to what has changed.
Saving it as a template file (.xltx or .xltm – for templates with macros), however, will allow you to mitigate the potential for someone to save over the template by accident. By saving it as one of these formats, when a user opens the template file it will automatically change the name to add a number to it and when they go to save it won’t save as a template file (unless they manually change it). While a user can still technically save over the template file, it’ll take some work to do so. In all likelihood, the user will end up saving it as a non-template file. Either way, you’ll still probably want to save a backup file somewhere else, but the risk of being overwritten should be minimized this way.
2. It makes it easy to find your templates on your computer
If you have your Excel files, including templates, in one folder, it becomes easier to sort and identify the templates by just looking for those that end in .xltx and .xltm. What you can do is sort by file type or when you go to open a file select the type of file you’re looking for. This will help avoid opening the wrong file.
If your files aren’t all in one location, you could also do a search of files ending with .xlt across a range of folders to help find all your templates. This could make it easier than to add the word ‘template’ into every one of your templates.
3. Easy access for users
By default, when you go to save a .xltx or .xltm file, Excel will select the templates folder. You don’t have to save your files there, but by doing so your templates will now show up when a user goes to create a new file and looks for templates. Your saved templates will be available by selecting the Personal section:
By being able to access the templates when creating a new file, a user doesn’t have to worry about finding the location of a template. If you don’t want to use the default location that Microsoft has assigned for templates, you can change the folder in the Excel options under the Save section:
This way you can also have all your templates in a shared location among many users. The user would simply need to change these settings to direct Excel to the correct folder.
Add a Comment
You must be logged in to post a comment