Learn how to create spreadsheet templates in Excel

Content
  1. Stop repeating your work and create a label
  2. Content that can be saved in a template
  3. Formatting options that can be saved in a template
  4. Advanced features that can be saved in a template
  5. Create Spreadsheet Templates in Excel
  6. Excel 2010 and Excel 2007
  7. Change the default Excel templates for all new files

Stop repeating your work and create a label

Templates are used in programs such as Excel and Google Sheets to save time creating the same type of file over and over, such as a weekly journal or expense report. In Excel or Google Sheets, a template is a file that is saved with a different extension and serves as the basis for new files. A template file contains various materials and settings that are applied to new files created from a template.

The instructions in this article apply to Excel 2019, 2016, 2013; Excel for Office 365 and Excel for Mac.

Content that can be saved in a template

  • Text : page titles, row and column titles, section titles, and other text.
  • Facts † Any cell contents, including text, numbers, and other data.
  • Graphics † Shapes, company logos and background images.
  • formulas † Calculations for use in new workbooks.

Formatting options that can be saved in a template

  • Text formatting † Font choice, size and color.
  • Cell and sheet sizes † Background fill color, column widths, number and date formats, and alignment.
  • 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 cannot be changed, hidden rows or columns, or tables that contain information not intended to be shared.
  • Macros † Automated procedures typically used in a workbook or worksheet.
  • Custom Toolbars † Includes commonly used options, macros, and the Quick Access Toolbar.

Create Spreadsheet Templates in Excel

For modern versions of Excel, you need to do some prep work before you save the templates.

Excel Online

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

Excel 2013 and later

You must set up a custom template folder before saving it as a template.

  1. Select File parameters:

  2. Select Save in the menu list.

  3. Find Default personal template location about halfway down the page.

  4. Type in the folder where you want to save your custom templates. Documents Custom Office Templates – a good choice.

Modify the new workbook until you’ve made all the necessary changes to the template, and then save the file with the template extension.

  1. Select File Save as

  2. Name your file as the template name should appear.

  3. drop down menu File type select one of the Excel template file types.

    Use Macro-enabled Excel template (.xltm) for books with macros, Excel 97-2003 Template (.xls) for templates that need to be compatible with very old versions of Excel and Excel Template (.xltx) for all other templates.

  4. Click Okay to save the template.

Excel automatically places the template in the correct directory. New documents can be created using a template by selecting in person in the file window New (Near Featured ) and select the correct template.

Excel 2010 and Excel 2007

Edit the new workbook until you’ve made all the changes you want to keep in the template.

  1. Select File Save as

  2. Name your file as you want the template name to appear.

  3. drop down menu File type select the Excel template file type. The folder will automatically change to the correct template folder.

  4. Click Okay to save the template.

Excel for Mac

Edit the book until you’ve made all the changes you want to see in the template.

Select File Save as template † Give the template a name and save it. The template is now available for all new documents.

Change the default Excel templates for all new files

In Excel 2013 and later, change the default Excel workbook template by saving the template in the correct location.

Default templates must have a name book.xltx or book.xltm and saved in Excel’s startup folder:

C:Users%username%AppDataRoamingMicrosoftExcelXLSTART

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

Leave a Reply

Your email address will not be published.