In this post you'll discover you'll discover that creating custom Excel templates removes repetition and brings consistency. This in turn which makes your data analysis easier and saves you time.
RELATED POST:
Where to find pre-made project management Excel templates
NEED HELP?
Want an expert to build your custom Excel templates? Enquire here.
1. What are custom Excel templates used for?
Templates are incredibly useful for replicating tasks that are frequently carried out when a spreadsheet is first created, e.g. if the same formatting is always used or the company logo is always placed in the top left cell.
Templates should be set up with little or no data within them, unless this data is required every time.
There are two types of template:
2. Workbook templates vs worksheet templates
A workbook template applies workbook-wide. When selected it opens a new workbook based on the template. The following list contains suggestions for things that could be included in a workbook template:
A worksheet template applies at sheet level only. A worksheet template contains only one sheet.
If it is saved as SHEET.XLTX or SHEET.XLTM in the \ XLSTART folder, this template is used whenever a new worksheet is inserted into a workbook.
To save a worksheet template, set up a workbook with only ONE worksheet, then save it as a workbook template.
3. How to create a new workbook using an Office.com template
1. Click the File tab.
2. Choose New.
3. A large gallery of professionally designed templates is displayed. Choose one.
4. A new workbook is created that is based on the template. You can do whatever you want to this workbook. When you are done, you can save it like a regular file.
4. How to create your own custom Excel templates
With the workbook that you wish to save as a template:
1. Click the File tab, then Save As then Browse to display the Save As dialog.
2. In the Save as Type box select Excel Template (.xltx).
If your workbook contains macros, choose Excel Macro-Enabled Template (.xltm).
The folder has switched to a dedicated Custom Office Templates folder. Do not change this location.
3. Type a name for your template then click Save.
5. How to create a new WORKBOOK using your own custom Excel templates
1. Click the File tab.
2. Choose New.
3. Click the PERSONAL tab. These are your personal templates that you have saved.
4. Click a template thumbnail.
A new workbook is created. The workbook looks just like the template but IS NOT the template. A quick look at the title bar shows the workbook name with a number appended to the end.
5. Whatever you do to this workbook will not affect the template.
6. When you are done, you can save the new workbook like a regular file.
6. Modifying custom Excel templates
1. Click the File tab.
2. Choose Open.
3. Navigate to the template, using the pathname noted when the template was initially saved.
4. Modify the template as required.
5. Re-save the template.
7. Deleting a custom Excel template
1. Click the File tab.
2. Choose New.
3. Click the PERSONAL tab.
4. Right-click on the template and choose Delete.
8. How to insert an a new WORKSHEET based on your own custom Excel template
1. Right-click a worksheet tab then click Insert…
2. Choose the worksheet template from the gallery then click Ok.
9. Key Takeaways
I hope you found plenty of value in this post. I'd love to hear your biggest takeaway in the comments below together with any questions you may have.
Have a fantastic day.
About the author
Jason Morrell
Jason Morrell is a professional trainer, consultant and course creator who lives on the glorious Gold Coast in Queensland, Australia.
He helps people of all levels unleash and leverage the power contained within Microsoft Office by delivering training, troubleshooting services and taking on client projects. He loves to simplify tricky concepts and provide helpful, proven, actionable advice that can be implemented for quick results.
Purely for amusement he sometimes talks about himself in the third person.
SHARE
