Creating templates

Excel supports three types of templates:

■ The default workbook template: Used as the basis for new workbooks. This file is named book.xltx.

■ The default worksheet template: Used as the basis for new worksheets that are inserted into a workbook. This file is named sheet.xltx.

■ Custom workbook templates: Usually, these are ready-to-run workbooks that include formulas, but they can be as simple or as complex as you like. Typically, these templates are set up so that a user can simply plug in values and get immediate results.

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:

1. Open a new workbook.

2. Add or delete sheets to give the workbook the number of worksheets that you want.

3. 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 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.

4. When your workbook is set up to your liking, choose Office

Save As.

5. In the Save As dialog box, select Template (*.xltx) from the box labeled Save As Type.

6. Enter book.xltx for the filename.

7. Save the file in your \XLStart folder (not in your Templates folder).

8. Close the file.

Tip The \XLStart folder may be located in either of these directories:

C:\Documents and Settings\<usemame>\Application Data\Microsoft\Excel\XLStart

C:\Program Files\Microsoft Office\Office12\XLStart

To determine the location of \XLStart, execute this VBA statement:

MsgBox Application.StartupPath

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.

Note If you choose File New, and select Blank Workbook from the New Workbook dialog box, the workbook will not be based on the book.xltx template. I don't know if this is a bug, or if it's by design.

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. This includes items such as column width, row height, and so on. If you don't like the default settings for a new worksheet, you can change them by following these steps:

1. Start with a new workbook and delete all the sheets except one.

2. Make any 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 Excel Options dialog box.

3. When your workbook is set up to your liking, select Office

Save As.

4. In the Save As dialog box, select Template (*.xltx) from the Save As Type box.

5. Enter sheet.xltx for the filename.

6. Save the file in your \XLStart folder (not in your Templates folder).

7. Close the file.

8. Close and restart Excel.

After performing this procedure, all new sheets that you insert by clicking the Insert Worksheet button (which is next to the last sheet tab) will be formatted like your sheet.xltx template. You can also press Shift+F11 to insert a new worksheet.

0 0

Post a comment