Microsoft Excel: How to Customize the Default Excel Workbook

When you create a new Excel workbook, you get a standard default Excel workbook. But what if you don’t like that workbook? Maybe you have a standard header that you always (or almost always) use on pages of your workbook. Or possibly you prefer a different default font style or size, typically use the comma number format, or you often change the layout of column widths when you create a new worksheet. Do you frequently print your worksheets on legal-size paper with a landscape orientation? Does your company require a disclaimer or confidentiality footer on key Excel workbooks? If you frequently change the standard Excel worksheets, customizing the default Excel workbook can be a big time-saver.

As it turns out, Excel gives you quite a bit of control over the look and layout of your worksheets. It’s fairly straightforward to create an entirely customized standard workbook. The trick behind this magic in Microsoft Excel is creating a template file named book.xltx (or book.xltm if your default workbook contains macros), and then save this file to the appropriate location on your hard drive. (This required name assumes English is the interface language). You can’t use book1 or custom or another variation.

Overview: Creating a New Excel Workbook Template

To create a new default Excel workbook template:

1. Open a new blank Excel workbook.
2. Next, customize the blank workbook exactly as you want it to look.
3. Save the workbook with the specific file name in a designated folder. Additional ideas and more detailed steps are provided below.

Some Excel workbook elements you might change:

• Font style and font size: Highlight the portions of the worksheet you want to change and select your preferences for number, alignment, and font formatting from the Font group in the Home tab.
• Print settings: Select one or more worksheets and then choose Page Layout tab > Page Setup group to specify print settings, including the header and footer, margins, and orientation, and indicate other print layout choices.
• Number of sheets: Add or delete worksheets, rename sheet tabs, and even change worksheet tab color.
• Column widths and layout: If you normally prefer different column widths, select the columns or even the entire worksheet and then modify the column width.
• Gridlines: Would you prefer darker gridlines on each of your worksheets? Unlike borders, gridlines only display; they don’t print. To change gridline color, pick File > Options > Advanced. Next, choose Display options for this worksheet and then choose the workbook name from the list. Finally, under Show gridlines, select a different Gridline color.

NOTE: Any new worksheets you insert into your custom default workbook will revert back to the original formatting and layout. You may want to add extra worksheets to the original workbook or reserve an extra or master worksheet you can copy as desired.

Applying Changes to Multiple Cells and/or Worksheets

To add custom formatting changes to every cell, column, or row, first, highlight all cells with Select All (press [Ctrl] + A). When you are done with the cell formatting, press [Ctrl] + [Home] to clear the cell highlights.

To apply changes such as formatting or print settings to multiple worksheets in a workbook, right-click any sheet tab, then left-click on Select All Sheets, which groups the worksheets together for shared actions. When you have finished your changes, left-click again on any sheet tab to clear the worksheet grouping. Find out more about grouping multiple worksheets.

How to Change the Number of Worksheets in a Workbook

You don’t need to create a new default workbook if all you want to do is change the number of worksheets in a new workbook. The default number of worksheets is 1; in Excel 2013 and earlier, the default is 3. To change the default number of worksheets in a new workbook, choose File > Options, pick the General category, and specify the desired number of sheets in the Include this many sheets setting.

Saving Your New Workbook

To save your new default workbook:

1. When the new default workbook is set up to your preferences, choose the File tab and then Save As > Excel Workbook.
2. In the Save As dialog box, choose the Save As Type drop-down list and select Excel Template (*.xltx).
3. Name the file as book.xltx

Excel XLSTART Directory

The file needs to be saved or moved to your XLSTART directory, which is on your local C: drive. The location of this directory varies depending on your version of Windows and Microsoft Office; search your hard drive for the folder. Depending on your version of Excel, examples of the location include:

• C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART\, where <username> is your Windows login name.
• C:\Program Files\Microsoft Office\root\Office16\XLSTART\

How to Locate the XLStart folder

To find out the path of the XLStart folder, check the Trust Center settings:

1. Click File > Options.
2. Click Trust Center, and then under Microsoft Excel Trust Center, click Trust Center Settings.
3. Click Trusted Locations, and then verify the path to the XLStart folder in the list of trusted locations.

Can’t find the XLSTART directory? You may need to create the directory in the above location, or the AppData folder may be hidden.

To show hidden folders in File Explorer:

1. Start File Explorer.
2. On the View tab of the ribbon, click the upper half of the Options button.
3. Activate the View tab of the Folder Options dialog.
4. Under Hidden files and folders, select Show hidden files, folders and drives.
5. Click OK.

You should now be able to see and navigate to the location of the XLSTART folder.

After you save the template file, you can close it.

1. Close Excel.
2. Start Excel to see your new workbook.

Using the New Default Excel Workbook

Now, every time you start Excel, the new blank workbook will be based on the template you created. In addition, when you press [Ctrl] + N, the new workbook will be created from your template.

As always, this or any other workbook can still be individually customized as needed.

Keep in mind that creating and saving a custom default Excel workbook only changes the default workbook on the active computer and does not affect the workbook used by others on your computer network. You can, however, share your default workbook by copying your book.xltx file to the proper location on another computer.

❗IMPORTANT❗ To make sure that Excel displays your updated default workbook, check to see if Excel is set up to show the Start screen when Excel starts.

To check your options for the Start Screen:

1. Pick File > Options and move to the General tab.
2. Under the Start up options section, check the box Show the Start screen when this application starts.
3. Press OK to close.

Options When XLSTART Directory Isn’t Available

On some networks with a lot of security restrictions, you may not have access to the XLSTART directory, or you may not have permission to save files. Instead, create a startup directory on your own system with any name you want and store the book.xltx file in this new alternate startup directory. The directory name you choose doesn’t matter, but you will need to tell Excel where it is.

To save your default workbook in an alternate directory:

1. Create a new folder on your C: drive where you will store your .xltx file.
2. Next, pick File > Options, and then click the Advanced tab.
3. Under the General section, type the full path of the folder that you want to use as the alternate startup folder in the At startup, open all files in: option.
4. If a workbook with the same name is in both the XLSTART folder and the alternate startup folder, the file in the XLSTART folder opens.

Caution: Because Excel will try to open every file in the alternate startup folder, make sure you specify a folder that contains only files that Excel can open and only files you want to see every time you start Excel.

Create your own custom workbook today to save you time and effort in Microsoft Excel.

Was this helpful? Explore other time-saving Microsoft Excel how-to articles.

© Dawn Bjork, MCT, MOSM, CSP®, The Software Pro®
Microsoft Certified Trainer, Productivity Speaker, Certified Speaking Professional