Do you find yourself creating new workbooks in Excel, then making the same changes to every one? Maybe you like to change font size, zoom percent, or the default row height?
If so, you can save yourself time and trouble by setting a default template for Excel to use each time you create a new workbook. As long as you name the template correctly, and put it in the correct location, Excel will use your custom template to create all new workbooks.
Note: biggest challenge with this tip is figuring out the right location for the template file. This can be maddeningly complex, depending on which platform and version of Excel you use. If you get frustrated and can't make things work, you can set your own startup folder manually, as described below.
Settings that can be saved in a template
A template can hold many custom options. Here are a few examples of settings that can be saved in a workbook template:
- Font formatting and styles
- Display options and zoom settings
- Page setup and print options
- Column widths and row heights
- Page formats and print area settings for each sheet
- The number (and type) of sheets in new workbooks
- Placeholder text (titles, column headers, etc.)
- Data validation settings
- Macros, hyperlinks and ActiveX controls
- Workbook calculation options
Remember: these settings only apply to new workbooks created after a custom template file is installed.
The process
- Open a new blank workbook and customize the options as you like
- Save the workbook as an Excel template with the name "book" (Excel will add .xltx) *
- Move the template to the startup folder used by Excel
- Disable Start screen at General > Start up options) **
- Quit and relaunch Excel to be sure settings are fresh
- Test to be sure Excel is using the template when new workbooks are created
* Based on comments, it seems the name of your workbook must be localized for your version of Excel. For example, if you're using the Czech version, you need to use "Sešit" instead of "book".
* * Not strictly required, but the "New blank workbook" option on the Start screen seems to ignore a custom template (?).
Common startup folder locations
Whenever Excel is launched, it establishes what is called a "startup folder", which is named XLSTART. The key is to put your template file into this folder so that Excel will find it. Unfortunately, the exact location of XLSTART varies according to the versions of Excel and Windows you use. Here are some common locations:
C:\Program Files\Microsoft Office\OFFICEx\XLSTART
C:\Users\user\AppData\Microsoft\Excel\XLSTART
C:\Users\user\AppData\Roaming\Microsoft\Excel\XLSTART
Can't find XLSTART?
If you can't find the startup folder for excel (XLSTART), you can use the VBA editor to confirm Excel's start up path:
- Run Excel
- Open the VBA editor (Alt + F11)
- Open the immediate window (Control + G)
- Type: ? application.StartupPath in the window
- Press Enter
The startup path will appear below the command. Once you've confirmed the location of XLSTART, drop in your template file.
Set your own startup directory
If you can't find Excel's startup directory, or if burying your template deep in an application hierarchy just seems wrong, you can tell Excel to look in your own startup folder by setting an option as follows:
- Create a directory called "xlstart" where you like
- Put your custom template in the new directory
- At Options > Advanced > General > Open all files in, enter the path to xlstart
- Test to make sure the template is working
Telling Excel about your own startup folder...make sure you use the correct path on your computer!
Test to make sure your template is being used
After you go through the steps to set up a default template, make sure you test to confirm your template is being used. One easy way to do this is to (temporarily) give cell A1 in your template a bright yellow or orange fill. That way, you can immediately see if your custom template is being used. Once you're sure things are working, remove the marker.
Setting a default Excel template on the Mac
The process for setting a default Excel template on a Mac is similar to the steps above for Windows. Again, confirming the startup folder can be tricky, depending on whether you have Excel 2011 or 2016 installed (2008 not tested). In Excel 2016, according to Microsoft, there is currently no startup folder. Also, as of mid-2016, the name of the template should be "workbook" (manually remove the .xltx extension) not "book".
Because of confusion around the startup folder, here's what I recommend on a Mac:
- Create a new directory in your home documents folder called "xlstart"
- Go to Preferences > General > At startup, open all files in, and set xlstart as path
- Open a new workbook and customize the options as you like
- Save the workbook as an Excel template with the name "workbook.xltx" inside xlstart
- Manually remove the extension ".xltx" so that the file is named only "workbook"
- Quit and relaunch Excel to be sure settings are updated
- Test to be sure Excel is using the template when new workbooks are created
I tested this with Excel 2011 and Excel 2016 installed on the same Mac in May 2016, and both used the same template as expected.
Note: Tested again in January 2020. Step #5 above (removing the extension) was not needed. Also, I was able to use 'book.xltx' for the filename, like the Windows version.
Template for new sheets
A workbook template controls the look and layout of sheets already in the workbook, but not new sheets. When you insert a new sheet,it will inherit Excel's sheet defaults. If you want to control new sheets with your own template, follow the process below.
- Open a new blank workbook and delete all sheets except one
- Make desired customizations to the sheet
- Save as an Excel template named "sheet.xltx" to the location determined above **
- Close the file
** If using a non-English version of Excel, you may need to localize this name.
To test that the sheet template is working, open a workbook and add a new sheet. You should see your customizations all newly inserted sheets.
