Customize Default Excel Workbook

Mynda Treacy

June 9, 2015

Did you know that when Excel opens a new workbook it uses a template called ‘Book’ to create the new file?

This means we can create a custom default Excel workbook by replacing the template file called ‘Book’ with one containing our own formatting.

How to Customize the Default Excel Workbook

It’s easy to do.

  1. Open Excel with a blank workbook.
  2. Apply the formatting to the blank workbook – colours, fonts, number formats, themes, print set up, page headers/footers etc.
  3. Save the file: File menu > Save As, or shortcut key F12.
  4. Set the “Save in” location to the XLSTART folder*. See below for more instructions on finding your XLSTART folder.
  5. Change the file name to “Book”
  6. From the “Save as type” drop-down list, select “Excel Template (*.xltx)
  7. Click Save.
  8.  

    custom default workbook

  9. Close and re-open Excel. The blank workbook should contain the formatting you previously set.

*To find your XLSTART folder :

Windows XP: C:\Documents and Settings\User_name\Application Data\Microsoft\Excel\XLSTART

Windows Vista: C:\Users\User_Name\AppData\Local\Microsoft\Excel\XLSTART

Windows 7, 8 or 10: C:\Users\User_Name\AppData\Roaming\Microsoft\Excel\XLSTART

Replace User_Name with your username on your computer.

Customizing the Default WorkSheet

And if you want any new sheets you insert to have the same formatting, you should also save the file as “Sheet.xltx” in the XLSTART folder.

Tip: make sure your Sheet.xltx file only contains one sheet.

Customizing Excel Options

You can also customize how many sheets are in any new files, the font size and view via the Excel Options menu:

Excel 2007 Windows Button > Excel Options > Popular.

Excel 2010 onward File Tab > Options > General:

custom Excel Options

Customize Default Save Location

If you find yourself regularly saving your Excel files to the same folder path you can customize what folder the File Save As dialog box opens at, which is sure to save you a few clicks:

Excel 2007 Windows Button > Excel Options > Save.

Excel 2010 onward File Tab > Options > Save:

custom Excel Options

Tip: have a poke around in the Options menus as there are loads of things you can customize.

More Customizing

Thanks

Special thanks for Khushnood for reminding me about the Sheet.xltx tip.

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

2 thoughts on “Customize Default Excel Workbook”

Leave a Comment

Current ye@r *