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.
- Open Excel with a blank workbook.
- Apply the formatting to the blank workbook – colours, fonts, number formats, themes, print set up, page headers/footers etc.
- Save the file: File menu > Save As, or shortcut key F12.
- Set the “Save in” location to the XLSTART folder*. See below for more instructions on finding your XLSTART folder.
- Change the file name to “Book”
- From the “Save as type” drop-down list, select “Excel Template (*.xltx)
- Click Save.
- 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:
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:
Tip: have a poke around in the Options menus as there are loads of things you can customize.
More Customizing
- Customize the Ribbon
- Customize the QAT
- Create your own default Excel Workspace
- Custom Chart Templates
- Default PivotTable Layout
Thanks
Special thanks for Khushnood for reminding me about the Sheet.xltx tip.
My pleasure Mynda 🙂
🙂