How to Create Custom Templates in Microsoft Excel?

Microsoft Excel is one of the most popular data processing applications for Windows users. Even you can use it on Mac instead of the Apple’s Numbers app. Sometimes, you may need to frequently prepare same Excel worksheet with different set of data. In such a case, Excel allows you to create a template and reuse instead of creating a new file each time. In this article, we will explain how to create a custom template in Microsoft Excel and save time.

Related: How to use Mail Merge in Word and Excel?

Why to Create a Template in Excel?

Depending up on your need, there are many reasons to make use of templates in Excel.

• Teachers can create a student’s mark sheet template and reuse the same template for filling marks during different exams.
• Salespeople can create a template for keeping track of their customers and revenue.
• Small companies can maintain templates for invoices, payment, inventory, etc.

Many users copy paste the previous workbook and modify the data when they wanted to reuse the same template. This can easily lead to mistakes or confusion when processing large amount of data. Therefore, you can create an empty or skeleton template with formulas and use it error free. In addition, you can also use templates for creating documentation or tutorials that you want to show in Excel worksheet instead of Microsoft Word document.

Templates in Microsoft Excel

In this article, we will discuss Microsoft Excel app that comes as part of Microsoft 365 subscription. However, you can follow the similar process when using independent Excel app. Whether you use Windows or Mac, Excel offers two ways of using templates:

1. Download and use predefined templates from Microsoft
2. Create your custom template
3. Use online templates in Excel

1. Using Predefined Microsoft Templates

Microsoft 365 subscription comes with large number of inbuilt templates. We recommend using these templates since they can save huge amount of your time.

• Open Microsoft Excel and when you are in the backstage view, you will see few available templates in “New” section.

• If you are not seeing the templates, click on “Home” tab and then click on “New blank workbook” button. This will expand that section and show you all available templates.

• Click any template to get more details about it. If you like the template, click on “Create” button to start downloading the template to your computer. For example, in the below screenshot we will download the “Formula tutorial” template.

• After successful download, the template will open and you can start editing the content.

• As you can see the template has plenty of sheets with beautiful content. Creating such content may take weeks for users and making use of these templates can save that time.
• The “Home” tab will show only few items like previously downloaded or used templates. You can click on the “More templates” link showing below the templates or go to “New” tab to view additional templates available for download.

• Again, the list does not contain all templates and you can use the search box to find your favorite templates from Microsoft. For example, search for “calendar” to see more than fifty different templates which are not shown before.

• You can download any calendar template and update the content using inbuilt formulas. For example, after downloading the “Phases of the moon calendar”, it will suggest you with some help for changing the year. You can change any year between 2017 to 2030 and get the moon phases updated as per the calculation logic in the “Moon Phase Data” worksheet.

• Therefore, you can simply change the year and reuse the template without making any data change. Similarly, you can download invoice, budget, and personal planning templates and reuse to save huge time.
• After making changes, make sure to save the file to local computer or OneDrive. You can save the file as normal Excel workbook format (.xlsx) or as Excel template format (.xltx). We recommend saving in Excel template format so that you can reuse when needed as a template without downloading again from Microsoft.

Note: You can view templates by going to “File > Home” or “File > New” menus when you are working with an Excel file. In addition, if you are not saving the downloaded template file or not making any changes to the template, every time you need to download it for viewing the content. It will not be stored in your computer.

Related: How to insert Excel tables in Word documents?

2. Creating Custom Templates in Excel

Though predefined templates are useful, you can also create a custom template as per your requirement. This will be a one-time task and you can reuse the template whenever needed. This is recommended when you have own style guide for your company to use custom fonts and styles that are not part of default templates.

• Open a blank Excel worksheet and create your own template. Alternatively, you can also modify any existing templates from Microsoft. Let us create a company invoice template as shown below.

• After your template is ready, go to “File > Save” or “File > Save As” and choose “Browse” option to open “Save As” dialog box.

• Click on the “Save as type” dropdown and select “Excel Template” option.

• Provide a file name (we use the file name as “Simple Invoice”) and save your file as Excel Template which will be with .XLTX file extension.
• The default location to save Excel template is C:\Users\<Username>\Documents\Custom Office Templates. We recommend not changing this location so that you can see all the saved templates when needed in one place.

3. Using Online Excel Templates

You can click on the “Templates on Office.com” button in the “Insert” dialog box to go to official Office website. Alternatively, you can directly open Office website in Edge to view all online Office templates.

Login to your Microsoft account used for purchasing the subscription and download any template you like.

After successful download, you will see a notification showing file name with .xltx extension.

Click on the folder icon to open the downloaded file folder. Alternatively, you can open File Explorer and find the downloaded file yourself. It should show with the type as “Microsoft Excel Template”. You can also download the .XLTX template file from any other trusted third-party websites.

We will rename the file name as “Timeline Template” so that it is easy to understand when seeing the file name. Now, copy the template file and paste it in the folder “C:\Users\<Username>\Documents\Custom Office Templates”. It should look like below in File Explorer.

Reusing Templates in Microsoft Excel

There are two ways to use Excel template file.

Creating Copies from Templates

This is like how you have created a template file copied from the Microsoft templates. You can reuse the custom or Microsoft templates and save as a new Excel file.

• When you open Excel app, it will show a new “Personal” tab containing all your saved templates.

• Open your template file and start editing the content.
• Finally save it as a new Excel file and send in email or share with your colleagues.

Inserting Templates in Another Excel File

Sometimes you may also need to insert a template inside another Excel file as a new sheet.

• Open the Excel file you want to insert your template.
• Right click on any of the sheet tabs showing at the bottom of the file and select “Insert” option.

• Under “General” tab, you can find all your Excel templates both custom created as well as downloaded and modified from Microsoft templates. In the below screen, you can see our custom “Simple Invoice” template and “Phases of the moon” downloaded from the Microsoft template section.
• Select the template to preview on the right pane and click on “OK” button insert it in your file as a new sheet.

• The sheet name will be same as what you have used in the template file.
• The insert dialog box also contains a “Spreadsheet Solutions” tab. It has some default templates for billing statement, blood pressure tracker, sales report, etc. that you can insert in your file.

Note: Excel app shows “Personal” template files available from C:\Users\<Username>\Documents\Custom Office Templates folder. This is the reason you should save all your templates in this folder so that it is easy to access them. However, sometimes you will not see the templates uploaded manually inside the folder (in our case, you do not see “Timeline Template” file)”. In this case, you have to open the template as a new file and copy paste the sheet to another file.