How To Set A Default Template In Excel

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.

The 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 up 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

These settings only apply to new workbooks created after a custom template file is installed.

The process

1. Open a new blank workbook and customize the options as you like
2. Save the workbook as an Excel template with the name " book " (Excel will add .xltx ) *
3. Move the template to the startup folder used by Excel
4. Disable Start screen at General > Start up options) **
5. Quit and relaunch Excel to be sure settings are fresh
6. Test to be sure Excel is using the template when new workbooks are created

• • 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 to confirm Excel’s start-up path like this:

1. Run Excel
2. Open the VBA editor (Alt + F11)
3. Open the Immediate Window (Control + G)
4. Type: ? application.StartupPath in the window
5. 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:

1. Create a directory called " xlstart " where you like.
2. Put your custom template in the new directory.
3. At Options > Advanced > General > Open all files in , enter the path to xlstart .
4. 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 when you create a new document. Once you’re sure things are working, remove the marker.

Note 25-Jun-2025 - I had trouble getting Excel to use my custom template on a new Windows 11 machine, even though book.sltx was in the correct location. The problem was Excel using a start screen. If your template is still not loading, check to see if the “Start screen” is enabled at File > Options > General > Startup options. If so, uncheck the option “Show the Start screen when this application starts”. This setting can prevent the template from loading.

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:

1. Create a new directory in your home documents folder called " xlstart “.
2. Go to Preferences > General > At startup, open all files in , and set xlstart as path.
3. Open a new workbook and customize the options as you like
4. Save the workbook as an Excel template with the name " workbook.xltx " inside xlstart .
5. Manually remove the extension " .xltx " so that the file is named only " workbook “.
6. Quit and relaunch Excel to be sure the settings are updated.
7. Test to confirm Excel uses 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.

1. Open a new blank workbook and delete all sheets except one.
2. Make desired customizations to the sheet.
3. Save as an Excel template named " sheet.xltx " to the location determined above. **
4. 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 in all newly inserted sheets.

Question: What formula tells you if A1 contains the text “apple”?

This is a surprisingly tricky problem in Excel. The “obvious” answer is to use the FIND function to “look” for the text, like this:

=FIND("apple",A1)

Then, if you want a TRUE/FALSE result, add the IF function :

=IF(FIND("apple",A1),TRUE)

This works great if “apple” is found – FIND returns a number to indicate the position, and IF calls it good and returns TRUE.

But FIND has an annoying quirk – if it doesn’t find “apple”, it returns the #VALUE error. This means that the formula above doesn’t return FALSE when text isn’t found, it returns #VALUE:

FIND returns the position of the text (if found), but #VALUE if not found.

Unfortunately, this error appears even if we wrap the FIND function in the IF function.

Grrrr. Nobody likes to see errors in their spreadsheets.

(There may be some good reason for this, but returning zero would be much nicer.)

What about the SEARCH function , which also locates the position of text? Unlike FIND, SEARCH supports wildcards, and is not case-sensitive. Maybe SEARCH returns FALSE or zero if the text isn’t found? Nope. SEARCH also returns #VALUE when the text isn’t found.

So, what to do? Well, in a classic, counter-intuitive Excel move, you can trap the #VALUE error with the ISNUMBER function , like this:

=ISNUMBER(FIND("apple",A1))

Now ISNUMBER returns TRUE when FIND yields a number, and FALSE when FIND throws the error.

Another way with COUNTIF

If all that seems a little crazy, you can also the COUNTIF function to find text:

=COUNTIF(A1,"*apple*")

It might seem strange to use COUNTIF like this, since we’re just counting one cell. But COUNTIF does the job well – if “apple” is found, it returns 1, if not, it returns zero.

For many situations (e.g. conditional formatting) a 1 or 0 result will be just fine. But if you want to force a TRUE/FALSE result, just wrap with IF:

=IF(COUNTIF(A1,"*apple*"),TRUE)

Now we get TRUE if “apple” is found, FALSE if not:

Note that COUNTIF supports wildcards – in fact, you must use wildcards to get the “contains” behavior, by adding an asterisk to either side of the text you’re looking for. On the downside, COUNTIF isn’t case-sensitive, so you’ll need to use FIND if case is important.

Other examples

So what can you do with these kind of formulas? A lot!

Here are a few examples (with full explanations) to inspire you:

• Count cells that contain specific text
• Sum cells that contain specific text
• Test a cell to see if contains one of many things
• Highlight cells that contain specific text
• Build a search box to highlight data (video)

Logical confusion?

If you need to brush up on how logical formulas work, see this video . It’s kind of boring, but it runs through a lot of examples.

Other formulas

If you like formulas (who doesn’t?!), we maintain a big list of examples .