Learn How to Create Spreadsheet Templates in Excel

In general terms, a template is something that serves as a pattern for processes that duplicate the template's characteristics. In a spreadsheet program such as Excel or Google Spreadsheets, a template is a file that is saved, usually 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 available to all new files created from the template.

Content That Can Be Saved in a Template Includes

  • Text - such as page titles, and row and column labels that are repeated in all new workbooks
  • Data - constant values such as tax rates, profit margins, and contact information
  • Graphics - company logos, background images
  • Formulas - calculations that are reused on all new sheets such as summing columns of numbers, and calculating average values

Formatting Options That Can Be Saved in a Template Include

  • Font settings - font type, size, and color
  • Cell and sheet formats - background fill color, column widths, and number and date formats
  • Number of sheets - the default number of sheets included in all workbooks

Setting Options That Can Be Saved in a Template Include

  • 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 - also includes the quick access toolbar

In Excel, you can create your own default templates that are used to create all new workbooks and worksheets. The default workbook template must be named Book.xlt and the default worksheet template named Sheet.xlt.

These templates need to be placed in the XLStart folder. For PCs, if Excel is installed on the local hard drive, the XLStart folder is usually located at:
C:\Program Files\Microsoft Office\Office #\XLStart

Note: the Office # folder shows the number of the version of Excel being used.

  • Excel 2003 - version 11
  • Excel 2007 - version 12
  • Excel 2010 - version 14
  • Excel 2013 - version 15

So the path to the XLStart folder in Excel 2010 would be:
C:\Program Files\Microsoft Office\Office14\XLStart