Customizing Excel
You can choose from dozens of options for adjusting the way Excel looks, acts, and works. Most are accessible in the Options dialog box that appears when you click Tools, Options. The settings on the 13 tabs here are generally self-explanatory, and many of them are variations on common features found in other Office applications. In this section, we highlight only the most useful:
→ For details of Office-wide configuration options, see "Configuring Common Office Features".
Options on the View tab enable you to hide or show interface elements, such as the Formula bar, status bar, gridlines, scrollbars, and the startup task pane. Unlike Word, which allows you to set many such options on a document-by-document basis, the settings you check here apply to every workbook you open.
The Calculation tab enables you to change the default settings Excel uses for calculating formulas. In the days when 286 and 386 computers ruled the Earth, setting manual calculation was a survival tactic, because calculating a large worksheet could literally take hours. In an era when processor speed is measured in gigahertz, this option is necessary only for scientific applications when you need to control the precise order of calculations. In general, the overwhelming majority of users should accept the default options on this tab.
Most of the options on the Edit tab are the same as those found in other Office programs. If you routinely select a range and fill in list values, consider changing the Move Selection After Enter box from its default selection of Down to Right. If you find Smart Tags annoying, clear the Show Paste Options and Show Insert Options boxes.
Tip from 
When entering currency values, such as entries in a check register, people with an accounting background often prefer to let Excel fill in the decimal point. If you choose the Fixed Decimal option on this tab and leave it at the default setting of 2, entering 14398 will result in a value of 143.98. It's extremely unlikely you'll want to set this option permanently. If you use it frequently, however, create this simple toggle macro and assign it to a toolbar button so that you can switch into and out of fixed decimal mode on demand:
Sub ToggleFixedDecimal()
Application.FixedDecimal = Not Application.FixedDecimal
End Sub
The Transition tab is most useful for those who are comfortable with 1-2-3 menus and keystrokes. If your organization prefers that you save files in an alternative format, you can choose that format as your default here as well.
→ For a discussion of the Chart and Color tabs, see "Editing and Formatting Chart Elements".
→ To learn when file compatibility issues might dictate choosing an alternate file format, see "File Compatibility Issues".
Click the General tab to display the options shown in Figure 21.16. The Sheets in New Workbook setting enables you to change the number of blank sheets in each new workbook to any number between 1 and 255. Choose a smaller setting if you rarely use multiple sheets in a workbook or a larger one if you regularly create complex workbooks, such as consolidated budgets. You can also adjust the font that Excel uses for text and numbers in new worksheets from the default of 10-point Arial. Choose a new font from the Standard Font list; specify a new size by using the drop-down list to its right.
Figure 21.16. Use these options to adjust the default font size, number of worksheets per workbook, and other key Excel options.
Tip from 
With Excel 2002, Microsoft made it a bit less likely that you'll fall into a common trap. A box on the General tab used to allow you to specify an Alternate Startup File Location. In this version, the wording is clearer: At Startup, Open All Files In the specified directory. Most users should leave this box blank. Templates and workbooks you want to load automatically should go in the Xlstart folder in your personal profile instead. If you specify an alternative location, Excel loads any workbooks stored in that folder as well as those from Xlstart. This feature is typically used in corporate settings to run macros and install Excel add-ins automatically.
Options on the International tab let you override system settings for date and currency formats, default paper size, and right-to-left orientation.
Set AutoRecover options on the Save tab. Note that this feature, borrowed from Word, is new in this version of Excel.
→ For a discussion of Office AutoRecover features, see "Setting Up Automatic Backup and Recovery Options".
Use the Spelling tab to set spell-checking settings. These options were available on a separate dialog box in earlier versions.
The Error Checking tab includes settings that let you control background checking for common worksheet errors, including those in formulas. These options, new in Excel 2002, include check boxes that let you locate numbers stored as text (which can cause problems with formulas) and text dates containing two-digit years (which can result in Y2K-style date arithmetic errors).
→ For more details on how to check for errors in formulas, see "Troubleshooting Formulas".
Look on the Security tab (see Figure 21.17) for file encryption and file sharing boxes, where you can specify a password that locks a file for modifications or encrypts it so the data can only be seen by authorized users. Click the Macro Security button to access options that affect how Visual Basic macros and scripts work with all workbooks.
Figure 21.17. If you enter a password here, the security option applies only to the current workbook. Click the Macro Security button to set options that apply to all workbooks.
→ Office XP includes a broad array of new security settings; for a complete overview and important details, turn to "Setting Security Options".
Changing Default Formatting for New Workbooks and Worksheets
Every time you start Excel or create a new workbook without using a custom template, Excel uses its default settings. To change settings for the default workbook, create a new template called Book.xlt and save it in the XLStart folder. Follow these steps:
Create or open the workbook whose settings you want to use as Excel's defaults.
To change the style of all cells in the workbook, modify the Normal style. Add other named styles, macros, text, and other content or formatting. If you want to change the number of sheets or add headers and footers, go right ahead.
Choose File, Save As. In the Save As Type box, choose Template.
In the File Name box, enter Book. (Excel adds the .xlt extension automatically.) Do not save the file in the Templates folder; instead, save it in the XLStart folder. The exact location of this folder varies depending on your version of Windows. On a Windows 98 system, with user profiles enabled, navigate to C:WindowsProfilesUsernameApplication DataMicrosoftExcelXLStart, substituting your Windows or network logon name for Username. (Windows NT or Windows 2000 users might need to substitute Winnt for Windows in this location.)
Note
What's the difference between an Excel template and a worksheet? Structurally, the two file types are identical. Like a workbook, a template can include as many sheets as you want, with or without text, charts, and formatting. The key difference is this: When you open a workbook template, from within Excel or from an Explorer window, Excel leaves the original template file undisturbed and creates a new, unnamed document that is an identical copy of the template.
Installing Excel Add-Ins
Excel includes a variety of special-purpose add-ins—compiled macros that add new functions beyond those already available. The Analysis ToolPak adds a broad range of worksheet functions to Excel's list of built-in functions, and the Solver add-in offers a wizard-based alternative to trial-and-error formula solving. Both add-ins are described in more detail in Chapter 23, "Using Formulas and Functions." You need to supply the main Office CD (or point to a network install point) to add any of these add-ins.
By default, most of Excel's default add-ins are configured to be installed on first use. That means you'll have to hunt down the main Office CD each time you use an add-in for the first time—an annoying distraction, especially if you're in the middle of a deadline and the CD isn't close at hand.
If you think you might use any of Excel's add-ins in the future, open Control Panel's Add/Remove Programs option, double-click the Microsoft Office XP entry, and launch the Windows Installer in maintenance mode. Go through the list of add-ins under the Excel group and change their status from Installed on First Use to Run from My Computer.
Excel Startup Switches
When you start Excel, it normally opens a new workbook using the default settings, runs any AutoStart macros in the Personal Macro workbook, and switches to the default location for data files. To change any of these settings, use one of the following startup switches with the Excel.exe command line. You can use any of these switches as part of a shortcut or type them directly at the command line.
Note
To learn more about using command line switches with Excel, see Special Edition Using Microsoft Excel 2002 by Patrick Blattner (published by Que).