Customize the Templates Dialog and Default Workbook

If you tend to perform the same tasks or use the same spreadsheet layouts again and again, you can build your own Template tab into Excel's standard Insert Template dialog to provide a quick starting point.

Imagine you have a spreadsheet containing days of the year and formulas summarizing various data for the days. You have formatted this spreadsheet beautifully with your company colors, logo, and required formulas, and you need to use it on a daily basis. Instead of reinventing the wheel (or copying and deleting what you don't need) each day, you can save yourself a lot of time and trouble by creating a template.

Excel's worksheet and workbook templates provide you with a running start on your next project, enabling you to skip the initial setup, formatting, formula-building, and so on. Saving a template worksheet simply means opening a new workbook, deleting all but one worksheet, and then creating the basic template you will be using. Once you're finished, select File Save As... and choose Template from the dialog's Save As Type drop-down list. If your template is to be a workbook template-i.e., it will contain more than one worksheet-again add a new workbook, make all the necessary changes, select File Save As..., and save as a template.

Template in hand, you can create a clone at any time by either selecting File New... and selecting a workbook template, or by right-clicking the Worksheet tab and selecting Insert... from the context sensitive menu to insert a new worksheet from a template. Wouldn't it be nice, though, to have those templates available to you right from Excel's standard Insert Template dialog, or to set your preferred workbook as the default? You can, by creating your own Template tab.

This tutorial assumes you have a single installation of Excel running on your computer. If you have multiple copies or versions of Excel installed, this may not work.

Creating Your Own Template Tab

If you have a slew of templates-workbooks, worksheets, or both-that you use on a regular basis, you can group them together right there on the Insert dialog.

From within any workbook, select File Save As... and, from the Files of Type pop-up menu, select Template (*.xlt). Excel will, by default, select the standard Templates or My Templates folder in which all your homegrown templates are kept. If you don't already have a My Templates folder, create one as a subfolder of the Templates folder using the New Folder button.

Select File New... on the worksheet menu bar (for Excel 2000 and above, choose General Templates from the New Workbook dialog that will appear). You should now see the tab you created (My Templates as depicted in the screen shot in Figure 1-10) on the dialog floating over your screen. You also should now see your Template workbooks and worksheets, as long as you saved them to this folder.

Figure 1-10. The Templates dialog

Using a Custom Default Workbook

Starting Excel opens a blank default workbook called Book1 containing three blank worksheets. This is fine and dandy if you want a clean slate each time you start Excel. If you're like us, however, you tend to favor one workbook over the others. So, for us, opening Excel involves dismissing the default workbook and searching for our regular workbook. It sure would be handy to have that favored workbook open at the outset, ready for action.

To do so, save your default workbook (template) in the XLSTART folder (generally found in C:\Documents and Settings\Owner\Application Data\Microsoft\Excel\XLSTART on Windows, and in Applications/Microsoft Office X/Office/Startup/Excel under Mac OS X). Once you have done this, Excel will automatically use whichever workbook(s) you have in here as the default.

The XLSTART folder is where your personal macro workbook is created and saved automatically when you record a macro. The personal macro workbook is a hidden workbook. You also can have your own hidden workbooks open in the background if you want by opening the required workbook, selecting Window Hide, closing Excel, and clicking Yes to save changes to the workbook you just hid. Now place this workbook in your XLSTART folder. All the workbooks you hide and place within the XLSTART folder will open as hidden workbooks each time you start Excel.

Don't be tempted to place too many workbooks into this folder, especially large ones, as all of them will open when you start Excel. Too many open workbooks can greatly slow down Excel's performance.

Naturally, if you change your mind and decide to go back to a blank default workbook, simply remove the appropriate workbook or workbook template from the Startup folder.