When you create a new Excel workbook, you get a standard default workbook. What if you don’t like that workbook? For example, you may prefer a workbook with only one worksheet. Or, maybe you don’t like the default font or font size. Perhaps you prefer to have the gridlines hidden in your worksheets. Or, maybe you have a standard header that you always use on printed pages.
As it turns out, Excel gives you quite a bit of control in this area. You can make some changes in the Excel Options dialog box. Other changes require a template.
Changing defaults in the Excel Options dialog box
The General tab of the Excel Options dialog box has four setting that control new workbooks.
You can specify the font, font size, view, and number of worksheets. When you change any of these settings, all new workbooks will use those settings. Changing these settings will not affect workbooks that you’ve already created.
Creating a book.xltx template
For a new workbook setting that can’t be specified in the Excel Options dialog box, you need to create a custom template file named book.xltx and then save that file to the proper location on your hard drive.
To create a new default workbook template, all you need to do is customize a blank workbook exactly as you like it. Here’s a list of some of the items you can change:
- Number of sheets: Add or delete sheets as you like. You can also change their names.
- Styles: Use the Style Gallery (Home➜Styles) to customize styles. By default, all cells use the Normal style, so if you want to change the default font in any way (including fill color, number format, font, and borders), modify the Normal style. To change the Normal style, right-click its name in the Style Gallery and choose Modify. Then make the changes in the Style dialog box.
- Print settings: Use the commands on the Page Layout tab to specify print-related settings. For example, you can include header or footer information or adjust the margins.
- Column widths: If you don’t like the default column widths, change them.
- Graphics: You can even insert a graphical object or two — for example, your company logo or a picture of your cat.
When the new default workbook is set up to your specifications, choose File➜Save As. In the Save As dialog box, follow these steps:
1. Select Template (*.xltx) in the Save As Type drop-down list. If your template contains any VBA macros, select Excel Macro-Enabled Template (*.xltm).
2. Name the file book.xltx (or book.xltm if it has macros).
3. Make sure that the file is saved to your XLStart folder. Excel proposes that you save the file in your Templates folder, but it must be saved in your XLStart folder.
Note: The location of the XLStart folder varies. To find the location of your XLStart folder, press Alt+F11 to activate the VB Editor. Press Ctrl+G to display the Immediate window, and then type this statement and press Enter:
? Application.StartupPath
After you save the file, you can close it. Now, every time you start Excel, the blank workbook that’s displayed will be based on the template you created. In addition, when you press Ctrl+N, the new workbook will be created from your template.
If you ever need to bypass your new default workbook and start with one of the normal Excel default workbooks, choose File➜New and choose the Blank Workbook item.
