MS Excel, short for Microsoft Excel, is the most powerful spreadsheet software used to record a large set of data and perform desired operations. When working with Excel, we regularly come across the same data or format. So we usually start working on the same layout or formatting multiple times for each worksheet or workbook. However, we can copy-paste the common data between the old and new sheets or workbooks. But, instead of copy-pasting, we can take advantage of Excel templates and save our time and effort while working with the same data and formatting in Excel.
This article discusses a brief introduction to Excel templates and step-by-step tutorials on using and creating them in Excel. In addition, we will also learn how to download Excel templates from the web.
What is an Excel Template?
Excel template refers to a special predefined spreadsheet that can be opened in Excel to continue working with specified formatting and predefined formulas. Excel templates are often used for calendars, event planning, invoicing, inventory, dashboards, budget planners, etc. These templates can be reused as many times as needed, and changes can be made accordingly. We need to make only a few or no changes to suit our needs with Excel templates.
Excel templates help us avoid cumbersome, repetitive tasks, allowing us to focus on the actual data while simultaneously saving time and effort. Excel templates are generally classified into the following three types based on their purposes:
- Default Sheet Template: The default sheet template (or existing sheet template) helps create a new worksheet with predefined adjustments.
- Default Workbook Template: The default workbook template (or existing workbook template) helps create an entire workbook with predefined adjustments. A workbook may contain multiple worksheets based on preferences.
- Custom Workbook Template: The custom workbook template is a predefined workbook template saved by the user. If the user needs to work on the same structure or format in the workbook regularly, the workbook adjustments can be saved as a custom template in the storage.
How to access built-in Excel Templates?
Excel comes with various templates already installed by default. It consists of templates of different categories required for personal use or Office use. These built-in templates are called standard templates that are best known for their ready-to-use feature.
To access or select the standard Excel template, we need to follow the below steps:
First, we need to click on the File tab on the ribbon and choose the 'New' option from the list.
After clicking the 'New' option, Excel displays various templates in the right-side section. In most cases, we usually choose the 'Blank Workbook' template. It is an empty template that allows us to work from scratch. Additionally, Excel displays many other templates for Budgets, Calendars, Forms, Invoices, Receipts, etc. These are built-in Excel templates.
We need to locate the relevant template that suits our requirement and double-click on it to start working on that template. To read more about any particular template, we can click on it once to view the description.
We can use the search box to locate/ find any specific template quickly. After double-clicking or clicking the Create button for any template, the same template will be immediately opened in the Excel window as any other typical workbook, but with predefined structure, inputs and formatting. For example, if we double-click on the 'Personal Monthly Budget' template, it is displayed in Excel like the below screen:
In the above Excel template, we can change everything as per our needs. We can add new rows columns, modify the existing data, change formatting like color, size, etc.
- Once we have done modifying the data or other sheet elements, we can save it as other typical Excel workbooks for future references.
In this way, we can access any of the existing/ default/ standard Excel templates and start manipulating the data accordingly to meet our requirements.
How to create a Custom Template in Excel?
Excel also allows users to create their custom template in Excel using which they can avoid working on repetitive tasks while saving time for productive operations. We can eliminate the need to type most of the complex formulas, add sophisticated styles, adjust formatting, and more with the custom template.
Before we learn how to create a custom template, we should know that we can save the following settings in an excel template:
- Cell Styles and formats
- The number and types of worksheets
- Page layouts and print areas for each worksheet
- Hidden areas like invisible rows, columns, or worksheets
- Protected areas of the worksheets used to prevent changes in specified cells
- Formulas, charts, hyperlinks, images, and other graphic elements
- Texts entered in all worksheets like columns headers, labels, etc.
- Excel Data validations settings like drop-down menus, validations, etc.
- Calculations and view options like freezing headers
- Macros and ActiveX control used with specific forms
To save an Excel workbook as an Excel template, we must perform the following steps:
First, we need to work on the Excel file as usual. Once we have created the workbook, we need to navigate the File tab and click the 'Save As' option from the list.
We can also use the keyboard key 'F12' to quickly go to the Save As dialogue box.
We must click the drop-down next to 'Save as type' and select the 'Excel Template (*.xltx)' option in the dialogue box. If the workbook contains a macro, we must select the 'Macro-Enabled Template (*.xltm)' option instead of 'Excel Template (*.xltx)'.
As soon as we choose to save a workbook as a template, Excel automatically chooses the destination folder where all the default templates are stored. However, it can be changed as desired.
After selecting the file type, we must enter the template name in the 'File name' box, choose the location to save a template, and click the Save button. A new template will be created immediately at the selected location, and we can create new workbooks based on this template whenever required.
Since we can create or save a custom template to our device storage using the above steps, we can also easily share it over email with other people. The recipient or client will only need to download/ save the template to their device and open it in Excel to start using it accordingly.
How to use custom/ created Excel Templates?
After saving the custom templates into the device storage, we can use them for creating new Excel workbooks. When the templates are saved into the default location of Excel templates (C:\Users\<User Name>\AppData\Roaming\Microsoft\Templates), they are automatically fetched into Excel.
When we want to use the custom templates in Excel, we need to go to the File tab and select the New option from the list. In Excel 2010 or earlier, we must click the 'My Templates' button. After that, we need to select the specific template and click the OK button. It looks like this:
When using Excel 2013 or higher version, we must click the Personal tab to access the saved custom templates from the default location. After that, we need to select the specific template and click the OK button. It looks like this:
Apart from this, if we want to open or use an excel template downloaded from the web or saved in a custom location, we need to perform the following steps:
First, we need to navigate the File tab and click the Open button. This will launch the file explorer dialogue box.
Using the file explorer, we must locate the folder where there is a custom or downloaded Excel template.
If there are many files in the corresponding folder, we can set a filter to display only the Excel template files. For this, we must click the drop-down next to the File name box and select Templates from the list. It will only display the Excel templates and hide all other file formats.
After finding the specific template, we need to double-click on it. Also, we can select the template in the file explorer window and click the Open button.
If the template is opened in a protected view mode, we need to click the 'Enable Editing' button so that we can edit the workbook elements. Once the template is opened in edit mode of Excel, we can make changes accordingly and save it in other desired formats.
How to download Excel Templates?
Excel also supports downloading the templates from online sources. The best and most trusted place to get the Excel templates is the Microsoft Official Website, where users can easily find or search the desired template. It includes various Excel templates categorized under specific groups such as calendar, budget, invoices, timelines, inventory templates, etc. Typically, all these templates are the same accessed from the Excel existing templates section.
Apart from using Excel built-in templates, downloading templates from the web also works better. It gives a better view and allows users to filter templates based on a specific program or category. Like the Excel interface, the web interface also displays all the templates, their descriptions, and download buttons for the corresponding templates. When we use Excel online version, called Excel for web, we can directly open any desired template in Excel online and start working on it.
Following are the steps to download excel templates in the system so that they can be used in the Excel desktop version:
First, we need to open the Microsoft Official Website for Excel templates using the following link:
Next, we need to click on the desired Excel templates. This will open a particular template page containing the description, as shown below:
After opening the template page, we can click on the 'Download' button, choose the location to save a template, and click on the Save button.
Important Points to Remember
- There must be an internet connection when accessing the built-in Excel Office templates. Besides, Microsoft has ended support for Office 2010 and lower versions, meaning that these versions will not get access to existing Excel templates. However, templates can be downloaded from the web.
- It is recommended to use the default location for saving Excel templates so that they remain visible in the Personal template section in Excel. If we save templates in other locations, we have to open them using the file explorer.
- When saving the custom templates, we must ensure to save them as '.xltm' if they contain macros; otherwise, we can use the '.xlt' format
- We must avoid downloading the templates from unofficial or unknown sources as templates may also contain malicious codes or viruses.