Creating New Custom Excel Templates

AUTHOR:  JASON MORRELL 

Jason Morrell

Reading Time:
  minutes remaining

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:

  • Workbook template.
  • Worksheet 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:

  • The no. of worksheets that the book starts with (and their names.)
  • Formatting of the worksheets.
  • Macros and styles.
  • Page setup.

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.

Creating a new workbook using an office.com template

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).

Creating your own custom Excel templates

The folder has switched to a dedicated Custom Office Templates folder. Do not change this location.

Save your custom Excel templates in the default Custom Office Templates folder. Do not change the 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.

This is how to create a new workbook using you new custom Excel template

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.

The title bar of the new workbook shows the custom Excel template name with a default number added 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

  • Templates are used to establish a standard layout, define standard styles, set up working macros etc., An example would be a blank form. You can create workbook templates or worksheet templates.
  • To save a regular workbook as a template, click the File tab choose Save As and then Excel Template from the Save Type As drop down list.
  • If your template has working macros, save it as an Excel Macro Enabled Template.
  • To create a new workbook based on an Office.com template, click the File tab and choose New. Click the FEATURED tab above the templates gallery and select a template.
  • To create a new workbook based on a custom template you have previously saved, click the File tab and choose New. Click the PERSONAL tab above the templates gallery and select a template.
  • To modify or delete a template, navigate to the templates folder and then open the file as normal.

Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! 

ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.

Enjoy the rest of your day!

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell


Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE

Office Legends Academy | Making Office Legends

Master Microsoft Office
in 30 days or less

more from

Office Mastery logo
Read More
SOS-Click: The Easy Automated Backup Tool For Home Users
Read More
6 Excel Templates to Help Organize Your Daily Activities
Read More
10 Ways Excel Consultants Can Help You Fully Leverage Your Business Spreadsheets
Read More
How to fix formulas: 21 Fatal Formula Fails (and how to avoid them)

JOIN THE DISCUSSION

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>