Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 6 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 templatei.e., it will contain more than one
worksheetagain 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 hack 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
templatesworkbooks, worksheets, or boththat 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.