Home and Learn: Microsoft Excel Course
Create an Excel Worksheet Template
Creating a worksheet template can really save you a lot of time. Templates are especially useful if you find yourself having to create the same spreadsheet over and over. For example, if a spreadsheet has sheets for each month of year it becomes a bit if a chore if you have to type out the heading and formula for each sheet. Instead, you only need to do it once. Then you can save it as a template. If you need a new month, you can then Insert your template. Here's how.
Open up a new workbook. Make sure you only have one worksheet.
Create the spreadsheet you want to use as a template. In the image below, we've just set up a simple spreadsheet with Week headings at the top and some labels down the left:
Notice how none of the data is filled in for the weeks. The reason there are zeros for the Weekly Totals and the Item Totals is because we have the formulas in place but no data for the weeks. Once it is saved as a template and inserted into a new workbook, then the data can be added. We won't have to add the formulas because they are already in place.
Once you have your spreadsheet looking the way you want it, click the File menu at the top of Excel. Then click File >Save As on the left hand side. On the Save As screen, click the Browse option.
When the Save As dialogue box appears, change the Save as type area at the bottom to Excel Template. Type a name for your template:
Before clicking Save, notice the file name now ends in xltx, and that it is being saved to a Templates folder inside Excel (Custom Office Templates in later versions).
Click the Save button to save your template.
To use your template, close the current worksheet. Create a new blank workbook. Right click a sheet name at the bottom and select Insert from the menu that appears:
The Insert dialogue box should appear. Your template should be on the list, in the General section:
Select your template, and click OK. Your template spreadsheet should then be inserted into your new workbook. You can delete any sheets your don't need, and rename the template. If you need a new sheet based on your template, right-click a sheet and select Insert from the menu again.
Excel Templates Not Showing Up
If you can't see your template, then you may need to reset your template location. To do this, you need to know your computer's user name.
In Windows 10, type File Explorer into the search box. Open up the File Explorer and have at look at the left hand side. Click on Local Disk C. On the right hand side, double-click on the Users folder and you should see your user name as one of the folder names.
Once you have your user name, type it instead of YOUR_USER_NAME in the file location below:
C:\Users\YOUR_USER_NAME\AppData\Roaming\Microsoft\Templates\
Now head over to Excel. Click File > Save. Then click the Options item from the menu on the left:
In the dialogue box that appears, select Save:
In the Default personnal templates location text area, paste the link above, not forgetting to paste your own user name in:
In Windows 11, the default location is here:
C:\Users\YOUR_USER_NAME\AppData\Roaming\Microsoft\Templates
Click OK on the dialogue boxes. You should now be OK to save and open up your new template file.
In the next part, you'll see how to add a data form to an Excel spreadsheet.
<--Back to the Excel Contents Page