When you create a new Excel workbook, you get a standard default 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?
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 default 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 saving this file to the appropriate location on your hard drive.
To create a new default Excel workbook template:
Some Excel workbook elements you might change:
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.
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, 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 on 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.
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 is 3). In Excel 2013 and Excel 2010, choose File > Options, pick the General category, and specify the desired number of sheets in the Include this many sheets setting.
To save your new default workbook:
The file needs to be saved in 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.
After you save the template file, you can close it.
Now, every time you start Excel, the new blank workbook will be based on the template you created. In addition, when you click the New toolbar button (or 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.
If the XLSTART directory is on a network, you may not have permission to save files. Instead, you can 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:
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.
Discover more Excel shortcuts, tips and tricks here.
By Dawn Bjork, MCT, The Software Pro®
Microsoft Certified Trainer, Technology Speaker, Software Consultant