Learn How to Create Spreadsheet Templates in Excel

Stop repeating your work and take a shortcut

Photograph depicting a stone mason using a template to trace architectural flourishes.

 Getty Images

Templates are used in programs like Excel and Google Sheets to save time when creating the same type of file repeatedly, like a weekly log or expense report. In Excel or Google Sheets, a template is a file that is saved with a different file extension and serves as a basis for new files. The template file contains a variety of content and settings that are applied to the new files created from the template. Templates are created by saving a normal document with a special extension, then storing it in the correct directory.

Content That Can Be Saved in a Template

  • Text: Page titles, row and column labels, section headings, and other text.
  • Data: Any cell contents including text, numbers, and other data.
  • Graphics: Shapes, company logos, and background images.
  • Formulas: Calculations that will be reused in new workbooks.

Formatting Options That Can Be Saved in a Template

  • Text formatting: Font selection, size, and color.
  • Cell and sheet formats: Background fill color, column widths, and number and date formats, alignment, etc.
  • Number of sheets:┬áThe default number of sheets included in all workbooks.

Advanced Features That Can Be Saved in a Template

  • Protected and hidden areas: Locked cells that can't be changed and hidden rows or columns, or worksheets containing information that is not for general access.
  • Macros: Automated procedures that are routinely used in a workbook or worksheet.
  • Custom toolbars: Containing frequently used options and macros and the quick access toolbar.

Creating Spreadsheet Templates in Excel

For modern versions of Excel, there is a little preparatory work we need to do before saving templates.

Excel Online

Files cannot be saved as templates from Excel Online. The files can be started in Excel Online, but you must open the file in a desktop version of Excel to save as a template. Select Open in Excel from the menu bar to send the file to the version of Excel installed on your computer.

Excel 2013 and Later

You will need to set the custom template directory before saving as a template.

  1. Select File > Options.
  2. Select Save in the menu list.
  3. Locate Default personal template location about halfway down the page.
  4. Type in the directory where you will save your custom templates. Documents\Custom Office Templates is a good choice.
Screenshot showing Excel 365 save options for setting a default custom template location.
Created by Alexander Fox

Modify a new workbook until you have made all the edits you want to use in the template, then save the file with the template extension.

  1. Select File > Save As.
  2. Name your file as the template's name should appear.
  3. In the file type drop-down menu, select one of the Excel Template file types.
  4. Select OK to save the template.

Excel will automatically place the template in the correct directory. New documents can be created with the template by selecting Personal in the in the new file window (next to Featured) and selecting the appropriate template.

What's the Best Template Format?

Use Excel Macro-Enabled Template (.xltm) for workbooks that contain macros, Excel 97-2003 Template (.xls) for templates that must be compatible with very old versions of Excel, and Excel Template (.xltx) for all other templates.

Excel 2010 and Excel 2007

Edit a new workbook until you have made all the changes you want to save in the template.

  1. Select File > Save As.
  2. Name your file as you want the template's name to appear.
  3. In the file type drop-down menu, select an Excel Template file type. The directory will automatically change to the correct template directory.
  4. Select OK to save the template.

Excel for Mac

Edit the workbook until you have made all the changes you want to see in the template.

Select File > Save as Template. Name the template and save it. The template will now be available for all new documents.

Changing Excel's Default Templates for All New Files

In Excel 2013 and later, you can change Excel's default workbook template by saving your template in the correct location.

Default templates must be named Book.xltx or Book.xltm and saved in Excel's startup folder:

C:\Users\%username%\AppData\Roaming\Microsoft\Excel\XLSTART

To change the template used to insert new sheets into existing workbooks, your template must be named Sheet.xltx or Sheet.xltm and saved in the same directory.