Microsoft Office Tutorials and References
In Depth Information
Working with Template Files
Using the workbook template to change workbook defaults
Every new workbook that you create starts out with some default settings. For example, the
workbook has three worksheets, the worksheets have gridlines, text appears in Calibri 11-point
font, columns are 8.43 units wide, and so on. If you’re not happy with any of the default
workbook settings, you can change them.
Making changes to Excel’s default workbook is fairly easy to do, and it can save you lots of time
in the long run. Here’s how you change Excel’s workbook defaults:
Open a new workbook.
Add or delete sheets to give the workbook the number of worksheets that you want.
Make any other changes that you want to make, which can include column widths, named
styles, page setup options, and many of the settings that are available in the two Display
Options sections in the Advanced tab of the Excel Options dialog box.
To change the default formatting for cells, choose Home ➜ Styles ➜ Cell Styles and then
modify the settings for the Normal style. For example, you can change the default font,
size, or number format.
When your workbook is set up to your liking, choose File ➜ Save As.
In the Save As dialog box, select Template (*.xltx) from the box labeled Save As Type.
Enter book.xltx for the filename.
Save the file in your \XLStart folder ( not in your Templates folder).
Close the file.
To determine the location of \XLStart, execute this VBA statement:
After you perform the preceding steps, the new default workbook that appears when Excel is
started is based on the book.xltx workbook template. You can also press Ctrl+N to create a
workbook based on this template. If you ever want to revert back to the standard default
workbook, just delete the book.xltx file.
If you choose File➜New and select Blank Workbook, the workbook will not be based
on the book.xltx template. I don’t know whether that’s a bug or by design. In any
case, it provides a way to override the custom book.xltx template if you need to.
Using the worksheet template to change worksheet defaults
When you insert a new worksheet into a workbook, Excel uses its built-in worksheet defaults for
the worksheet. These defaults include items such as column width, row height, and so on. If you