Excel 2002

CALCULATING A PERSON'S AGE

A person's age indicates the number of full years that the person has been alive. The formula for calculating the number of years between two dates won't calculate this value correctly. You can use two other formulas, however, to calculate a person's age.

The following formula returns the age of the person whose date of birth you enter into cell A1. This formula uses the YEARFRAC function, which is available only when you install the Analysis ToolPak add-in.

=INT(YEARFRAC(TODAY( ), A1))

The following formula, which doesn't rely on a Analysis ToolPak function, uses the DATEDIF function to calculate an age:

=DATEDIF(A1,TODAY( ),"Y")

If you're a stickler for detail, use the following formula to calculate the exact age in years, months, and days:

=DATEDIF(A1,NOW( ),"y") & "years," & DATEDIF(A1,NOW( ),"ym") & "months,"& DATEDIF(A1,NOW( ),"md") & "days"

This formula will return a text string such as 49 years, 3 months, 29 days.

TOP & BOTTOM ALIGNMENT

Left, right, and center alignment all refer to the placement of a text entry in relation to the left and right cell borders (that is, horizontally). You can also align entries in relation to the top and bottom borders of their cells (that is, vertically). Normally, all entries are vertically aligned with the bottom of the cells (as though they were resting on the very bottom of the cell). You can also vertically center an entry in its cell or align it with the top of its cell.

To change the vertical alignment of a cell selection, open the Format Cells dialog box (Ctrl+1) and then choose the Alignment tab and select Top, Center, Bottom, or Justify in the Vertical drop-down list box.

TURNKEY APPLICATIONS

A turnkey application refers to a spreadsheet solution that is programmed to work as a standalone application. Such an application always requires macros, and may involve creating custom menus and custom toolbars.

These applications are large-scale projects that are designed to be used by many people or for a long time. They often interact with other systems (such as a corporate database) and must be very stable.

AUTOCOMPLETE READS YOUR MIND

AutoComplete enables you to type the first few letters of a text entry into a cell, and Excel automatically completes the entry based on other entries that you've already made in the column.

Begin entering text or a value.
If Excel recognizes your entry, it automatically completes it.
If Excel guesses correctly, press Enter to accept it. If you want to enter something else, just continue typing and ignore Excel's guess.

GETTING TO KNOW AUTOCOMPLETE

AutoComplete is kinda like a moronic mind reader who anticipates what you might want to enter next based on what you just entered. This feature comes into play only when you're entering a column of text entries. (It does not come into play when entering values or formulas or when entering a row of text entries.) When entering a column of text entries, AutoComplete looks at the kinds of entries that you make in that column and automatically duplicates them in subsequent rows whenever you start a new entry that begins with the same letter as an existing entry.

If you override a duplicate supplied by AutoComplete in a column by typing one of your own, you effectively shut down its ability to supply any more duplicates for that particular letter. In other words, you're on your own if you don't continue to accept AutoComplete's typing suggestions.

If you find that the AutoComplete feature is really making it hard for you to enter a series of cell entries that all start with the same letter but are otherwise not alike, you can turn off the AutoComplete feature. Choose Tools, Options and select the Edit tab. Then, select the Enable AutoComplete for Cell Values check box to remove its check mark before clicking OK.

AUTOCORRECT

The AutoCorrect feature is a godsend for those of us who tend to make the same stupid typos over and over again. Excel already knows some corrections to make automatically when it sees them when you first install the program such as, lowercasing the second letter of an entry that has two initial capital letters. You can also add to the list of text replacements at any time when using Excel. To add to the replacements:

Choose [T]ools, [A]utoCorrect on the menu bar to open the AutoCorrect dialog box.
Enter the typo or abbreviation in the [R]eplace text box.
Enter the correction or full form in the [W]ith text box.
Click the [A]dd button or press Enter to add the new typo or abbreviation to the AutoCorrect list.
Click the OK button to close the AutoCorrect dialog box.

AUTOCORRECT YOUR TYPOS

The AutoCorrect feature is a lifesaver for those of us who tend to make the same stupid typos over and over again. With AutoCorrect, you can alert Excel to your own particular typing gaffe and tell the program how it should automatically fix them for you.

When you first install Excel, the AutoCorrect feature already knows to automatically correct two initial capital letters in an entry (by lowercasing the second capital letter), capitalize the name of the days of the week, and to replace a set number of text entries and typos with particular substitute text.

You can add to the list of text replacements at any time when using Excel. These text replacements can be of two types: typos that you routinely make along with the correct spellings, and abbreviations or acronyms that you type all the time along with their full forms.

To add to the replacements, follow these steps:

Choose Tools, AutoCorrect Options on the menu bar to open the AutoCorrect dialog box.
On the AutoCorrect tab in this dialog box, enter the new typo or abbreviation in the Replace text box.
Enter the correction or full form in the With text box.
Click the Add button or press Enter to add the new typo or abbreviation to the AutoCorrect list.
Click the OK button to close the AutoCorrect dialog box.

AUTOFILL INSTEAD OF FORMULAS

Besides being a shortcut way to copy cells, AutoFill can quickly create a series of incremental values. For example, if you need a list of values from 1 to 100 to appear in A1:A100, you could do it with formulas. You would enter 1 in cell A1, the formula =A1+1 into cell A2, and then copy the formula to the 98 cells below.

You could also use AutoFill to create the series for you without using a formula. To do so, enter 1 into cell A1 and 2 into cell A2. Select A1:A2 and drag the fill handle down to cell A100.

When you use AutoFill in this manner, Excel checks out the selected cells to figure out how to complete the rest of the series. If A1 contained 1 and cell A2 contained 3, Excel would recognize this pattern and fill in 5, 7, 9, and so on. This technique also works with decreasing series and dates. Excel even recognizes common series names, such as months and days of the week.

AUTOFILTER

Excel's AutoFilter feature makes it a breeze to hide everything in a database except the records you want to see. All you have to do to filter a database with this incredibly nifty feature is position the cell pointer somewhere in the database before you choose [D]ata, [F]ilter, Auto[F]ilter on the menu bar.

To filter the database to just those records that contain a particular value, you then click the appropriate field's drop-down list button to open a list box containing all the entries made in that field, and select the one you want to use as a filter. Excel then displays only those records that contain the value you selected in that field (all other records are temporarily hidden).

WHEN AUTOFIT JUST WON'T DO

When AutoFit's best-fit won't do, drag the right border of the column (on the frame) until it's the size you need instead of double-clicking it. This manual technique for calibrating the column width also works when more than one column is selected. Just be aware that all selected columns assume whatever size you make the one that you're actually dragging.

AUTOFORMAT TIP

If you format a table with a title that has been centered in a cell using the Merge and Center button on the formatting toolbar, you need to click a table cell other than the one with the merged-and-centered title prior to choosing Format, AutoFormat. Choosing this command when the cell pointer is in a merged-and-centered cell causes Excel to select only that cell for formatting. To get the program to select all the cells in the table (including the merged-and-centered one), place the cell pointer in any non-merged-and-centered cell, and then choose Format, AutoFormat.

SAVING GRACE: AUTORECOVER

The most practical new feature in Excel 2002 has to be its ability to recover unsaved data after you experience some sort of computer glitch or crash. AutoRecover automatically saves changes to your document to any open workbook file in ten-minute intervals (provided that you've manually saved it at least once with File, Save). If your computer crashes, the next time that you start Excel the program automatically opens a Document Recovery task pane (this one on the left side of the work area) where you can open the most complete version of the recovered file.

GET TO KNOW AUTOSUM

The AutoSum tool on the Standard toolbar is worth its weight in gold. It not only enters the SUM function but also selects the most likely range of cells in the current column or row that you want totaled and automatically enters them as the function's argument. And nine times out of ten, Excel selects (by highlighting) the correct cell range to be totaled. For that tenth case, you can manually correct the range by simply dragging the cell pointer through the block of cells that need to be summed.

Position the cell pointer in the cell where the sum is to appear, and click the AutoSum tool (the button looks like a stylized capital letter E). Excel inserts the SUM function (equal sign and all) onto the Formula bar; places a marquee (the moving dotted line) around the cells it thinks you want to add; and uses that cell range as the argument of the SUM function.

BACKGROUND CHECK

In some cases you may want to use a graphics file as a background for a worksheet -- similar to the wallpaper you may display on your Windows desktop. Remember: The graphics background is for the screen display only; it doesn't show up on the page when you print the worksheet.

To add a background to a worksheet, follow these steps:

Activate the worksheet.
Choose the Format, Sheet, Background command. Excel displays a dialog box that lets you choose a graphics file.
Locate the desired graphics file (you may have to change to a different folder).
Click Insert, and Excel tiles your worksheet with the graphic you selected.

IN THE BEGINNING: BOOK 1

When you start Excel without specifying a document to open -- which is what happens when you start the program by clicking the Microsoft Excel button on the Office XP shortcut bar -- you get a blank workbook in a new workbook window. This workbook, temporarily named Book1, contains three blank worksheets (Sheet1, Sheet2, and Sheet3). To begin to work on a new spreadsheet, you simply start entering information in the first sheet of the Book1 workbook window.

USING OFFICE BINDERS

If you have Microsoft Office installed, you may take advantage of its binder feature. A binder is a container that can hold documents from different applications: Excel, Word, and PowerPoint.

You may find that a binder is useful when working on a project that involves documents from different applications. For example, you may be preparing a sales presentation that uses charts and tables from Excel, reports and memos from Word, and slides prepared with PowerPoint. You can store all the information in a single file. Another advantage is that you can print the entire binder, and all pages are numbered sequentially.

To use a binder start the Binder application. You get an empty binder. You then can add existing documents or create new documents in the binder. Consult the online Help for more details on using this feature.

BRING ON THE BORDERS!

To emphasize sections of your worksheet or parts of a particular table, you can add borderlines or shading to certain cells. Don't confuse the borderlines that you add to accent a particular cell selection with the gridlines normally used to define cell borders in the worksheet -- borders that you add are printed whether or not you opt to print the worksheet gridlines.

To add borders to a cell selection, choose Format, Cells. Click the Border tab from the Format Cells dialog box that opens. Select the type of line you want to use in the Style area of the dialog box (such as thick, thin, bold, or hash marks) and then select from the Border section of the dialog box the edge or edges you want this line applied to.

When selecting where you want the borderlines drawn, keep these things in mind:

To have Excel draw borders around only the outside edges of the entire selection, click the Outline button in the Presets section of the Border tab.
If you want borderlines to appear around all four edges of each cell in the selection (like a paned window), select the Inside button in the Presets section instead.

To get rid of borders, you must select the cell or cells that presently contain them, open the Format Cells dialog box, and click the None button in the Presets section. Note that you can also do the same thing by clicking the first button in the Borders pop-up menu (the one showing only dotted lines around and within the rectangle).

NEW 2002 BORDER PATROL

New in Excel 2002, you can have cell borders without having to fool around with the options on the Borders tab of the Format Cells dialog box or attached to the Borders button on the Formatting toolbar. Instead, you simply draw your borders right on the cells of the worksheet.

To do this, click the Borders pop-up button on the Formatting toolbar and then choose the Draw Borders option at the bottom of the menu. Excel responds by displaying the floating Borders toolbar.

When you first open the Borders toolbar, the Pencil tool is selected on this toolbar, and you can use its Pencil mouse pointer to outline the cell range that you select by dragging through its cells. If you want to put borders around each cell in a range that you select with the Pencil pointer, click the drop-down button attached to the Draw Border tool and select the Draw Border Grid option on its pop-up menu before you drag through the cell range.

BREAKING UP ISN'T HARD TO DO

If you import text using the Windows Clipboard, you may find that Excel has placed all of your text into a single column without breaking it down into individual fields in separate columns. Fortunately, Excel can parse text that is stored in a column. Start by selecting the text (in a single column). Then choose Data, Text to Columns; Excel displays the first of three Text to Columns Wizard dialog boxes. These dialog boxes are identical to those used for the Text Import Wizard, except that the title bar text is different.

CELL CLEANUP SEVERAL WAYS

To get rid of just the contents of a cell selection rather than delete the cells along with their contents, select the range of cells to be cleared and press Delete or choose Edit, Clear, Contents on the menu bar.

If you want to get rid of more than just the contents of a cell selection, choose Edit, Clear and then choose from among the submenu commands:

Choose All to get rid of all formatting and notes as well as entries in the cell selection.
Choose Formats to delete only the formatting from the current cell selection without touching anything else.
Choose Comments if you only want to remove the notes in the cell selection but leave everything else behind.

ADDING CELL COMMENTS

The Excel cell comment feature enables you to attach a comment to a cell -- useful if you need to document a particular value or to help you remember what a formula does. When you move the mouse pointer over a cell that has a comment, the comment pops up in a small box.

To add a comment to a cell, follow these steps:

Select the cell.
Choose the Insert, Comment command (or press Shift+F2). Excel displays a text box that points to the cell.
Enter the text for the comment into the text box.
Click any cell when you're finished.

The cell displays a small red triangle to indicate that the cell contains a comment.

ADDING COMMENTS TO A CELL

To add a comment to a cell, follow these steps:

Select the cell to which you want to add the comment.
Choose [I]nsert, Co[m]ment on the menu bar.
A new text box appears. This text box contains the name of the user as it appears in the User [n]ame text box on the General tab of the Options dialog box and the insertion point located at the beginning of a new line right below the user name.
Type the text of your comment in the text box.
When finished entering the comment text, click somewhere outside of the text box.
Excel marks the location of a comment in a cell by adding a tiny triangle in the upper-right corner of the cell. (This triangular note indicator appears in red on a color monitor.)
To display the comment in a cell, position the thick white-cross mouse pointer somewhere in the cell with the note indicator.

ROTATING CELL CONTENTS

If you want to rotate text to shorten the amount of horizontal space that a column requires (at the expense of making the row taller), you can rotate text to be vertical or at any angle between horizontal and vertical. You'll find a wealth of alignment options when you choose Format, Font from the menu bar, including the capability to rotate cell content.

When you rotate text at an angle, the resulting display can have a dramatic effect on your worksheet; you'll probably want to experiment with it, including printing it to assess output quality.

WHERE YOUR CURRENT CELL IS LOCATED

You are here!

Excel has three ways to tell users where the current cell is located:

The Cell Pointer -- The current cell is always displayed with an emphasized frame. The weight of the rule that frames the cell is always heavier than that of a normal cell.
Bolded column letter and row number -- In the worksheet frame the current row number and column letter are always displayed in bold. This makes finding your current cell just like finding your destination on a road map!
The A1 Cell reference -- In the top left corner of the worksheet, just above the column letters, there is a cell that shows a column letter followed by a row number. This cell displays the current cell.

REMEMBERING CELL NAMES

If you've forgotten the range name that you need in a formula, or don't want to type it, do the following when it's time to type the name: Press the F3 function key to display all range names, and then double-click the appropriate name.

DRAGGING AND DROPPING A CELL RANGE

Although drag and drop is primarily a technique for moving cell entries around a worksheet, you can adapt it to copy a cell selection, as well.

To use drag and drop to move a range of cell entries (you can only move one cell range at a time), follow these steps:

Select a cell range.
Position the mouse pointer on one edge of the selected range. Your signal that you can start dragging the cell range to its new position in the worksheet is when the pointer changes to the arrowhead.
Drag your selection to its destination. You drag by depressing and holding down the primary mouse button -- usually the left one -- while moving the mouse. As you drag, you actually move only the outline of the cell range, and Excel keeps you informed of what the new cell range address would be (as a kind of drag-and-drop tooltip) if you release the mouse button at that location. Drag the outline until it's positioned on the new cells in the worksheet where you want the entries to appear (as evidenced by the cell range in the drag-and-drop tooltip).
Release the mouse button. The cell entries within that range reappear in the new location as soon as you release the mouse button.

REMEMBERING CELL NAMES

SENDING THE CELL POINTER TO THE RIGHT PLACE

Excel automatically advances the cell pointer to the next cell down in the column every time you press Enter to complete the cell entry. If you want to customize Excel so that pressing Enter doesn't move the cell pointer as the program enters your data, or to have it move the cell pointer to the next cell up, left, or right, choose Options on the Tools menu and select the Edit tab in the Options dialog box.

To prevent the cell pointer from moving at all, choose the Move Selection after Enter option to remove the check mark from its check box. To have the cell pointer move in another direction, choose the Direction pop-up list box right below the Move Selection after Enter check box and then select the new direction you want to use (Right, Up, or Left). When you're finished changing this setting, click the OK button or press Enter.

NAMING A CELL RANGE

By assigning descriptive names to cells and cell ranges, you can go a long way toward keeping on top of the location of important information in a worksheet. Rather than trying to associate random cell coordinates with specific information, you just have to remember a name. And, best of all, after you name a cell or cell range, you can use this name with the Go To feature.

To name a cell or cell range in a worksheet:

Select the cell or cell range that you want to name.
Click the cell address in the Name Box on the formula bar. Excel selects the cell address in the Name Box.
Type the name for the selected cell or cell range in the Name Box. When typing the range name, you must follow Excel's naming conventions.
Press the Enter key.

SINGLE CELL SAFETY TIPS

Shifting individual cells around could cause problems in other places in your worksheet, so use caution with the Insert, Cells command. Better yet, avoid it if you can and insert entire rows or columns. You should literally never have to use this command.

CELL SELECTION FOR CHART CREATION

If you attempt to create a chart with only the active cell selected, Excel analyzes the location of the active cell and includes all surrounding cells that contain data. Excel will not include any cells beyond empty rows or columns. If what you want charted is neatly encompassed in a range delineated by empty columns and rows and worksheet boundaries, simply click a cell in that range and proceed with chart creation.

CELL SELECTION WITH YOUR MOUSE

The mouse is a natural for selecting a range of cells. Just position the mouse pointer (in its thick, white-cross form) on the first cell and drag in the direction that you want to extend the selection.

To extend the cell selection to columns to the right, drag right, highlighting neighboring cells as you go.
To extend the selection to rows to the bottom, drag down.
To extend the selection down and to the right at the same time, drag diagonally toward the cell in the lower-right corner of the block you are highlighting.

WRAPPING TEXT IN A CELL

Wrapping text within a cell is a good way to display more information without making the column wider. For example, this is often useful for lengthy table headings.

To format a cell or range so that the words wrap around, follow these steps:

Select the cell or range that you want to apply word wrap formatting to.
Choose Format, Cells (or press Ctrl+1).
Click the Alignment tab of the Format Cells dialog box.
Check the box labeled "Wrap text."
Click OK to apply the formatting to the selection.

Remember: When you decrease the column width of a cell that's formatted with wrap text, the words wrap around to the next line to accommodate the new column width.

PELL-MELL TO CELL WELL

If your formula needs to refer to a cell in a different worksheet in the same workbook, use the following format for your formula:

SheetName!CellAddress

Precede the cell address with the worksheet name, following it with an exclamation point.

Remember: If the worksheet name in the reference includes one or more spaces, you must enclose it in single quotation marks. The following is a formula that refers to a cell on a sheet by the name of All Depts:

=A1*'All Depts'!A1

SOUNDING THE ALL CLEAR!

If you wan to get rid of more than just the contents of a cell selection, choose Edit, Clear and then choose from among the submenu commands:

Choose All to get rid of all formatting and notes as well as entries in the cell selection.
Choose Formats to delete only the formatting from the current cell selection without touching anything else.
Choose Comments if you only want to remove the notes in the cell selection but leave everything else behind.

THE ACTIVE CELL AND RANGES

In Excel, one of the cells in a worksheet is always the active cell. The active cell is the one that's selected and it displays with a thicker border. Its contents appear in the formula bar. You can also select a group (or range) of cells by clicking and dragging the mouse over them. When you issue a command that does something to a cell or a range of cells, that something will be done to the active cell or to the selected range of cells.

The selected range is usually a group of contiguous cells, but it doesn't have to be. If you hold down the Ctrl key while you click and drag the mouse, you can select more than one group of cells.

CLEARING VS. DELETING CELLS

No discussion about editing in Excel would be complete without a section on getting rid of the stuff you put into cells. You can perform two kinds of deletions in a worksheet:

Clearing a cell: Just deletes or empties the cell's contents without removing the cell from the worksheet, which would alter the layout of the surrounding cells.
Deleting a cell: Gets rid of the whole kit and caboodle
- cell structure along with all its contents and formatting. When you delete a cell, Excel has to shuffle the position of entries in the surrounding cells to plug up any gaps made by the demise.
- Deleting entire columns and rows from a worksheet is risky business unless you are sure that the columns and rows in question contain nothing of value. Remember, when you delete an entire row from the worksheet, you delete all information from column A through IV in that row (and you can see only a very few columns in this row). Likewise, when you delete an entire column from the worksheet, you delete all information from row 1 through 65,536 in that column.

CALLING YOUR CELLS' NAMES

To name a cell or cell range in a worksheet:

Select the cell or cell range that you want to name.
Click the cell address in the Name Box on the formula bar. Excel selects the cell address in the Name Box.
Type the name for the selected cell or cell range in the Name Box. When typing the range name, you must follow Excel's naming conventions.
Press the Enter key.

USING NAMES FOR CELLS AND RANGES IN EXCEL

Dealing with cryptic cell and range addresses can sometimes be confusing. Fortunately, Excel enables you to assign descriptive names to cells and ranges. You can give a cell a name such as InterestRate, for example, or you can name a range JulySales.

Using names for cells and ranges offers the following advantages:

A meaningful range name (such as TotalIncome) is much easier to remember than a cell address (such as AC21).
You can quickly move to areas of your worksheet by using the Name Box, at the left side of the Formula Bar (by clicking the arrow to open a drop-down list of defined names) or by choosing Edit, Go To from the menu bar (or pressing F5) and specifying the range name.
After you select a named cell or range, its name appears in the Name Box.
Creating formulas is easier. You can paste a cell or range name into a formula by using the Insert, Name, Paste command or by selecting a name from the Name drop-down list box.
Names make your formulas more understandable and easier to use. =Income-Taxes is more intuitive than =D20-D40.
Macros are easier to create and maintain if you use range names rather than cell addresses.

GET THOSE CELLS OUTTA HERE!

To delete the cell selection rather than just clear out its contents, select the cell range and choose Delete from the shortcut menu or Edit, Delete from the menu bar (or press Alt, E, D). Excel displays the Delete dialog box. You use the radio button options in this dialog box to indicate how Excel should shift the cells left behind to fill in the gaps when the cells currently selected are blotted out of existence:

Normally, the Shift cells left (Alt, L) radio button is selected, meaning that Excel moves entries from neighboring columns on the right to the left to fill in gaps created when you delete the cell selection by clicking OK or pressing Enter.
If you want Excel to move entries up from neighboring rows below, click the Shift cells up (Alt, U) radio button.
If you decide to remove all the rows in the current cell selection, click the Entire row (Alt, R) radio button in the Delete dialog box.
If you decide to delete all the columns in the current cell selection, click the Entire column (Alt, C) radio button.

If you know ahead of time that you want to delete an entire column or row from the worksheet, you can select the column or row on the workbook window frame and then choose Delete from the column or row shortcut menu or choose Edit, Delete (Alt, E, D) from the menu. You can remove more than one column or row at a time provided that they all neighbor one another and that you select them by dragging through their column letters or row numbers (Excel can't delete nonadjacent selections).

GIVING YOUR CELLS THE BEST FIT

Sometimes Excel doesn't automatically adjust the width of your columns to your complete satisfaction; however, the program has an added feature that makes changing the column widths a breeze. The easiest way to adjust a column is to do a best-fit, using the AutoFit feature.

With this method, Excel automatically determines how much to widen or narrow the column to fit the longest entry currently in the column. Here's how to use AutoFit to get the best-fit for a column:

Position the mouse pointer on the column frame, on the right border of the column that needs adjusting. The mouse pointer changes to a double-headed arrow pointing left and right.
Double-click the mouse button. Excel widens or narrows the column width to suit the longest entry.

You can apply a best-fit to more than one column at a time. Simply select all the columns that need adjusting (if the columns neighbor one another, drag through their column letters on the frame; if they don't, hold down the Ctrl key as you click the individual column letters). After you select the columns, double-click any of the right borders on the frame.

But AutoFit doesn't always produce the expected results. A long title that spills into several columns to the right produces an awfully wide column when you use best-fit.

HIDE 'N SEEKIN' CELLS

The content of cells that are part of hidden columns and rows are part of the worksheet and you can refer to them in formulas whenever necessary. Active cell movement, however, is restricted. You can't move to hidden cells with the arrow keys or select one with the mouse.

MERGING CELLS

Excel offers a helpful feature that enables you to merge cells into a single, larger cell. This feature enables you to have cells of unequal sizes. If you have a table that spans six columns, for example, you can merge six cells at the top to form a single larger cell for the table's title.

Following are some of the things to remain aware of in merging cells:

You can merge cells horizontally or vertically.
If a selection contains more than one nonempty cell, the merged cells contain the contents and formatting of the upper-left cell of the merged range.
Understanding that cells get merged -- not the contents of the cells -- is very important.

To merge a range of cells, follow these steps:

Select the cells that you want to merge.
Choose Format, Cells. Excel displays the Format Cells dialog box.
Click the Alignment tab.
Select the Merge Cells check box.
Click OK.

To unmerge cells, select the cells and deselect the Merge Cells check box on the Alignment tab of the Format Cells dialog box.

REFERENCING CELLS FAR AND WIDE

If your formula needs to refer to a cell in a different worksheet in the same workbook, use the following format for your formula:

SheetName!CellAddress

Precede the cell address with the worksheet name, following it with an exclamation point.

=A1*'All Depts'!A1

INDENT TEXT IN CELLS

In Excel 2000, you can indent the entries in a cell selection by clicking the Increase Indent button on the Formatting toolbar (the button normally immediately to the left of the Borders button with the picture of the arrow pushing the lines of text to the right). Each time you click this button, Excel indents the entries in the current cell selection to the right by one character width of the standard font.

ROTATING TEXT IN CELLS

You can rotate text within a cell up to 90 degrees. Here's how:

Select the range of cells you want to rotate.
Choose Format, Cells, Alignment.
In the Orientation section either click the diamond to the angle you want the text to be or in the box that says degrees choose the angle you want your text to have.

WRAPPING UP YOUR CELLS

Traditionally, column headings in Excel worksheet tables have been a problem -- you either had to keep them really short or abbreviate them if you wanted to avoid widening all the columns more than the data warranted. You can avoid this problem in Excel by using the Wrap Text feature:

Select the cells with the column headings (the cell range B2:H2).
Choose Format, Cells (Ctrl+1) and select the Alignment tab in the Format Cells dialog box that opens.
Select the Wrap Text check box to activate text wrapping.

Text wrap breaks up the long text entries in the selection (that either spill over or are cut off) into separate lines. To accommodate more than one line in a cell, the program automatically expands the row height so that the entire wrapped-text entry is visible.

When you select Wrap Text, Excel continues to use the horizontal and vertical alignment you specified for the cell. Note that you can use any of the Horizontal alignment options including Left (indent), Center, Right, Justify, or Center Across Selection.

You can't, however, use the Fill option. Select the Fill option on the [H]orizontal drop-down list box only when you want Excel to repeat the entry across the entire width of the cell. And, if you want to wrap a text entry in its cell and have Excel justify the text with both the left and right borders of the cell, select the Justify Fill option on the [H]orizontal pop-up menu in the Alignment tab in the Format Cells dialog box.

CHARACTER REFERENCE

Although Excel allows up to 31 characters (including spaces) for a sheet name, you want to keep your sheet names much briefer for two reasons:

* First, the longer the name, the longer the sheet tab. And the longer the sheet tab, the fewer tabs can be displayed. And the fewer the tabs, the more tab scrolling you have to do to select the sheets you want to work with.

* Second, should you start creating formulas that use cells in different worksheets, Excel uses the sheet name as part of the cell reference in the formula. (How else could Excel keep straight the value in cell C1 on Sheet1 from the value in cell C1 on Sheet2?!) Therefore, if your sheet names are long, you end up with unwieldy formulas in the cells and on the Formula bar even when you're dealing with simple formulas that only refer to cells in a couple of different worksheets.

So remember: As a general rule, the fewer characters in a sheet name, the better.

HANDLING MISSING DATA IN AN EXCEL CHART

Sometimes, data that you're charting may be missing one or more data points. Excel offers several options for handling the missing data. Just follow these steps:

Activate the chart.
Choose the Tools, Options menu command.
In the Options dialog box that appears, click the Chart tab.
Select the option that corresponds to how you want to handle the missing data.
The available radio button options are as follows:
Not Plotted (Leave Gaps): Excel simply ignores missing data, and the data series leaves a gap for each missing data point.
Zero: Excel treats missing data as zero.
Interpolated: Excel calculates missing data by using data on either side of the missing point(s).

Remember: The options that you set apply to the entire active chart; you can't set a different option for different series in the same chart.

CHARTING YOUR CUSTOMIZATION COURSE

Customizing a chart involves changing its appearance, as well as possibly adding new elements to it. These changes can be purely cosmetic (such as changing colors or modifying line widths) or quite substantial (such as changing the axis scales or rotating a 3D chart). New elements that you might add include features such as a data table, a trendline, or error bars.

Before you can customize a chart, you must activate it on a chart sheet, by clicking its sheet tab. To activate an embedded chart, click the chart's border. To deactivate an embedded chart, just click anywhere in the worksheet.

MODIFYING A CHART ELEMENT

Most elements in a chart can be modified in several ways. For example, you can change colors, line widths, fonts, and so on. Modifications are made in the Format dialog box (which varies for each type of chart element).

To modify an element in a chart, follow these steps:

Select the chart element.
Access the Format dialog box using any of the following techniques:
Double-click the item.
Choose the Format [Item Name], command.
Press Ctrl+1.
Right-click the item and choose Format [Item Name], from the shortcut menu.
Click the tab that corresponds to what you want to do.
Make the changes.
Click OK.

CHANGING CHART GRIDLINES

Gridlines can help you determine what the chart series represents numerically. Gridlines simply extend the tick marks on the axes.

To add or remove gridlines, follow these steps:

Activate the chart.
Choose the Chart, Chart Options command.
Click the Gridlines tab.
Check or uncheck the check boxes that correspond to the desired gridlines.

Remember: Each axis has two sets of gridlines: major and minor. Major units are the ones displaying a label. Minor units are those in between. If you're working with a 3-D chart, the dialog box has options for three sets of gridlines.

ADDING A LEGEND TO A CHART

If you create your chart by using the Chart Wizard, you can include a legend. If you don't include a legend as you create the chart, you can add one later if you need one. To add a legend, follow these steps:

Activate the chart.
Choose the Chart, Chart Options menu command.You can also right-click the border area of the chart and select Chart Options from the shortcut menu.
Excel displays its Chart Options dialog box.
Click the Legend tab.
Select the Show Legend check box.
Select a placement option for the legend by clicking the appropriate radio button: Bottom, Corner, Top, Right, or Left.
The picture of the chart in the Legend tab displays how the legend appears on the chart for various placement options.
Click OK.

MOVING CHART TITLES AND LEGENDS

To move a chart title, click the title and drag one of the edges of the rectangular block that surrounds the title. Even though there are handles on this boundary, they serve no purpose. You cannot resize a chart title area with these handles. Change the font size or alter the text and the size of the box, if necessary.

To move a legend, click it and drag it. To resize it, click it and drag one of the eight handles in any direction.

UNDERSTANDING THE CHART TOOLBAR

The Chart toolbar appears when you click an embedded chart, activate a chart sheet, or choose View, Toolbars, (Alt, V, T) Chart. This toolbar includes nine tools. You can use these tools to make some common chart changes:

Chart Objects: When a chart is activated, you can select a particular chart element by using a drop-down list.
Format Selected Object: Displays the Format dialog box for the selected chart element.
Chart Type: Expands to display 18 chart types when you click the arrow.
Legend: Toggles the legend display in the selected chart.
Data Table: Toggles the display of the data table in a chart.
By Row: Plots the data by rows.
By Column: Plots the data by columns.
Angle Clockwise: Displays the selected text at a -45-degree angle.
Angle Counterclockwise: Displays the selected text at a +45-degree angle.

Excel includes several other chart-related tools that aren't on the Chart toolbar. You can customize the toolbar to include these additional tools, which are located in the Charting category in the Customize dialog box.

CHANGING A CHART TYPE

You should always try to choose the type of chart you use in Excel based on the data you intend to display in the chart. For example, area, column, and line charts are ideal for showing changes to values over time, whereas pie charts are ideal for showing percentages. However, sometimes you inherit a chart that you didn't create and that doesn't do the data justice or whose data has changed and as a result so must your chart. In those cases when you want to change a chart, here's what you do:

To change a chart on a worksheet, position your mouse over a blank area in the chart and then press the left mouse button. Handles will appear around the chart. To change a chart on a chart sheet, move the mouse over the tab for the chart sheet and then press the left mouse button.
Select Chart, Chart Type (Alt, C, T). The Chart Type dialog box appears.
Select the chart type that you want.
Select the chart sub-type you want to use and click OK.

The chart changes into the chart type you selected.

CHANGING THE DEFAULT CHART TYPE

Excel's default chart type is a 2D column chart with a light-gray plot area, a legend on the right, and horizontal gridlines.

If you don't like the look of this chart, or if you typically use a different type of chart, you can easily change the default chart:

Select the Chart, Chart Type command Alt, I, H).
Choose the chart type that you want to use as the default chart. This can be a chart from either the Standard Types tab or the Custom Types tab.
Click the button labeled Set as default chart. You are asked to verify your choice.

If you have many charts of the same type to create, changing the default chart format to the chart type with which you're working is much more efficient than separately formatting each chart. Then you can create all of your charts without having to select the chart type.

CHANGING CHART TYPES

The type of chart you should choose depends on your data. Each chart type presents data in a specific way. For example, area, column, and line charts are ideal for showing changes to values over time. Pie charts are ideal for showing percentages. After you create a chart, you can select a different type of chart that will better suit your data. Follow these steps to experiment with different chart types:

To change a chart on a worksheet, click a blank area in the chart you want to change. Handles (little black squares) appear around the chart.
To change a chart on a chart sheet, click the tab for the chart sheet.
Click Chart, Chart Type. The Chart Type dialog box appears.
Click the chart type you want to use from the list of options in the Chart Type box.
Click the chart design you want to use from the Chart Sub-Type box.
Note: The available chart designs depend on the chart type you selected in Step 3.
Click OK to confirm your changes. The chart displays the chart type you selected.

BYPASSING CHART WIZARD

If you just don't have time to complete the Chart Wizard's four-step process, you can create a finished Clustered Column chart-as-graphic-object by selecting the labels and values to be charted, clicking the Chart Wizard button on the Standard toolbar, and then clicking the Finish button in the Chart Wizard Chart Type dialog box.

To create a finished chart on its own chart sheet, select the labels and values to be charted, and then press F11. Excel then creates a new Clustered Column chart using the selected data on its own chart sheet (Chart1) that precedes all the other sheets in the workbook.

ROTATING 3-D CHARTS

As you work with 3-D charts, you may find that some data is completely or partially obscured. You can rotate the chart so that it shows the data better by following these steps:

Activate the 3-D chart.
Choose the Chart, 3-D View menu command.
In the 3-D View dialog box that appears, make your rotations and perspective changes by clicking the appropriate controls.
Click OK (or click Apply to see the changes without closing the dialog box).

You can also rotate the chart in real time by dragging corners with the mouse. Normally, this procedure displays an outline of the chart's borders only. If you press Ctrl while dragging a corner, you can also see outlines of the chart series.

CHANGING A CHART'S LOCATION

If you embed your chart on a worksheet, you can click a border and drag it to a new location on the worksheet. To move the embedded chart to a different sheet or to a separate chart sheet, select the chart and choose the Chart, Location menu command. Specify the new location in the Chart Location dialog box and click OK.

QUESTIONABLE CIRCUMSTANCES

The first place to go for help in Excel is the Ask a Question box that appears on the right side of the Excel Menu bar. This combo box is your ever-present key to accessing the Answer Wizard, a component of the Excel Help system that tries to respond intelligently to your queries by suggesting related help topics to which you may want to refer.

Although it's called the Ask a Question box, you really don't have to ask a formal question; entering a phrase with key terms will usually do just as well. For example, suppose that you want to find out how to print the columns in a spreadsheet table or data list at the top of each page. Instead of having to go through the trouble of typing out the formal question, "How do I get my spreadsheet headings to print on every page of my report?" you can simply enter the keyword phrase "print headings" in the Ask a Question box.

To use the Ask a Question box to consult the Answer Wizard, follow three easy little steps:

Click the Ask a Question box to select whatever text it currently contains.
When you first use the Ask a Question box, it contains the phrase, "Type a question for help." When you click anywhere in this box, Excel selects all of the text in the box so that whatever you begin typing in the box replaces this original text.
Type the keywords or a keyword phrase describing the topic that you want help with in the Ask a Question box.
Whatever you type in the Ask a Question box replaces the original text it contains.
Press Enter to display a pop-up list of possible help topics.
Simply click the topic to open the Help window and display information on one of the help topics listed in the Ask a Question box's pop-up menu. If none of the topics suggested by the Answer Wizard seem to fit the bill, click the See More button at the bottom of the list. If these further topics don't offer a good match, click the Ask a Question box and try using other keywords or more descriptive phrases -- heck, you can even try asking it a real question -- to try to get the Answer Wizard to cough up a pertinent topic or two this time.

CLEARLY CLICKABLE

Although reusing graphics wherever you can to take advantage of the browser's caching ability is tempting, don't use the same graphic as a button on one page and as a decorative headline on another. For example, if you use an icon as a button leading to the About section of the site, don't use the same icon merely as decoration for the headline on that page. Otherwise, people will still think that it's a button. Give the icon a slightly different visual treatment so people know that its function has changed.

FROM CLIPBOARDS TO PICTURE CHARTS

You can create a picture chart using a method that doesn't require that the image exists in a file. This technique works as long as the image can be copied to the Clipboard.

The first step is to locate the image that you want and copy it to the Clipboard. Generally, simpler images work better. You may want to paste it into Excel first, where you can adjust the size, remove the borders, and add a background color, if desired. Or you can create the image using Excel's drawing tools. In either case, copy the image to the Clipboard.

When the image is on the Clipboard, activate the chart, select the data series, and choose the Edit, Paste command (Alt, E, P). Your chart is converted. You also can paste the image to a single point in the data series, rather than the entire data series. Just select the point before you paste. This technique also works with data markers in line charts, XY (scatter) charts, or bubble charts.

COLOR YOUR WORKSHEET WORLD

A new feature in Excel 2002 enables you to add a color to a worksheet tab. You can use this feature, for example, to identify a specific worksheet by its color. To color a worksheet tab, follow these steps:

Select the worksheet tab that you want to color.
Use any of the following methods to open the Format Tab Color dialog box:
Choose Format, Sheet, Tab Color from the menu bar.
Right-click the sheet tab and choose Tab Color from the shortcut menu that appears.
Select a color for the tab from the Tab Color palette.
Click OK.

To undo the tab color, follow the same procedure and click the No Color option in the Format Tab Color dialog box and click OK.

Remember, if you select a tab that you color-code, the sheet name appears underlined with the color. If a tab displays a background color, it's not currently selected.

BACKGROUND COLORS AND PATTERNS

To change the background color or pattern that you use in cells, follow these steps:

Select the cell or range that you want to format.
Choose Format, Cells from the menu bar (or press Ctrl+L).
Click the Patterns tab in the Format Cells dialog box that appears.
Click a color on the Color grid.
To add a pattern, click the Pattern drop-down list box and select a pattern from the list. If you want, you can choose a second color for the pattern.
Click OK to apply the color and/or pattern.

A faster way to change the background color (but not a pattern) is to select the cells and then select a color from the Fill Color tool on the Formatting toolbar.

Remember: If you use background colors or patterns, you may not receive the results that want if you print with a noncolor printer.

DEFAULT COLUMN WIDTH

To change the default width of all columns, use the Format, Column, Standard Width command. This command displays a dialog box into which you enter the new default column width. All columns that haven't been previously adjusted take on the new column width.

ASSIGNING 26 LETTERS TO 256 COLUMNS IN EXCEL

When it comes to labeling the 256 columns in an Excel worksheet, our alphabet - with its measly 26 letters - is not up to the task. To make up the difference, Excel doubles up the cell letters in the column reference so that column AA immediately follows column Z. This is followed by column AB, AC, and so on to AZ. After column AZ, you find column BA, and then BB, BC, and so on. According to this system for doubling the column letters, the 256th (and last) column of the worksheet is column IV. This, in turn, gives the very last cell of any worksheet the cell reference IV65536.

CALIBRATING COLUMNS

For those times when Excel 2002 doesn't automatically adjust the width of your columns to your complete satisfaction, the program makes your changing the column widths a breeze. The easiest way to adjust a column is to do a best-fit, using the AutoFit feature. With this method, Excel automatically determines how much to widen or narrow the column to fit the longest entry currently in the column.

Here's how to use AutoFit to get the best-fit for a column:

Position the mouse pointer on the right border of the gray frame with the column letter at the top of the worksheet. The mouse pointer changes to a double-headed arrow pointing left and right.
Double-click the mouse button. Excel widens or narrows the column width to suit the longest entry.

You can apply a best-fit to more than one column at a time. Simply select all the columns that need adjusting (if the columns neighbor one another, drag through their column letters on the frame; if they don't, hold down the Ctrl key while you click the individual column letters). After you select the columns, double-click any of the right borders on the frame.

DELETING COMMENTS

To delete a comment, you need to select its cell in the worksheet and then choose [E]dit, Cle[a]r, Co[m]ments on the menu bar or choose Delete Co[m]ment on the cell's shortcut menu. Excel removes the comment along with the note indicator from the selected cell.

DEALING WITH DATA CONSOLIDATION

Data consolidation refers to the process of merging data from multiple worksheets or multiple workbook files. For example, a division manager may consolidate various departmental budgets into a single workbook.

The main factor that determines how easy a consolidation task will be is whether the information is laid out exactly the same way in each worksheet. If so, the job is relatively simple.

If the worksheets have little or no resemblance to each other, your best bet may be to edit each sheet so they match each other. In some cases, it may be more efficient to simply reenter the information in a standard format.

DOLLING UP YOUR DATA

You can make data in your worksheet look more attractive by using various fonts, styles, sizes, underlines, colors, and special effects. Follow these steps and liberate your creativity:

Select the cells containing the data you want to change.
Click Format, Cells and then click on the Font tab.
To select a font for the data, click on the font you want to use. Look in the preview areas to see what it looks like.
To select a style for the data, click the style you want to use.
To select a size for the data, click the size you want to use.

DATA ENTRY EXPRESS

You can save a lot of time and energy when you want the same entry (text, value, or formula) to appear in many cells of the worksheet; you can enter the information in all the cells in one operation. You first select the cell ranges to hold the information. (Excel lets you select more than one cell range for this kind of thing.) Then you construct the entry on the formula bar and press Ctrl+Enter to put the entry into all the selected ranges.

The key to making this operation a success is to hold the Ctrl key as you press Enter so that Excel inserts the entry on the formula bar into all the selected cells. If you forget to hold Ctrl and you just press Enter, Excel places the entry in the first cell only of the selected cell range.

You can also speed up data entry in a list that includes formulas by making sure that the "Extend list formats and formulas (Alt, I)" check box is selected on the Edit tab in the Options dialog box (opened by selecting Tools, Options (Alt, T, O) on the menu bar). When this check box is selected, Excel automatically formats new data that you type in the last row of a list to match that of like data in earlier rows and copies down formulas that appear in the preceding rows. Note, however, that for this new feature to "kick in," you must have manually entered the formulas and formatted the data entries in at least three rows preceding the new row.

FILTERING DATA IN A LIST

To filter the database to just those records that contain a particular value, you then click the appropriate field's drop-down list button to open a list box containing all the entries made in that field and select the one you want to use as a filter. Excel then displays only those records that contain the value you selected in that field (all other records are temporarily hidden).

After you've filtered a database so that only the records you want to work with are displayed, you can copy those records to another part of the worksheet to the right of the database (or better yet, another worksheet in the workbook). Simply select the cells and then choose Edit, Copy on the menu bar (Alt, E, C), move the cell pointer to the first cell where the copied records are to appear, and press Enter. After copying the filtered records, you can then redisplay all the records in the database or apply a slightly different filter.

CREATING A DATA FORM

You can create a data form for a new database simply by entering a row of field names and selecting them before you choose Data, Form on the menu bar. When you do this, Excel displays the alert dialog box indicating that the program can't determine which row in your list contains the column labels (that is, the field names). To have Excel use the selected row as the field names, click OK or press Enter. Excel will then create a blank data form listing all the fields down the form in the same order as they appear across the selected row.

Creating a blank data form from field names alone is just fine, provided that your database doesn't contain any calculated fields (that is, fields whose entries result from a formula's computation rather than from manual entry). If your new database will contain calculated fields, you need to build their formulas in the appropriate fields of the first record. Then select both the row of field names and the first database record with the formulas indicating how the entries are calculated before you choose Form on the Data menu. Excel knows which fields are calculated and which are not (you can tell that a field is a calculated field in the data form because Excel lists its field name but does not provide a text box for you to enter any information for it).

CREATING A DATA FORM FROM FIELD NAMES ALONE

You can create a data form for a new database simply by entering a row of field names and selecting them before you choose Data, Form on the menu bar. When you do this, Excel displays the alert dialog box indicating that the program can't determine which row in your list contains the column labels (that is, the field names). To have Excel use the selected row as the field names, click OK or press Enter. Excel will then create a blank data form listing all of the fields down the form in the same order as they appear across the selected row.

Creating a blank data form from field names alone is just fine, provided that your database doesn't contain any calculated fields (that is, fields whose entries result from a formula's computation rather than from manual entry). If your new database will contain calculated fields, you need to build their formulas in the appropriate fields of the first record. Then select both the row of field names and the first database record with the formulas indicating how the entries are calculated before you choose Form on the Data menu. Excel knows which fields are calculated and which are not (you can tell that a field is a calculated field in the data form because Excel lists its field name but does not provide an edit box for you to enter any information for it).

USING DATA ENTRY FORMS

If you're entering data that is arranged in rows, you may find it helpful to use Excel's built-in data form for data entry. To enter data using a data entry form, follow these steps:

If your data entry range does not have descriptive headings in the first row, enter some headings. You can always erase these headings later if you don't need them.
Select any cell in the header row.
Choose the Data, Form command.
Excel asks whether you want to use that row for headers (click OK). It then displays a dialog box with edit boxes and several buttons.
Enter data into the edit boxes, using Tab to move between the boxes. When you complete entering the data for a row, click the New button. Click Close when finished.
Excel dumps the data into the worksheet and clears the dialog box for the next row.

DO THE DATA HUDDLE

The amount of computer memory available to Excel determines the ultimate size of the spreadsheet you can build, not the total number of cells in the worksheets of your workbook. When you run out of memory, you've effectively run out of space -- no matter how many columns and rows are still left to fill. To maximize the information you can get into a single worksheet, always adopt the "covered wagon" approach to worksheet design by keeping your data close together.

DEALING WITH DATA LABELS

Sometimes, you want your chart to display the actual data values for each point on the chart. Or you may want to display the category label for each data point.

To add data labels to a chart series, follow these steps:

Activate the chart by clicking on the chart sheet's tab or clicking on the chart.
Select the data series.
Choose the Format, Selected Data Series command (or double-click the data series in the chart).
In the Format Data Series dialog box, click the Data Labels tab.
Select the option that corresponds to the type of data labels that you want.

MISSING DATA IN A CHART

Sometimes, data that you're charting may be missing one or more data points. Excel offers several options for handling the missing data:

Activate the chart.
Choose the Tools, Options command.
In the Options dialog box, click the Chart tab.
Select the option that corresponds to how you want to handle the missing data.

The options are as follows:

Not plotted (leave gaps): Missing data is simply ignored, and the data series has a gap for each missing data point.
Zero: Missing data is treated as zero.
Interpolated: Missing data is calculated using data on either side of the missing point.

Remember: The options that you set apply to the entire active chart; you can't set a different option for different series in the same chart.

SEARCHING AND REPLACING DATA

Excel 2002 adds considerable enhancements to Excel's search and replace capabilities, as the following list describes:

A single Find and Replace dialog box replaces the separate Find and Replace dialog boxes.
Find What is now a drop-down list box so that you can search for previously searched for items.
You can search for information with specific formatting (for example, bold and red text with a yellow background and a specific number format and so on).
You can search for information in a single worksheet or the entire workbook and display a window showing all references.

RESTORING YOUR DATABASE AFTER FILTERING

To restore a database list after filtering its records, click the AutoFilter button in the field or fields involved in the filtering and click the (Show All) check box to restore its check mark (and the check marks in all the boxes for all the individual entries, as well) before you click the OK button at the bottom of the pop-up list.

DATE- AND TIME-STAMPING

Sometimes, you need to date-stamp or time-stamp your worksheet. Excel provides two shortcut keys that do this for you:

Current date: Ctrl + ; (semicolon)
Current time: Ctrl + Shift + ; (semicolon)

Use these shortcuts to insert the date or time into a worksheet cell.

ENTERING DATES FOR THE 21st CENTURY

Contrary to what you may think, when entering dates in the 21st century, you need to enter only the last two digits of the year. To put the date January 6, 2002 in a worksheet, for example, enter 1/6/02 in the target cell. Likewise, to put the date February 15, 2010 in a worksheet, enter 2/15/10 in the target cell.

Note that this system of having to put in only the last two digits of dates in the 21st century works only for dates in the first three decades of the new century (2000 through 2029). To enter dates for the years 2030 on, you need to input all four digits of the year.

This procedure also means, however, that to put in dates in the first three decades of the 20th century (1900 through 1929), you must enter all four digits of the year. For instance, to put in the date July 21, 1925, you have to enter 7/30/1925 in the target cell. Otherwise, if you enter just the last two digits (25) for the year part of the date, Excel will enter a date for the year 2025 and not 1925!

Excel 2002 always displays all four digits of the year in the cell and on the Formula bar even when you only enter the last two. So, for instance, if you enter 11/06/03 in a cell, Excel automatically displays 11/06/2003 in the worksheet cell (and on the Formula bar when that cell is current).

That way, you can always tell when you have entered a 20th rather than a 21st century date even if you can't keep the stupid rules straight for when to enter just the last two digits and when to enter all four.

HOW TO ENTER DATES AND TIMES INTO EXCEL

Excel recognizes the following time formats:

3 AM or PM
3 A or P (for AM or PM)
3:21 AM or PM
3:21:04 AM or PM
15:21
15:21:04

Excel knows the following date formats. (Note that month abbreviations always use the first three letters of the name of the month, as in Jan, Feb. . . .)

November 2, 2001 or November 2, 01
11/2/01 or 11-2-01
2-Nov-01 or 2/Nov/01 or even 2Nov01
11/2 or 2-Nov or 2/Nov or 2Nov
Nov-01 or Nov/01 or Nov01

AUTOMATIC DECIMAL POINTS

If you're entering lots of numbers with a fixed number of decimal places, you can save time by letting Excel enter the decimal point (like the feature available on some adding machines).

Select the Tools, Options command.
Click the Edit tab.
Check the check box labeled Fixed Decimal and make sure that it's set for the number of decimal places that you want to use.

Excel now supplies the decimal points for you automatically. For example, if you have it set for two decimal places and you enter 12345 into a cell, Excel interprets it as 123.45 (it adds the decimal point). To restore things to normal, just uncheck the Fixed Decimal check box in the Options dialog box.

Remember: Changing this setting doesn't affect any values that you have already entered.

IN A FIX OVER DECIMAL PLACES

While the Fixed Decimal setting is turned on, Excel adds a decimal point to all the numeric values that you enter. However, if you want to enter a number without a decimal point, or one with a decimal point in a position different from the one called for by this feature, you have to remember to type the decimal point (period) yourself. For example, to enter the number 1099 instead of 10.99 when the decimal point is fixed at two places, type 1099 followed immediately by a period (.) in the cell.

And, for heaven's sake, please don't forget to turn off the Fixed Decimal feature before you start work on another worksheet or exit Excel. Otherwise, when you intend to enter values such as 20, you'll end up with 0.2 instead, and you won't have a clue what's going on!

USING FIXED DECIMAL PLACES CAREFULLY

While the Fixed Decimal setting is turned on, Excel adds a decimal point to all the numeric values you enter. However, if you want to enter a number without a decimal point, or one with a decimal point in a position different from the one called for by this feature, you have to remember to type the decimal point (period) yourself. For example, to enter the number 1099 instead of 10.99 when the decimal point is fixed at 2 places, type 1099. in the cell.

And, for Heaven's sake, please don't forget to turn off the Fixed Decimal feature before you start work on another worksheet or exit Excel. Otherwise, when you intend to enter values like 20, you'll end up with 0.2 instead, and you won't have a clue what's going on!

OPEN DIALOG BOXES BUTTONS

Use the buttons on the left side of the Open dialog box (History, My Documents, Desktop, Favorites, and Web folders) to easily open any folders associated with these buttons that contain workbook files:

Click the History button to open workbook files saved in the Recent folder (located inside the Office folder within the Microsoft folder).
Click the My Documents button to open workbook files saved in the Personal folder inside the Windows folder. (In fact, on some computers, the My Documents button in the Excel 2000 Open dialog box appears as the Personal button.)
Click the Desktop folder to open workbook files saved directly on the desktop of your computer.
Click the Favorites button to open workbook files saved in the Favorites folder inside the Windows folder.
Click the Web folders button to open workbook files (especially those saved as Web pages) saved in any Web folders that are created on your hard disk.

DOCUMENT RECOVERY

Everyone has experienced the pain of losing data due to power outages or computer crashes. Normally, the only prescription for this was the suggestion to make sure you back your work up. Excel 2002, however, offers a document recovery feature to help minimize the frustration of having to deal with such a scenario. The AutoRecovery feature saves your documents in regular intervals saving you the heartache and a bundle on stress relieving therapies.

DOCUMENT RECOVERY TO THE RESCUE

Excel 2002 offers a new document recovery feature that can help you in the event of a computer crash because of a power failure or some sort of operating system freeze or shutdown. The AutoRecover feature saves your workbooks on regular intervals. In the event of a computer crash, Excel displays a Document Recovery task pane the next time you start Excel after rebooting your computer.

When you first start using Excel 2002, the AutoRecover feature is set to automatically save changes to your workbook every ten minutes. You can shorten or lengthen this interval as you see fit. Choose Tools, Options, and then click the Save tab. Use the spinner buttons or enter a new automatic save interval into the text box marked "Save AutoRecover Info Every 10 Minutes" before clicking OK.

Note that the AutoRecover feature works only on Excel workbooks that you have saved at least one time. In other words, if you build a new workbook and don't bother to save and rename it prior to experiencing a computer crash, the AutoRecover feature will not bring back any part of it. For this reason, it is very, very important that you get into the habit of saving new workbooks with File, Save very shortly after beginning work on one of its worksheets. Or, you can use the trusty keyboard shortcut Ctrl+S.

PROTECTING YOUR DOCUMENTS

After you've more or less finalized a worksheet by checking out its formulas and proofing its text, you often want to guard against any unplanned changes by protecting the document.

Each cell in the worksheet can be locked or unlocked. By default, Excel locks all the cells in a worksheet so that, when you follow these steps, Excel locks the whole thing up tighter than a drum.

Choose [T]ools, [P]rotection, [P]rotect Sheet on the menu bar. Excel opens the Protect Sheet dialog box where the [C]ontents, [O]bjects, and [S]cenarios check boxes are all selected.
If you want to assign a password that must be supplied before you can remove the protection from the worksheet, type the password in the [P]assword (optional) text box.
Click OK or press Enter. If you typed a password in the [P]assword (optional) text box, Excel opens the Confirm Password dialog box. Reenter the password in the [R]eenter password to proceed text box exactly as you typed it into the [P]assword (optional) text box in the Protect Sheet dialog box and click OK or press Enter.

ROUNDING DOLLAR VALUES

Often, you need to round dollar values to the nearest penny. For example, a calculated price may be something like $45.78923. In such a case, you should round the calculated price to an even penny. This may sound simple, but there are actually three ways to round such a value:

Round it up to the nearest penny.
Round it down to the nearest penny.
Round it to the nearest penny (the rounding may be up or down).

The following formula assumes a dollars and cents value is in cell A1. The formula rounds the value to the nearest penny. For example, if cell A1 contains $12.421, the formula returns $12.42.

=ROUND(A1,2)

If you need to round the value up to the nearest penny, use the CEILING function. The following formula rounds the value in cell A1 up to the nearest penny. If, for example, cell A1 contains $12.421, the formula returns $12.43.

=CEILING(A1,0.01)

To round a dollar value down, use the FLOOR function. The following formula, for example, rounds the dollar value in cell A1 down to the nearest penny. If cell A1 contains $12.421, the formula returns $12.42.

=FLOOR(A1,0.01)

DRAG-AND-DROP IN INSERT MODE

Drag-and-drop in Insert mode is one of Excel's most finicky features. Sometimes, you do everything just right and you still get the alert box indicating that Excel is about to replace existing entries instead of pushing them aside (always click the Cancel button). Fortunately, you can insert things with the Cut (Alt, C) and Insert Paste (Alt, I) commands without worrying about which way the I-beam selection goes.

PERSNICKETY DRAG-AND-DROP

Drag-and-drop in Insert mode is one of Excel's more finicky features. Sometimes, you do everything just right and you still get the alert box indicating that Excel is about to replace existing entries instead of pushing them aside (always click the Cancel button). Fortunately, you can insert things with the Cut (Alt, C) and Insert Paste (Alt, I) commands without worrying about which way the I-beam selection goes.

CLEARING AN EXCEL CELL

When using Excel, keep in mind that clearing a cell and deleting a cell are very different actions. When you clear a cell, its contents are removed, but the cell remains in the worksheet. When you delete a cell you remove the cell--contents and all--from the worksheet.

A TALE OF TWO EDITS

Excel gives you a choice between editing a cell's contents in the cell or on the Formula bar. Whereas, most of the time, editing right in the cell is just fine, when dealing with really, really long entries (like humongous formulas that seem to go on forever or text entries that take up paragraphs and paragraphs), you may prefer to do your editing on the Formula bar.

Editing on the formula bar is easier because Excel expands the Formula bar to as many rows as are necessary to display the entire cell contents, whereas, in the worksheet display, the cell contents may be running right off the screen.

To edit the contents in the Formula bar rather than in the cell itself, you must position the cell pointer in the cell and then double-click somewhere (probably the first place that needs changing) in the cell contents on the Formula bar.

ERROR HANDLING

When working with VBA, you should be aware of two broad classes of errors: programming errors -- or bugs -- and run-time errors. A well-written program handles errors the way Fred Astaire danced: gracefully. Fortunately, VBA includes several tools to help you identify errors -- and then handle them gracefully.

Error handling involves trapping run-time errors and handling them in some way. If you write programs for only your own use, you might find error handling unnecessary. If a run-time error occurs, you simply deal with it. Because you wrote the code, you'll know whether the error is serious or not.

Error handling becomes important if other people -- especially inexperienced users -- use your programs. For example, suppose that you were running someone's macro and the macro suddenly stopped. Wouldn't you want to see a friendly message that tells you how to proceed, rather than a techno-babble error message that states "Object variable not set"?

COMMON ERROR VALUE

Under certain circumstances, even the best formulas can appear to have freaked out once you get them in your worksheet. You can tell right away that a formula's gone haywire because you get a strange, incomprehensible message instead of the calculated value you expected. This weirdness is known, in the parlance of spreadsheets, as an error value. Its purpose is to let you know that some element is preventing Excel from returning the anticipated calculated value. A typical error value looks like this: #REF! (This one refers to when Excel encounters an invalid cell reference.)

TRACING ERROR VALUE

The Trace Error button on the Auditing toolbar helps you to identify the cell that is causing an error value to appear. Often, an error in one cell is the result of an error in a precedent cell. Activate a cell that contains an error, and click the Trace Error button. Excel draws arrows to indicate the error source.

PRINTING OR SUBSTITUTING ERROR VALUES

A new feature in Excel 2002 gives you the option to print error values as they appear on your worksheet or to replace each of the error values with a predefined character in the printed output. Error values include #NUM!, #DIV/0!, #REF!, #N/A, #VALUE!, #NAME?, and #NULL!.

To select how you want error values to appear in your printed output, follow these steps:

Choose File, Page Setup (Alt, F, U) from the menu bar.
Click the Sheet tab of the Page Setup dialog box that appears.
Choose an option from the Cell Errors As drop-down lists box (Alt, E).
Click OK to close the Page Setup dialog box.

DISABLING EVENTS

Excel is capable of monitoring a wide variety of events and executing your VBA code when a particular event occurs. For example, workbook events such as Open (when the workbook is open or created) occur for a particular workbook. By default, all events are enabled. To disable all events, execute the following VBA instruction:

Application.EnableEvents = False

To enable events, use this instruction:

Application.EnableEvents = True

Why do you need to disable events? The main reason is to prevent an infinite loop of cascading events from occuring.

For example, assume you've written code that executes whenever data is entered into a cell (this particular cell must contain a text string). In this case you use a procedure named "Worksheet_Change" to monitor the Change event for a Worksheet. Your procedure validates the user's entry, and if that entry is not a string, it displays a message and then clears the entry. The problem is that clearing the entry with your VBA code generates a new Change event, so your event-handler procedure executes again. This is not the intention, so you must disable events before you clear the cell, and then enable events again so you can monitor the user's next entry.

F3 KEY AND RANGE NAMES

You can rely on the F3 key for four different uses in relation to range names:

Press the F3 function key to display all range names.
Use Ctrl+F3 to activate the Edit, Define Name command.
When you're not in the midst of editing, press the F3 key to activate the Edit, Paste Name dialog box. Here you have the ability to paste a list of range names into your workbook.
Use Ctrl+Shift+F3 after you've highlighted some cells that contain a border row or column that you'd like to use as range names.

AMAZING EXCEL FACTS

You may find it interesting to stop and think about the actual size of a worksheet. Do the arithmetic (256 x 65,536), and you'll see that a worksheet has 16,777,216 cells. Remember: This is in just one worksheet. A single workbook can hold more than one worksheet.

If you're using the standard VGA video mode with the default row heights and column widths, you can see nine columns and 18 rows (or 162 cells) at a time. This works out to less than 0.0001 percent of the entire worksheet. In other words, nearly 104,000 VGA screens of information reside inside a single worksheet.

If you entered a single digit into each cell at a relatively rapid clip of one cell per second, it would take you about 194 days, nonstop, to fill up a worksheet. To print the results of your efforts would require more than 36,000 sheets of paper -- a stack about six feet tall.

FILE DISPLAY PREFERENCES

The Open dialog box can display your workbook filenames in four different styles:

List: As a list of filenames only, displayed in multiple columns
Details: As a list of filenames, with details about each file (its size, file type, and when it was last modified)
Properties: As a list of filenames, with file properties displayed in a separate panel for the selected file
Preview: As a list of filenames, with a preview screen displayed in a separate panel for the selected file

You control the style by clicking the View icon and then selecting from the drop-down list.

FILE NAMING RULES

Excel's workbook files are subject to the same rules that apply to other Windows 95 (or later) files. A filename can be up to 255 characters, including spaces. This enables you (finally) to give meaningful names to your files. You can't, however, use any of the following characters in your filenames:

\ (slash)
? (question mark)
: (colon)
* (asterisk)
" (quote)
< (less than)
> (greater than)
| (vertical bar)

You can use uppercase and lowercase letters in your names to improve readability. The filenames aren't case-sensitive, however. If you have a file named My 1999 Budget and try to save another file with the name MY 1999 BUDGET, Excel asks whether you want to overwrite the original file.

If you plan to share your files with others who use Excel 5 or earlier, you should make sure that the filename is no longer than eight characters, with no spaces. Otherwise, the filename will appear rather strange. For example, a file named My 1999 Budget will appear as MY1999~1.XLS, because Windows assigns every file an eight-character filename to be compatible with pre-Windows 95 operating systems.

KEEPING A SHARED FILE SAFE

If you are creating a workbook whose contents are to be updated by several different users on your network, you can use the Protect and [S]hare Workbook command on the [T]ools, [P]rotection submenu.

This command ensures that Excel tracks all the changes made and that no user can intentionally or inadvertently remove Excel's tracking of changes made to the file.

To do this, you simply select the Sharing with Tracked Changes check box in the Protected Shared Workbook dialog box that appears after choosing the command.

After selecting this check box, you can, if you so desire, add a password in the Password (optional) text box below that each user must supply before he or she can open the workbook to make any changes.

OPENING MULTIPLE FILES SIMULTANEOUSLY

When using the Open dialog box, you can select several consecutive files by holding down the Shift key and clicking the first and then the last file in the group you want to select. To select nonconsecutive files, hold down the Ctrl key as you click the different filenames. When you click the Open button you will open all of the selected files.

OPENING FILES WITH A TWIST

Through the pop-up menu for the open dialog box in Excel 2002 users can access files by:

Open Read Only -- allowing you to view the document but not change it.
Open as Copy -- allowing you to open a duplicate of the file without altering the original.
Open in Browser -- allowing you to view your workbooks as Web pages in your favorite browser (only available if you saved your file as a Web page).
Open and Repair -- allowing you to attempt to repair damaged files before opening them in Excel.

TOP-NOTCH FILTERING

When filtering, you can choose Excel's so-called Top 10 option, but don't take the terms literally. The default setting is to choose the Top 10 Items. Substitute the 10 with any number you want, substitute the word Bottom for Top, or substitute the word Percent for Items. Looking for all the records in the upper quartile? Change 10 to 25 and change Items to Percent and you'll get your data.

STANDARD FONT OPTIONS

When you start a new worksheet, Excel assigns a uniform font and type size to all the cell entries you make. This font varies according to the printer you use -- for a laser printer like the HP LaserJet or Apple LaserWriter, Excel uses a font called Arial in a 10-point size. Although this font is fine for normal entries, you may want to use something with a little more zing for titles and headings in the worksheet.

If you don't especially care for the standard font that Excel uses, modify it by choosing Tools, Options on the menu bar and then selecting the General tab. When you do this, you see an option called Standard font near the bottom of the Options dialog box. Select the new standard font from its drop-down list. If you want a different type size, choose the Size option as well and either enter the new point size for the standard font or select it from this option's drop-down list.

FRONTPAGE FORM TEMPLATES

FrontPage 2002 includes several templates that contain pre-designed forms. If you are looking for a relatively standard form type, starting with one of these templates is likely to save you some time. The following are the form templates available in the Page Templates dialog box (unfortunately, they are somewhat randomly mixed in with various other kinds of templates):

Feedback form: A simple form designed to solicit comments from users on a variety of company-related topics. It uses the Save Results component to send input to a text file.
Guest book: A basic text-input form that enables site visitors to record a comment, much like the guest register of a small hotel or bed-and-breakfast inn. Although this form is a relatively simplistic way to encourage user involvement on your Web, a surprising number of people actually take the time to sign your guest book (especially if you let them read what they and others have written). The Guest Book form uses the Save Results component to send comments to an HTML page that can be viewed by visitors to your Web site.
Search page: A simple one-field text-string search form used in conjunction with FrontPage's built-in text search engine.
User registration: Enables users to enter a user name and password that allows them to access a designated access-controlled Web. This component is restricted to certain Web servers and must be saved in a root Web. Results are processed by the Web Registration component.

FOOLING AROUND WITH FORMAT PAINTER

When you feel the urge to format on the fly (so to speak), use the Format Painter on the Standard toolbar (the button that looks like a paintbrush right next to the Paste tool). This wonderful little tool enables you to take the formatting from a particular cell that you've fancied up and apply its formatting to other cells in the worksheet simply by selecting those cells.

To use the Format Painter to copy a cell's formatting to other worksheet cells, just follow these easy steps:

Format an example cell or cell range in your workbook, selecting whatever fonts, alignment, borders, patterns, and color you want it to have.
With the cell pointer in one of the cells you just fancied up, click the Format Painter button in the Standard toolbar.
The mouse pointer changes from the standard thick, white cross to a thick, white cross with an animated paintbrush by its side, and you see a marquee around the selected cell whose formatting is to be used by the Format Painter.
Drag the white-cross-plus-animated-paintbrush pointer (the Format Painter pointer, for short) through all of the cells you want to format in the same manner as the example cell you first selected.

As soon as you release the mouse button, Excel applies all the formatting used in the example cell to all the cells you just selected!

To keep the Format Painter selected so that you can format a bunch of different cell ranges with the Format Painter pointer, double-click the Format Painter button after you select the sample cell with the desired formatting. To stop formatting cells with the Format Painter pointer, you simply click the Format Painter button (it remains depressed when you double-click it) again to restore the button to its undepressed state and return the mouse pointer to its normal thick, white-cross shape.

FORMAT PAINTER PRIMER

You can use the Format Painter to restore a cell range that you gussy all up back to its boring default (General) cell format. To do this, click an empty, previously unformatted cell in the worksheet before you click the Format Painter button and then use the Format Painter pointer to drag through the cells you want returned to the default General format.

COPY FORMATTING

Once you format one cell to suit your needs, you can make other cells look exactly the same. Follow these steps to save valuable time:

Click a cell displaying the formatting you want to copy.
Click the Paintbrush tool to copy the formatting.
Select the cells you want to display the formatting. It's as easy as that!

ACCEPTABLE WAYS TO BEGIN A FORMULA

When you type an equal sign into an empty cell, Excel assumes that you are entering a formula (a formula always begins with an equal sign). Excel's accommodating nature also permits you to begin your formula with a minus sign or a plus sign. However, Excel always inserts the leading equal sign after you enter the formula.

As a concession to former 1-2-3 users, Excel also enables you to use an @ symbol to begin a formula that stars with a function. For example, Excel accepts either of the following formulas:

=SUM(A1:A200)
@SUM(A1:A200)

However, after you enter the second formula, Excel replaces the @ symbol with an equal sign.

MAKING AN EXACT COPY OF A FORMULA

As you may already know, when you copy a formula, Excel adjusts its cell references when you paste it to a different location. Sometimes you may want to make an exact copy of the formula. One way to do this is to convert the cell references to absolute values, but this isn't always desirable. A better approach is to select the formula while you're in edit mode and then copy it to the Clipboard as text. There are several ways to do this. Here's a step-by-step example of how to make an exact copy of the formula in A1 and copy it to A2:

Double-click on A1 to get into edit mode.
Drag the mouse to select the entire formula. You can drag from left to right or from right to left.
Click the Copy button on the Standard toolbar. This copies the selected text to the Clipboard.
Press Enter to end edit mode.
Activate cell A2.
Click the Paste button to paste the text into cell A2.

You can also use this technique to copy just part of a formula to use in another formula. Just select the part of the formula that you want to copy by dragging the mouse; then use any of the available techniques to copy the selection to the Clipboard. You can then paste the text into another cell.

Formulas (or parts of formulas) copied in this manner won't have their cell references adjusted when they are pasted to a new cell. This is because the formulas are being copied as text, not as actual formulas.

FORMULA ERRORS

Suppose you have a lengthy formula that contains an error, and Excel won't let you enter it because of the error. In this case, you can convert the formula to text and tackle it again later. To convert a formula to text, just remove the initial equal sign (=). To try the formula again, insert the initial equals sign to convert the cell contents back to a formula.

FORMULA ERROR MESSAGES

Under certain circumstances, even the best formulas can appear to have freaked out once you get them in your worksheet. You can tell right away that a formula's gone haywire because instead of the nice calculated value you expected to see in the cell, you get a strange, incomprehensible message in all uppercase letters beginning with the number sign (#) and ending with an exclamation point (!) or, in one case, a question mark (?). This weirdness is known, in the parlance of spreadsheets, as an error value. Its purpose is to let you know that some element -- either in the formula itself or in a cell referred to by the formula -- is ------------ preventing Excel from returning the anticipated calculated value. Here is a list of some error values and their meanings:

#DIV/0! -- Appears when the formula calls for division by a cell that either contains the value 0 or, as is more often the case, is empty. Division by zero is a no-no according to our math.
#NAME? -- Appears when the formula refers to a range name that doesn't exist in the worksheet. This error value appears when you type the wrong range name or fail to enclose in quotation marks some text used in the formula, causing Excel to think that the text refers to a range name.
#NULL! -- Appears most often when you insert a space (where you should have used a comma) to separate cell references used as arguments for functions.
#NUM! -- Appears when Excel encounters a problem with a number in the formula, such as the wrong type of argument in an Excel function or a calculation that produces a number too large or too small to be represented in the worksheet.
#REF! -- Appears when Excel encounters an invalid cell reference, such as when you delete a cell referred to in a formula or paste cells over the cells referred to in a formula.
#VALUE! -- Appears when you use the wrong type of argument or operator in a function, or when you call for a mathematical operation that refers to cells that contain text entries.

ENTERING A FUNCTION

When you enter a function, Excel always converts it to uppercase. It's a good idea to use lowercase when entering functions: If Excel doesn't convert it to uppercase, it means that it doesn't recognize your entry as a function (which means that you probably spelled it incorrectly).

EVALUATING PARTS OF A FORMULA

Excel 2002 comes with a new Evaluate Formula tool that helps you to evaluate parts of a formula (that is, calculate the result) and to trace the precedents of the parts formula. The capability to evaluate parts of a formula can prove very useful if you're tracking down errors in formulas.

To evaluate parts of a formula, follow these steps:

Click the cell containing the formula.
Choose the Tools, Formula Auditing, Evaluate Formula menu command. (You can also click the Evaluate Formula button on the Formula Auditing toolbar.) Excel displays the Evaluate Formula dialog box. In the Reference area, the cell reference appears. The Evaluation preview box displays the formula with the first expression or cell reference underlined.
To show the value of the underlined expression, click Evaluate. Excel italicizes the result of the expression.
Repeat Step 3 as many times as necessary to evaluate all expressions in the formula.
Use the Step In button to examine the formula that the underlined cell reference in the expression represents. Use the Step Out button to evaluate the current reference and return to the previous reference.
Click Close after you finish evaluating the formula.

Remember: The Step In button is not available is a cell reference lies in a different workbook.

A quick way to evaluate parts of a formula is to go into Edit mode (by double-clicking the cell or pressing F2), highlight the part of the formula that you want to evaluate, and press F9. Excel displays the result of the formula part. Press Esc to cancel. Do not press Enter; otherwise, Excel permanently replaces the part of the formula with the resulting value.

LET A WIZARD CREATE YOUR FORMULA

Beginning with Excel 97, Excel ships with an add-in called Conditional Sum Wizard. Once you install this add-in, you can invoke the Wizard by selecting Tools, Conditional Sum.

You can specify various conditions for your summing, and the add-in creates the formula for you (always an array formula). The Conditional Sum Wizard add-in, although a handy tool, is not all that versatile. For example, you can combine multiple criteria using an And condition, but not an Or condition.

PASTING NAMES INTO A FORMULA

If your formula uses named cells or ranges, you can type the name in place of the address. A less error-prone approach is to choose the name from a list and have Excel insert the name for you automatically. You can do this in two ways:

Select the Insert, Name, Paste command (Alt, I, N, P). Excel displays its Paste Name dialog box with all of the names listed. Select the name and click OK.
Press F3. This command also displays the Paste Name dialog box.

AN EASY WAY TO ENTER FORMULAS

If you need assistance entering or editing formulas, you can use the Formula Palette. To access the Formula Palette, click the Edit Formula button (=) in the formula bar.

If the active cell is empty, you can enter a formula into the cell.
If the active cell already contains a formula, you can use the Formula Palette to edit the formula.

The Formula Palette displays the result of the formula as it is being entered. If you need to use a function in your formula, select it from the function list (which occupies the space normally used by the Name box on the left side of the formula bar).

EXCEL FORMULAS AND FORMATTING

When defining a formula that uses only operators, Excel picks up the number formatting of the cells that are referenced in the formula (with the exception of the Percent style number format). For example, if you add cell A2 to B3, as in =A2+B3, and cell B3 is formatted with the Currency Style format, the result will inherit this format and be displayed in its cell using the Currency Style. This is not true, however, when you define a formula that uses a built-in function. If you sum cells A2 and B3 with the SUM function, as in =SUM(A2:B3), the result appears in its cell using the normal General number, even if cell B3 uses the Currency Style format.

LETTING YOUR FORMULAS ALL HANG OUT

Every once in a while you may need to print the formulas in a worksheet instead of printing the calculated results of the formulas. You can check over the printout to make sure that you haven't done anything stupid (like replace a formula with a number or use the wrong cell references in a formula) before you distribute the worksheet companywide -- which can be really embarrassing.

First, you have to display the formulas, rather than their results, in the cells.

Choose Tools, Options on the menu bar.
Select the View tab.
Select the Formulas check box to put a check mark in it.
Click OK or press Enter.

After Excel displays the formulas in the worksheet you are ready to print it as you would any other report.

CONVERTING FORMULAS TO VALUES

Sometimes, you may want to convert a formula to its current value (in other words, remove the formula and leave only its result). To do so, follow these steps:

Select the cell that contains the formula. If you want to convert several formulas you can select a range.
Choose the Edit, Copy command (you can also press Ctrl+C or click the Copy button on the Standard toolbar).
Choose the Edit, Paste Special command.
In the Paste Special dialog box, select the Values option button.
Click OK.
Press Enter to cancel Copy mode.

Remember: The preceding procedure overwrites the formulas. If you want to put the current values of the formulas in a different (empty) area of the worksheet, select a different range before Step 3 in the preceding list.

EDITING FORMULAS

You can edit your formulas just like you can edit any other cell. You might need to edit a formula if you make some changes to your worksheet and need to adjust the formula to accommodate the changes. Or, the formula may return one of Excel's error values, in which case you'll need to edit the formula to correct the error.

The following are the four ways to get into cell-edit mode:

Double-click the cell, which enables you to edit the cell contents directly in the cell.
Press F2, which enables you to edit the cell contents directly in the cell.
Select the cell that you want to edit, and then click in the formula bar. This enables you to edit the cell contents in the formula bar.
Click the Edit Formula button in the Formula bar to access the Formula Palette.

While you're editing a formula, you can select multiple characters either by dragging the mouse over them or by holding down Shift while you use the direction keys.

You might have a lengthy formula that you can't seem to edit correctly -- and Excel won't let you enter it because of the error. In this case, you can convert the formula to text and tackle it again later. To convert a formula to text, just remove the initial equal sign (=). When you're ready to try again, insert the initial equal sign to convert the cell contents back to a formula.

ENTERING FORMULAS BY POINTING

The other method of entering a formula still involves some manual typing, but you can simply point to the cell references instead of entering them manually. For example, to enter the formula =A1+A2 into cell A3, follow these steps:

Move the cell pointer to cell A3.
Type an equal sign (=) to begin the formula. Notice that Excel displays Enter in the status bar.
Press the up arrow twice. As you press this key, notice that Excel displays a faint moving border around the cell and that the cell reference appears in cell A3 and in the formula bar. Also notice that Excel displays Point in the status bar.
Type a plus sign (+). The faint border disappears and Enter reappears in the status bar.
Press the up arrow one more time. A2 is added to the formula.
Press Enter to end the formula.

Pointing to cell addresses rather than entering them manually is usually more accurate and less tedious.

When you create a formula that refers to other cells, the cell that contains the formula has the same number format as the first cell it refers to.

FUSSING WITH FORMULAS

SPACES IN FORMULAS

Normally, you enter a formula without using any spaces. However, you can use spaces (and even line breaks) within your formulas. Doing so has no effect on the formula's result, but may make the formula easier to read. To enter a line break in a formula, press Alt+Enter.

THE FUNCTION OF FORMULAS

A formula is a special type of cell entry that returns a result: When you enter a formula into a cell, the cell displays the result of the formula. The formula itself appears in the formula bar (which is just below the toolbars at the top of the Excel window) when the cell is activated.

A formula begins with an equal sign (=) and can consist of any of the following elements:

Operators such as + (for addition) and * (for multiplication)
Cell references, including addresses such as B4 or C12, as well as named cells and ranges
Values and text
Worksheet functions (such as SUM)

You can enter a formula into a cell in three ways: manually (typing it in), by pointing to cell references, or with the assistance of the formula palette.

FUNCTION PROCEDURE BASICS

When you use the Paste Function dialog box to enter a function, the formula palette kicks in after you click OK. For built-in functions, the formula palette displays a description for each of the function's arguments. Unfortunately, it is not possible to provide such descriptions for your custom function arguments.

ENTERING FUNCTIONS USING THE FORMULA PALETTE

The Formula Palette makes it easy to enter a function and its arguments. Using this tool ensures that the function is spelled correctly and has the proper number of arguments in the correct order.

To enter a function using the Formula Palette, activate the cell that will contain the function and then use either of these two methods:

Select the Insert, Function command (or click the Paste Function button) and select the function from the Paste Function dialog box.
Click the Edit Formula icon (=) on the edit line and then select a function from the function list in the Name box. If the function does not appear on the list, select the More Functions option and choose the function from the Paste Function dialog box.

Excel displays the Formula Palette directly below the edit line (you can drag it to a new location if it's in your way). The Formula Palette prompts you for each argument of the function you selected. You can enter the arguments manually or (if they are cell references) point to them in the worksheet. The Formula Palette displays the result. When you've specified all of the required arguments, click OK.

You can use the Formula Palette to insert a function into an existing formula. Click the Edit Formula button (=) to bring up the Formula Palette. Then move the cursor to the location where you want to insert the function and choose the function from the function list.
You can use the Formula Palette to edit a function in an existing formula. Click the Edit Formula button (=) to bring up the Formula Palette. Then click the function in the formula. Use the Formula Palette to adjust the function's arguments.

ENTERING FUNCTIONS MANUALLY

If you're familiar with the function that you want to use, you may choose to simply type the function and its arguments into your formula. Often this is the most efficient method.

Remember: If you're using a function at the beginning of a formula, you must provide an initial equal sign (=).

WHY USE FUNCTIONS IN YOUR FORMULAS?

Excel provides more than 300 built-in functions that can make your formulas perform powerful feats and save you a great deal of time.

Functions do the following:

Simplify your formulas.
Enable formulas to perform calculations that are otherwise impossible.
Allow "conditional" execution of formulas -- giving them some rudimentary decision-making capability.
You can see a list of the available functions by clicking on the Edit Formula icon (=) and then on the Functions drop-down list in the formula bar.

Most, but not all, worksheet functions use one or more arguments, enclosed in parentheses. Think of an argument as a piece of information that clarifies what you want the function to do. For example, the following function (which rounds the number in cell A1 to two decimal places) uses two arguments:

=ROUND(A1,2)

STAND-ALONE FUNCTIONS

Although AND and OR are frequently used with the IF function, they can be used by themselves. The entry below results in the answer TRUE if both conditions are true, or FALSE if either condition is false:

=AND(B7>10,D7="Sales")

The next entry below results in the answer TRUE if either condition is true, or FALSE if both conditions are false:

=OR(B7>10,D7="Sales")

USING FUNCTIONS IN EXCEL

Instead of creating more complex formulas from scratch out of an intricate combination of these operations, you can find an Excel function to get the job done.

A function is a predefined formula that performs a particular type of computation. All you have to do to use a function is supply the values that the function uses when performing its calculations (in the parlance of the Spreadsheet Guru, such values are known as the arguments of the function). As with simple formulas, you can enter the arguments for most functions either as a numerical value (for example, 22 or -4.56) or, as is more common, as a cell reference (for example, B10) or as a cell range (for example, C3:F3).

Just as with a formula you build yourself, each function you use must start with an equal sign (=) so that Excel knows to enter the function as a formula rather than as text. Following the equal sign, you enter the name of the function (in uppercase or lowercase -- it doesn't matter, as long as you don't misspell the name). Following the name of the function, you enter the arguments required to perform the calculations. All function arguments are enclosed in a pair of parentheses. Here is an example of the SUM function:

If cells A2:E2 contain 5, 15, 30, 40, and 50, and the SUM function below was inserted into cell F2:
=SUM(A2:C2)
Then the SUM displayed in cell F2 would be 50 If you type the function directly in a cell, remember not to insert spaces between the equal sign, function name, and the arguments enclosed in parentheses. Some functions use more than one value when performing their designated calculations. When this is the case, you separate each function with a comma (not a space).
After you type the equal sign, function name, and the left parenthesis that marks the beginning of the arguments for the function, you can point to any cell or cell range you want to use as the first argument instead of typing the cell references. When the function uses more than one argument, you can point to the cells or cell ranges you want to use for the second argument right after you type, (comma) to complete the first argument.

After you finish entering the last argument, type a right parenthesis to mark the end of the argument list and then click the Enter box (or press Enter or an arrow key) to insert the function in the cell and have Excel calculate the answer.

VOILA! FUNCTIONS ENTERED AUTOMATICALLY!

Most of the time, you're on your own when it comes to inserting functions. However, at least two situations arise in which Excel will enter functions for you automatically.

When you click the AutoSum button on the Standard toolbar, Excel does a quick check of the selected cells and the surrounding cells. It then proposes a formula that uses the SUM function. If Excel guessed your intentions, just press Enter (or click the AutoSum button a second time) to accept the proposed formula(s).
When you select the Data, Subtotals command, Excel displays a dialog box that enables you to specify some options. Then, it proceeds to insert rows and columns and enter some formulas automatically. These formulas use the SUBTOTAL function.

GLOBAL REPLACE WARNING

Be careful with global search-and-replace operations; they can really mess up a worksheet in a hurry if you inadvertently replace values, parts of formulas, or characters in titles and headings that you hadn't intended to change. With this in mind, always follow one rule:

Never undertake a global search-and-replace operation on an unsaved worksheet.

Also, verify whether or not the "Find entire cells only" check box is selected before you begin. You can end up with a lot of unwanted replacements if you leave this check box unselected when you really only want to replace entire cell entries (rather than matching parts in cell entries).

If you do make a mess, choose the Edit, Undo Replace command (Ctrl+Z) to restore the worksheet.

GOAL SEEKING

Excel's goal-seeking feature lets you determine the value that a single input cell requires to produce a result that you want in a dependent (formula) cell. Here's the procedure:

Start with a workbook that uses formulas.
Select the Tools, Goal Seek command (Alt, T, G).
Complete the Goal Seek dialog box by specifying the formula cell to change, the value to change it to, and the cell to change.
Click OK, and Excel displays the solution.
Click OK to replace the original value with the found value; or click Cancel to restore your worksheet to the form that it was in before you issued the Tools, Goal Seek command (Alt, T, G).

Remember: Excel can't always find a value that produces the result you're looking for (sometimes a solution doesn't exist). In such a case, the Goal Seek status box informs you of that fact. If Excel reports that it can't find a solution, but you're pretty sure that one exists, try these options:

Change the current value of the changing cell to a value closer to the solution and then reissue the command.
Adjust the Maximum iterations settings in the Calculation panel of the Options dialog box (Tools, Options (Alt, T, O)). Increasing the number of iterations makes Excel try other possible solutions.
Double-check your logic and make sure that the formula cell does indeed depend on the specified changing cell.

THE GO TO FEATURE

Excel's Go To feature provides an easy method for moving directly to a distant cell in the worksheet. To use this feature, you display the Go To dialog box by selecting the [G]o To command on the [E]dit pull-down menu or by pressing Ctrl+G or the function key, F5. Then, in the Go To dialog box, you type the address of the cell you want to go to in the cell address in the Reference text box -- you can type the column letter or letters in upper- or lowercase letters.

USING A GRAPHIC FILE

A wide assortment of graphic files (sometimes known as electronic clip art) is available, and you can use these images in your work without violating any copyright restrictions. Excel can import most graphics files that can be placed on a worksheet's draw layer.

To add a graphic file to a worksheet, you can use either of the following options:

Choose the Insert, Picture, From File command to locate a particular file stored on disk.
Choose the Insert, Picture, Clip Art command to use the Microsoft Gallery of clip art.

After an image is placed on a worksheet, you can move and resize it. You can also use the tools on the Picture toolbar to manipulate the image. The Picture toolbar appears when you select a picture.

Remember: Depending on the type of image, resizing may or may not produce good results.

Using graphics in a worksheet can dramatically increase the size of your workbook, resulting in more memory usage and longer load and save times.

WHEN YOU NEED TO DITCH THE GRAPHICS

Here's something you'll want to know about the graphics you add to a worksheet: how to hide them. You see, adding graphics to a worksheet can appreciably slow down the screen response because Excel has to take the time to redraw each and every little picture in a document window whenever you scroll the view even slightly. To keep this sluggishness from driving you crazy, either hide the display of all the graphics (including charts) while you edit other things in the worksheet, or temporarily replace them with gray rectangles that continue to mark their places in the worksheet but don't take nearly as long to redraw.

To hide all the graphics or replace them with gray placeholders, choose the Tools, Options command (Alt, T, O) on the menu bar and then select the View tab. Click the Hide all radio button (Alt, D) under Objects to get rid of the display of all graphics entirely. Click the Show placeholders radio button (Alt, P) to temporarily replace the graphics with shaded rectangles. (This is the safest bet, because the placeholders give you a general idea of how changes to the cells of the worksheet impact the graphics.)

Before you print the worksheet, be sure that you redisplay the graphic objects: Open the Options dialog box, select the View tab, and then click the Show all radio button (Alt, A).

NIXING THE GRAPHICS

Here's something you'll want to know about the graphics you add to a worksheet: how to hide them. You see, adding graphics to a worksheet can appreciably slow down the screen response because Excel has to take the time to redraw each and every little picture in the document window whenever you scroll the view even slightly. To keep this sluggishness from driving you crazy, either hide the display of all the graphics (including charts) while you edit other things in the worksheet, or temporarily replace them with gray rectangles that continue to mark their places in the worksheet but don't take nearly as long to redraw.

To hide all the graphics or replace them with gray placeholders, choose the Tools, Options command on the menu bar and then select the View tab. Click the Hide All radio button under Objects to get rid of the display of all graphics entirely. Click the Show Placeholders radio button to temporarily replace your charts with shaded rectangles. Note that selecting the Show Placeholders option has no effect on graphics created with the tools on the Drawing toolbar or imported into the worksheet.

Before you print the worksheet, be sure that you redisplay the graphic objects: Open the Options dialog box, select the View tab, and then click the Show All radio button.

MAKING HEADWAY WITH HEADERS AND FOOTERS

Headers and footers are simply standard text that appears on every page of the report. The header prints in the top margin of the page, and the footer prints -- you guessed it -- in the bottom margin. Both are centered vertically in the margins. Unless you specify otherwise, Excel automatically adds a header that shows the name of the worksheet you are printing (as shown on its sheet tab) and a footer that shows the current page number.

If you don't want a header or footer printed in your report, you simply open the Header/Footer tab in the Page Setup dialog box and then select the (none) option at the very top of the Header and Footer drop-down list box.

FROM TOP TO TOE: HEADERS AND FOOTERS

EDITING AND FORMATTING HYPERTEXT LINKS

The contents of cells that contain hypertext links are formatted according to the settings contained in two built-in workbook styles: Hyperlink and Followed. The Hyperlink style is applied to all new hypertext links you set up in a worksheet that you haven't yet used. The Followed Hyperlink is applied to all hypertext links that you have already used. If you want to change the way used and unused hypertext appears in the workbook, you need to change the formatting used in Followed Hyperlink and Hyperlink styles respectively.

If you need to edit the contents of the cell containing a hypertext link, you must be careful that, while getting Excel into Edit mode so that you can change the text, you don't inadvertently follow the link. This means that under no circumstances can you click the cell with the hypertext link with the (primary) mouse button because that's the way you follow the hypertext link to your destination! The best way to get around this problem, if you're used to selecting cells by clicking them is to click on a cell next to the cell with the hypertext link in it. Move your cell pointer with the arrow keys and press F2. Make your changes and click or press Enter.

If you want to remove the cell entry along with the hypertext link from a cell, you choose Edit, Clear, All (Alt, E, A, A) on the menu bar. If you want to delete the hypertext link but leave the entry in the cell, you choose Edit, Clear, Hyperlinks (Alt, E, A, H) instead. Just don't try deleting a hypertext link by selecting the cell and pressing the Delete key: Doing this succeeds only in removing the entry from the cell without getting rid of the underlying hypertext link. Because pressing the Delete key merely removes the cell entry while leaving the hyperlink behind, if you click this seemingly empty cell, Excel will jump you to the hyperlink's destination.

IMPORTING OLD DIALOG SHEETS

If, for compatibility purposes, you need to insert an Excel 5 or Excel 95 dialog sheet (in other words, a custom dialog box) in later versions of Excel, you won't find the command to do so on the Insert menu. You can only add an Excel 5/95 dialog sheet by right-clicking on any Sheet tab and selecting Insert from the shortcut menu. Then, in the Insert dialog box, click the MS Excel 5.0 Dialog icon.

INTENT ON INDENTS

In Excel 2002, you can indent the entries in a cell selection by clicking the Increase Indent button on the Formatting toolbar. The Indent button is normally located immediately to the left of the Borders button, and it sports a picture of an arrow pushing the lines of text to the right. Each time you click this button, Excel indents the entries in the current cell selection to the right by one character width of the standard font.

SCROLLING WITH THE INTELLIMOUSE

Microsoft developed a type of mouse called the IntelliMouse, designed specifically for scrolling through Office documents and lists of stuff in Windows 95 and 98. On the IntelliMouse, in between the normal left and right mouse buttons, you find a little wheel.

In Excel, you can use this wheel instead of the scroll bars to move to a new part of a worksheet as follows:

Turn the wheel button up and down a notch to scroll up and down the rows of cells, three rows at a time. As you rotate the wheel down by notches, you scroll down the rows of the worksheet. As you rotate the wheel up by notches, you scroll up the rows of the worksheet.
Click the wheel button to scroll in any of the four directions (up, down, left, or right). When you click the wheel button, the mouse pointer changes to a four-headed arrow shape. To then scroll in a particular direction, simply move the mouse (without clicking any of the mouse buttons) in the direction in which you wish to scroll the worksheet. When the part of the worksheet with the cells you want to use appears on the screen, click one of the cells in that area to stop the scrolling and return the mouse pointer to its normal white-cross shape.

TYPE JUSTIFICATION

This command has nothing to do with the alignment options you may already be used to. Instead, the Edit, Fill, Justify (Alt, E, I, J) command is useful for rearranging text in cells so that if fits in a specified range.

For example, you may import a text file that has very long lines of text. You easily can justify this text so that it's displayed in narrower lines. This command works with text in a single column. It essentially redistributes the text in the cells so that it fits into a specified range. You can make the text either wider (so that it uses fewer rows) or narrower (so that it uses more rows).

Select the cells to be justified (all in one column) and then extend the selection to the right so that the selection is as wide as you want the end result to be. Choose the Edit, Fill, Justify command, and Excel redistributes the text.

Blank rows serve as paragraph markers. If the range you select isn't large enough to hold all the text, Excel warns you and allows you to continue or abort. Be careful, because justified text overwrites anything that gets in its way.

KEYBOARD SHORTCUT FOR FORMATTING CELLS

The keystroke shortcut -- Ctrl+1 -- that opens the Format Cells dialog box is one worth knowing. Many of you will be doing almost as much formatting as you do data entry in a worksheet. Just keep in mind that the shortcut is Ctrl plus the number 1 and not the function key F1. Further, you must use the 1 key on the top row of the regular typewriter keyboard, not the 1 located on your numeric keypad. Pressing Ctrl plus 1 on the numeric keypad doesn't work any better than pressing Ctrl+F1.

ASSIGNING KEYBOARD SHORTCUTS WITH CARE

When you create a new macro, you have the option of assigning it to a keyboard shortcut by typing the shortcut in the Keyboard Shortcut box on the Record Macro dialog box. When you do this, the macro runs when you press the Ctrl key and the specified lowercase key simultaneously. If you specify an uppercase letter for the key, you can run the macro by pressing Ctrl+Shift+the specified key.

Unfortunately, Excel does not stop you from creating shortcuts that override other predefined Excel shortcut keys. If you specify a shortcut key combination that matches a Microsoft Excel shortcut, your new shortcut overwrites it; each time you press the shortcut keys, your macro -- not the Microsoft key combination -- runs. For example, the Save command shortcut is Ctrl+S. If you create a macro with a shortcut key of S, your macro runs instead of the Save command when you press Ctrl+S.

Excel does use many of the available shortcut keys, so you are bound to overwrite one sooner or later. Keep in mind that if you use a particular Excel shortcut regularly, you do not want to create a macro shortcut that overwrites it.

CHANGING LINKS

If your workbook uses one or more formulas that contain links to other workbooks, you may need to change the source workbook for your external references. For example, you may have a worksheet that has links to a workbook named Preliminary Budget. Later, you get a finalized version named Final Budget.

To change the link source, follow these steps:

Select the Edit, Links command.
In the Links dialog box, select the source workbook that you want to change.
Click the Change Source button.
In the Change Links dialog box, select a new source file and then click OK.

After you select the file, all external reference formulas are updated.

CREATING LINKS

Setting up a link from one Windows application to another isn't difficult, although the process varies slightly from application to application. The following are the general steps to take:

Activate the window in the source application that contains the information that you want to copy.
Select the information by using the mouse or keyboard. If Excel is the source application, you can select a cell, range, or entire chart.
Select Edit, Copy (Alt, E, C) from the source application's menu. The source application copies the information to the Windows clipboard.
Activate the destination application. If it isn't open, you can start it without affecting the contents of the clipboard.
Move to the appropriate position in the destination application.
Select the appropriate command in the destination application to paste a link. The command varies, depending on the application. In Microsoft Office applications, the command is Edit, Paste Special (Alt, E, S).
A dialog box will probably appear, letting you specify the type of link that you want to create.

WORKING WITH A LOTUS 1-2-3 FILE

If you open a Lotus 1-2-3 file in Excel, be aware that Excel evaluates the database criteria ranges differently. This may affect the results obtained when using advanced filtering and database functions.

For example, in 1-2-3, a criteria such as "John" finds only rows with cells that contain the text "John." When you open a 1-2-3 file in Excel, the "transition formula evaluation" is in effect. If you don't change this setting, the criteria ranges will be evaluated as they are in 1-2-3.

But if you select Tools, Options, and clear the Transition formula evaluation check box (in the Transition tab of the Options dialog box), Excel evaluates the criteria range using its rules (which are different). For example, the "John" criteria finds any rows that contain cells with text beginning with "John"; this includes cells that contain "John," "John Smith," and "Johnson."

THE MACRO RECORDER

One way to get code into a VBA module is to record your actions using Excel's macro recorder.

As you'll see, recording macros is very useful, but it has its limitations. In fact, when you record a macro you almost always need to make some adjustments or enter some code manually.

This example shows how to record a macro that simply changes the page setup to Landscape orientation. If you want to try this, start with a blank workbook, and follow these steps:

Activate a worksheet in the workbook (any worksheet will do).
Select the Tools, Macro, Record New Macro command. Excel displays its Record Macro dialog box.
Click OK to accept the defaults. Excel automatically inserts a new VBA module into the project. From this point on, Excel converts your actions into VBA code. While recording, Excel displays the word Recording in the status bar and also displays a miniature floating toolbar that contains two toolbar buttons (Stop Recording and Relative Reference).
Select the File, Page Setup command. Excel displays its Page Setup dialog box.
Select the Landscape option, and click OK to close the dialog box.
Click the Stop Recording button on the miniature toolbar (or select Tools, Macro, Stop Recording). Excel stops recording your actions.

To take a look at the macro, activate the VBE (Alt+F11 is the easiest way), and locate the project in the Project Explorer window. Click the Modules node to expand it. Then click the Module1 item to display the code window (if the project already had a Module1, the new macro will be in Module2).

RECORDING A MACRO

If you find yourself doing the same thing over and over again, record a macro. The process of recording a macro is surprisingly straightforward:

Open the Record New Macro dialog box to turn on the macro recorder by choosing Tools, Macro, Record New Macro.
Name the macro using basically the same naming conventions as when naming a range of cells in Excel (that is, start the name with a letter and use underscore characters instead of spaces) and (optionally) assign it a keystroke.
Perform the sequence of actions you want recorded in the macro just as you normally do in Excel. You can choose commands from the pull-down or shortcut menus, click tools in the toolbars, or use shortcut keystrokes.
Turn off the macro recorder when you finish by clicking the Stop Recording button that appears in the tiny Stop Recording toolbar as soon as you start recording.
Perform the task anytime you want simply by double-clicking the macro name in the Macro dialog box (opened by choosing Tools, Macro, Macros or pressing Alt+F8) or by selecting the macro name and then choosing the Run button.

Alternately, if you bothered to assign a keystroke shortcut to the macro when you recorded it, you can run the macro by pressing the keystroke shortcut.

You'll be happy to know that, when recording your actions, Excel doesn't record mistakes (don't you wish everyone else was so forgiving?). In other words, if you're entering the heading "January" in the macro and you type Janaury by mistake and then correct your error, Excel does not record your pressing of the Backspace key to delete the characters "aury" and then typing uary. The only thing the macro records is the final, corrected result of January!

CUSTOM MENU FOR MACROS

To keep all the macro references in one location and prevent clutter on the existing Excel menus, you can create a custom menu for macros. To create a new menu, follow these steps:

Click Tools, Customize to open the Customize dialog box.
On the Customize tab select the New Menu option as the desired category. A New Menu option displays as the available command.
Click the New Menu option and drag it to the desired menu location.

After you add the menu, you can right-click it and change the name, which makes it ready to receive your macros.

When you name a menu option, you can also create a shortcut key that corresponds to the menu option. Similar to the shortcuts you create for macros, the menu option shortcut launches whatever command you assign to the menu option. Also, these shortcuts launch with the Alt key. To create a menu shortcut, you need to type an ampersand (&) before the character in the menu item name that corresponds to the shortcut key. For example, if you want Alt+T to launch the menu option "Determine Total," you place the & before the letter T: "Determine &Totals."

FLOATING THE EXCEL MENU BAR

Toolbars like the Standard and Formatting toolbars are not the only things that float in Excel. You can even float the menu bar containing all the pull-down menus (thus, the reason for the double bar that now appears at the very beginning of the menu bar when it's docked at the top of the screen right below the Excel window's title bar). When you select a menu on a floating menu bar, its commands may appear above the bar next to the menu's name rather than below the bar (as is normal), depending upon how much room there is between the floating bar and the bottom of the screen.

EXCEL'S RESIDENT MIND READER

RECORDING MISTAKE-FREE MACROS

You'll be happy to know that, when recording your actions for a macro, Excel doesn't record mistakes. (Don't you wish everyone could be so forgiving?) In other words, if you're entering the heading "January" in the macro and you type Janaury by mistake and then correct your error, Excel does not record your pressing of the Backspace key to delete the characters "aury" and then typing uary. The only thing the macro records is the final, corrected result of January!

KEEPING TRACK OF SAVED MACROS

Where Excel saves your macro depends on how you created the macro in the first place:

If you created the macro as part of the current workbook, Excel places the macro in a hidden module sheet (called something like Module1, Module2, and so on) that is added to the workbook.

To see the contents of the macro, choose Tools, Macro, Macros. Then select the macro in the "Macro name" text box, and then click the Edit button.

If you created the macro as part of the Personal Macro Workbook, Excel places the macro in a hidden module sheet (called something like Module1, Module2, and so on) in a hidden workbook called PERSONAL.XLS.

To see the contents of the macro, choose Windows, Unhide and then select Personal.xls in the "Unhide workbook" list box before you click OK or press Enter. When the Personal workbook is open, you need to choose Tools, Macro, Macros; select the macro in the "Macro name" text box; and then click the Edit button.

If you saved the macro as part of a new workbook, Excel puts the macro in a hidden module sheet (called something like Module1, Module2, and so on) that is added to a new workbook (and given a temporary filename like Book1, Book2, or whatever the next available number is).

To see the contents of the macro, choose Tools, Macro, Macros. Then select the macro in the "Macro name" text box, and then click the Edit button.

BUILD IN EASY MAINTENANCE

One of the cardinal rules of "spreadsheeting" is that things change. You often can save yourself lots of time by planning for the inevitable changes. You can do a few things to make your worksheets as modifiable as possible:

Avoid hard-coding values in formulas. For example, assume that you have formulas that calculate sales commissions by using a commission rate of 12.5 percent. Rather than use the value .125 in the formulas, enter it into a cell and use the cell reference. Or, create a named constant.
Use names whenever possible. Cell and range names make your formulas easier to read and more understandable. When the time comes to modify your formulas, you may be able to modify just the range to which a name refers.
Use simplified formulas. Beginning users sometimes create formulas that are more complicated than they need to be. As you gain more experience with Excel, be on the lookout for useful functions that can make your formulas simple and clear. Such formulas are easier to modify, when needed.
Use a flexible layout. Rather than try to cram everything into a single worksheet, use multiple worksheets. You'll find that this makes expanding much easier, should the need arise.
Use named styles. Using named styles makes obtaining consistent formatting much easier if you need to add new data to accommodate a change.
Keep it clean. Keeping your workbooks clean and free of extraneous information is also a good idea. For example, if the workbook has empty worksheets, remove them. If you created names that you no longer use, delete them. If you no longer need a range of cells that you used to perform a quick calculation, delete the range.

CREATING A MAP

To create a map, follow these steps:

Select the data to be mapped.
This must be one column of area names and at least one column of data. If the columns have descriptive headers, include these in the selection.
For Excel 97, choose Insert, Map (or click the Map button on the Standard toolbar). For Excel 2000 or later, choose Insert, Object, Microsoft Map.
Click and drag in the worksheet to specify the location and size of the map.
If two or more maps are possible (or if you've developed custom map templates), you get a dialog box that lets you select the desired map from this list.

Microsoft Map analyzes the area labels and generates the appropriate map.

Remember: Unlike charts, maps must be embedded in a worksheet (there are no separate "map sheets").

To order additional maps or data from MapInfo (the company that developed the mapping package fro Microsoft), you can contact the company directly. For information on how to do so, activate a map and click the Help, About command.

MINDING YOUR MOUSE MANNERS

Windows programs such as Excel use four basic mouse techniques to select and manipulate various objects in the program and workbook windows:

Clicking an object to select it: Positioning the pointer on something and then pressing and immediately releasing the primary mouse button (the left button unless, as a leftie, you've switched the buttons around).
Right-clicking an object to display its shortcut menu: Positioning the pointer on something and then pressing and immediately releasing the secondary mouse button (the right button unless, as a leftie, you've switched the buttons around).
Double-clicking an object to open it: Positioning the pointer on something and then pressing and immediately releasing the primary mouse button rapidly twice in a row.
Dragging an object to move or copy it: Positioning the pointer on something and then pressing and holding down the primary mouse button as you move the mouse in the direction you wish to drag the object. When you have positioned the object in the desired location on the screen, you then release the primary mouse button to place it.

When clicking an object to select it, you must make sure that the tip of the mouse pointer is touching the object that you want to select before you click. To avoid moving the pointer slightly before you click, grasp the sides of the mouse between your thumb (on one side) and your ring and little finger (on the other side), and then click the primary button with your index finger. If you run out of room on your desktop for moving the mouse, just pick up the mouse and reposition it on the desk (which does not move the pointer).

JUST NAME IT!

Name each worksheet that you use in a workbook with some intelligible English name like Invoice or Price List (by double-clicking the sheet tab, entering a new name on the tab, and then pressing Enter) rather than leaving them with their normally indecipherable names like Sheet1 and Sheet2. That way, you not only know which sheets in your workbook you've already used, but you also have a fair idea of what they contain and when you should select them.

CHANGING THE NUMBER FORMAT

Excel offers many different formats that you can use to make the numbers in your worksheet easier to read. Follow these steps:

Select the cells containing the numbers you want to change.
Click on Format, Cells, and then on the Number tab.
Click the category that describes the numbers in the cells selected.
To select the number of decimal places you want the numbers to display, double-click this area. Then type the number of decimal places.
To select the way you want negative numbers to appear, click one of the available styles.
Click OK to apply your changes.

CREATING A CUSTOM NUMBER FORMAT

When entering numbers into cells in Excel it may be wise to alert Excel to the format of the numbers you will be entering so the program understands how to deal with the numbers. Excel already supports a variety of number formats, such as Currency, Comma, Percent formats, Zip Code, and Phone number. However, there may be situations where another number format is necessary that Excel isn't equipped with.

If none of the predefined Excel number formats fits your bill, you can always resort to creating a custom number of your very own design. To create a custom number format:

Choose Custom as the [C]ategory in the Format Cells dialog box.
Select the number format code that is closest to what you want from the [T]ype list box.
If necessary, edit this number format code in the [T]ype text box.

There's only one problem with doing this: Excel wasn't lying when it called them number format codes. All you see in the [T]ype list box when Custom is the [C]ategory is a bunch of weird-looking codes composed of lots of #s, 0s, ?s, Ds, Ms, and Ys.

Rather than work yourself into a lather (or, heaven forbid, do something nerdy like trying to decipher this gibberish), make life easier by focusing your attention on the Sample area above the [T]ype text box as you select various formats from the [F]ormat Codes list box. There:

Excel shows how the value in the active cell of the selected range will look in the selected format.
As soon as you see what you like, go for it by clicking the OK button or pressing Enter.

In addition to choosing built-in formats by their number format codes, you can create your own custom codes by doing a little creative editing. Although you do have to use these junky number format codes (ugh) when performing this editing, you really don't have to be a rocket scientist to figure out how to do it.

HIDDEN CUSTOM NUMBER FORMATS

Instead, you might actually find in your worksheets a custom number format quite handy -- one that hides the display of any cell entry in the worksheet. (You can continue to see the entry on the formula bar when the cell pointer is in its cell.) You can then use your custom hidden format to temporarily hide cell ranges that you don't want printed out or visible in your worksheet on-screen.

To build the custom hidden number format:

Open the Format Cells dialog box (Ctrl+1).
Click Custom in the [C]ategory list box in the Number tab of the Format Cells dialog box.
Select General in the Type list box and replace this text with three semicolons. The custom hidden number format consists of just three semicolons in a row (with no spaces between them, ex. ;;;). Odd as this looks, these three semicolons tell Excel that it should display nothing for positive values, nothing for negative values, and nothing for anything else in the cell.
After entering the codes in the [T]ype text box, click the OK button or press Enter to apply the custom format to the current cell selection. Custom formats are saved as part of the worksheet the next time you save the workbook file. (Remember, don't neglect that Save tool on the Standard toolbar!)

When you apply this hidden format to cells, the cell display disappears in the worksheet (the contents still show up on the formula bar when you select the cell). To make hidden entries reappear:

Select the hidden cells.
Open the Format Cells dialog box.
Select one of the other visible number formats (the General format, for example) and apply it to cells.

Note that the custom number formats that you create are added to the bottom of the [T]ype list box. This means that when you next open the Format Cells dialog box and select Custom in the [C]ategory list box of the Number tab, you may have to scroll through the [T]ype list box all the way to the bottom before you can locate and select the number format codes that you're responsible for adding.

WHERE DID THOSE NUMBER FORMATS COME FROM?

Excel may create custom number formats without you realizing it. When you use the Increase Decimal or Decrease Decimal button on the Formatting toolbar, new number formats are created that appear on the Number tab of the Format Cells dialog box. (To access this dialog box, click Cells on the Format menu.) For example, if you click the Increase Decimal button five times, the following custom number formats are created:

0.0
0.000
0.0000
0.000000

A format string for two decimal places is not created because that format string is built-in.

A NUMBER OF SPECIAL FORMATS

Excel contains a nifty category of number formats called Special. The Special category contains the following four number formats that may interest you:

Zip Code: Retains any leading zeros in the value (important for zip codes and of absolutely no importance in arithmetic computations). Example: 00123.
Zip Code + 4: Automatically separates the last four digits from the first five digits and retains any leading zeros. Example: 00123-5555.
Phone Number: Automatically encloses the first three digits of the number in parentheses and separates the last four digits from the previous three with a dash. Example: (999) 555-1111.
Social Security Number: Automatically puts dashes in the value to separate its digits into groups of three, two, and four. Example: 666-00-9999.

These Special number formats really come in handy when creating databases in Excel, which often deal with stuff like zip codes, telephone numbers, and sometimes even Social Security numbers.

INTRODUCING THE OFFICE ASSISTANT

Excel comes with an interactive help tool called the Office Assistant. The Office Assistant takes the form of an animated character (you can choose from eight characters) that floats over the Excel screen.

If the Office Assistant is not visible, click the Microsoft Excel Help button in the Standard toolbar. You can drag the assistant to any location on the screen.

OPENING EXCEL WHILE BROWSING

If you become a regular Internet or intranet user, you can open an Excel workbook (saved in HTML format) using the File, Open command of Microsoft Internet Explorer or Netscape Navigator. The file opens without switching you back to Excel and Excel need not be open for this to work. Excel toolbars and the Excel menu enable you to work with your workbook, make changes, and save it as an Excel file, all the time within the Explorer environment.

ADDING DATA TO AN OUTLINE

You may need to add additional rows or columns to an outline. In some cases, you may be able to insert new rows or columns without disturbing the outline, and the new rows or columns become part of the outline. In other cases, you'll find that the new row or column is not part of the outline. If you create the outline automatically, just select Data, Group and Outline, Auto Outline again. Excel makes you verify that you want to modify the existing outline. If you create the outline manually, you need to make the adjustments manually, as well.

CENTERING PRINTED OUTPUT

Normally, Excel prints a page at the top and left margins. If you want the output to be centered vertically or horizontally on the page, follow these steps:

Choose the File, Page Setup command.
Click the Margins tab of the Page Setup dialog box that appears.
Select the appropriate check boxes in the Center on Page area: Horizontally or Vertically.
Click OK to close the Page Setup dialog box.

MANUAL PAGE BREAKS

Excel handles page breaks automatically. After you print or preview your worksheet, it displays dashed lines to indicate where page breaks will occur. Sometimes, you want to force a page break -- either a vertical or a horizontal one.

To insert a horizontal manual page break, follow these steps:

Move the cell pointer to the cell that will begin the new page, but make sure that it's in column A. (Otherwise, you'll insert a vertical page break and a horizontal page break.)
Choose Insert, Page Break. The page break is inserted in the row above the cell pointer.

To insert a vertical page break, follow these steps:

Move the cell pointer to the cell that will begin the new page, but make sure that it's in row 1. (Otherwise, you'll insert a horizontal page break and a vertical page break.)
Select Insert, Page Break. The page break is inserted in the column to the left of the cell pointer.

INSERTING MANUAL PAGE BREAKS

To override Excel's automatic handling of page breaks, you must insert one or more page breaks manually. To insert a horizontal page break, follow these steps:

Move the cell pointer to the row that you want to begin the new page, but make sure that it's in column A. (Otherwise, you insert a vertical page break and a horizontal page break.)
Choose Insert, Page Break from the menu bar to create the page break.
Excel inserts the page break in the row above the cell pointer.

To insert a vertical page break, follow these steps:

Move the cell pointer to the column that you want to begin the new page, but make sure that it's in row 1. (Otherwise, you insert a horizontal page break and a vertical page break).
Choose Insert, Page Break to create the page break.
Excel inserts the page break in the column to the left of the cell pointer.

In manipulating page breaks, using the Zoom feature to zoom out on the worksheet is often helpful. Doing so gives you a bird's-eye view of the worksheet, and you can see more pages at once.

FREEZING PANES

Window panes are great for viewing different parts of the same worksheet that normally can't be seen together. You can also use window panes to freeze headings in the top rows and first columns so that the headings stay in view at all times, no matter how you scroll through the worksheet. Frozen headings are especially helpful when you work with a table that contains information that extends beyond the rows and columns shown on-screen.

To create and freeze window panes, position the pointer in the cell after the one you want to freeze. For example if you want all the content in row 1 to be frozen, place your pointer in a cell in row 2. Choose [W]indow, [F]reeze Panes on the menu bar. Note, if you want to freeze the panes in column C, row 5, then place your pointer in cell D6.

DEFROSTING FROZEN PANES

Excel gives you an Unfreeze command to get rid of any kind of frozen panes. But if you attempt to reverse recent actions by performing a series of Undos from the toolbar button, Excel will skip over any freeze-related commands, and you can undo any actions taken before them.

SPLITTING PANES

Splitting a window into two or four panes lets you view multiple parts of the same worksheet.

The Window, Split command splits the active worksheet into two or four separate panes.
The split occurs at the location of the cell pointer.
You can use the mouse to drag the pane and resize it.
To remove split panes, choose Window, Remove Split.

NESTING PARENTHESES

You don't have to worry too much about nesting parentheses in a formula, even if you don't pair them properly so that you have a right parenthesis for every left parenthesis in the formula. If you don't include a right parenthesis for every left one, Excel displays an alert dialog box that suggests the correction that needs to be made to balance the pairs. If you agree with the program's suggested correction, you simply select the [Y]es button.

IT'S PLAYBACK TIME

After you turn off the macro recorder, you are ready to test the macro. If your macro enters new text or deletes some text as part of its actions, be careful where you test it out. Be sure that the cell pointer is in a part of the worksheet where existing cell entries won't be trashed when you play back the macro. When you're ready to test the macro, you can do any of the following:

The easiest way to play back a macro is to press the shortcut keystrokes, assuming that you assigned a shortcut to your macro.
You can choose Tools, Macro, Macros on the menu bar or press Alt+F8 and then double-click the macro's name in the Macro dialog box or select the name and click the Run button (this method works for any macro whether or not it has shortcut keys assigned).

If, when you play back the macro, you find it running amok in your worksheet, press Esc to stop the macro prematurely. Excel displays a macro error dialog box indicating the point at which the macro was interrupted. Click the Halt button in this dialog box to shut the macro down.

MODIFYING THE PLOT AREA

The Plot Area is the part of the Excel chart that contains the actual chart. The Format Plot Area dialog box has only one tab: Patterns. This tab enables you to change the color and pattern of the Plot Area and adjust its borders.

When you select a chart element, you'll find that many of the toolbar buttons that you normally use for worksheet formatting also work with the selected chart element. For example, if you select the chart's Plot Area, you can change its color by using the Fill Color tool on the Formatting toolbar. If you select an element that contains text, you can use the Font Color tool to change the color of the text.

You can resize and reposition the Plot Area by selecting it and then dragging its borders.

PREVIEWING WEB PAGES WITH YOUR BROWSER

To get an idea of how the worksheet data and charts will look should you save all or part of its workbook in a Web page, you can use the File, Web Page Preview command on the menu bar. When you choose this command, Excel 2000 launches your Windows Web browser showing all the worksheet data and charts in the workbook more or less as they will appear in the final Web page. If the workbook you're previewing contains data and charts placed on different sheets and you preview the Web page, you can use the sheet tabs at the bottom of the browser's window to browse the information on the individual sheets.

CLEARING THE PRINT AREA

Excel includes a special printing feature called Print Area. You can use the File, Print Area, Set Print Area command to define any cell selection on a worksheet as the Print Area. After the Print Area is defined, Excel then prints this cell selection anytime you print the worksheet. Whenever you fool with the Print Area, you need to keep in mind that once defined, its cell range is the only one you can print until you clear the Print Area.

To clear the Print Area (and therefore go back to the printing defaults), you just have to select File, Print Area, Clear Print Area on the menu bar.

You can also define and clear the Print Area from the Sheet tab of the Page Setup dialog box. To define the Print Area from this dialog box, insert the cursor in the Print Area text box on the Sheet tab and then select the cell range or ranges in the worksheet. To clear the Print Area from this dialog box, select the cell addresses in the Print Area text box and press the Delete key.

SETTING A PRINT AREA

The Print Area text box shows you the cell range of the current print area that you selected with the File, Print Area, Set Print Area command on the menu bar. Use this text box to make changes to the range of cells that you want to print. To change the print area range, select this text box, and then drag through the cell range in the worksheet or type in the cell references or range names. Separate individual cell ranges with a comma (as in A1:G72, K50:M75) when designating nonadjacent areas. If, in selecting the cell range, you find that you need to, you can reduce the Page Setup dialog box to just the Print area text box by clicking its Collapse/Expand dialog button.

Use the Print [a]rea option when your workbook contains a section that you routinely need to print so that you don't have to keep selecting the range and then choosing the Selectio[n] radio button in the Print dialog box every blasted time you print it.

PRINT PREVIEW

Use the Print Preview feature before you print any worksheet, section of worksheet, or entire workbook. Because of the peculiarities in paging worksheet data, check the page breaks for any report that requires more than one page. Print Preview mode not only shows you exactly how the worksheet data will be paged when printed but also enables you to modify the margins, change the page settings, and even print the report when everything looks okay.

To switch to Print Preview mode, click the Print Preview button on the Standard toolbar (the one with the magnifying glass on the page, next to the Print button) or choose [F]ile, Print Pre[v]iew on the menu bar. Excel displays all the information on the first page of the report in a separate window with its own toolbar.

SCALING YOUR PRINTER OUTPUT

Sometimes you may need your printed output to fit exactly on one page. Or you may just want the worksheet to print smaller or larger than normal. Excel lets you scale the output from 10 percent to 400 percent of normal.

To scale your printed output, follow these steps:

Select File, Page Setup.
Click the Page tab of the Page Setup dialog box.
Enter a scaling factor manually in the % Normal Size box, or let Excel scale the output automatically to fit on the desired number of pages.

If you want to return to normal scaling, type 100 in the box labeled % Normal Size.

Remember: Scaling printed output does not affect the zoom factor of the worksheet.

USING A PRINTER YOU DON'T HAVE

If you need to print a report on a printer that is not currently attached to your PC or which is unavailable on your network, you can send the report to a print file. You will need to make certain that you have installed the correct printer drivers for the printer, which you do by using the Add Printer icon in the Printers folder (click the Windows Start button and choose Settings, Printers to open this folder).

When you check the Print file check box, Excel stores the output in a file, prompting you for a filename before printing. The resulting file will not be a standard text file. Rather, it will include all the printer codes that are required to print your worksheet. You can save the file and then send this file to your printer at a later time. This technique is also useful if you want to e-mail a print file to someone so that they can print the report on their printer -- even if they don't have Excel on their PC.

To actually use the print file, you need to copy the file to the printer at the command prompt. The following is an example of the command you would use (assuming that you saved the file to drive A: using the name Report.prn and that your printer is connected directly to your PC on printer port 1):

Copy A:\REPORT.PRN /B PRN

PRINTING COLORS IN BLACK AND WHITE

If you have a colorful worksheet, but your printer is stuck in a monochrome world, you may discover that the worksheet colors don't translate well to black and white. In this case, you need to instruct Excel to ignore the colors when printing:

Select File, Page Setup.
Click the Sheet tab of the Page Setup dialog box.
Place a check mark next to the "Black and white" check box.

PRINTING YOUR SPREADSHEET

Clicking the Print button on the Standard toolbar is a quick way to print the current worksheet using the default settings. If you've changed any of the default print settings, Excel uses the settings you put in; otherwise, it uses these default settings:

Prints the active worksheet (or all selected worksheets), including any embedded charts or drawing objects.
Prints one copy.
Prints the entire worksheet.
Prints in portrait mode.
Doesn't scale the printed output.
Uses 1-inch margins for the top and bottom and 3/4-inch margins for the left and right.
Doesn't print a header or footer.
For wide worksheets that span multiple pages, it prints down and then over.

PRETTY-UP THE PRINTOUT

You can use the "Center on page" options in the Margins tab of the Page Setup dialog box to center a selection of data (that takes up less than a full page) between the left and right margin. Select the Vertically checkbox to center the data between the top and bottom margins.

AUDIBLE PROOFREADING

Excel 2002 comes with a new text-to-speech feature that you can use as an aid in proofreading your spreadsheet data. This tool is useful for the sight impaired but can also help if you're entering a lot of data manually -- for example, from a printed sheet. While you're entering the data into the range, you can look at the printed sheet while Excel confirms each entry audibly. Or, if you've already entered the data, you can check the printed sheet while Excel speaks out each entry.

To use the text-to-speech feature, you need to display the Text to Speech toolbar. Follow these steps to display the toolbar and use its options:

Choose the Tools, Speech, Show Text To Speech Toolbar menu command. (You can also choose the View, Toolbars, Text To Speech menu command.) Excel displays the Text To Speech Toolbar.
If you want Excel to speak a range, select the range and click the By Rows or By Columns button to make Excel speak the cells across rows or down columns.
Click the Speak Cells button for Excel to start speaking the cells. Click the Stop Speaking button for Excel to stop speaking the cells.
If you want Excel to speak the cell every time that you enter data into a cell, click the Speak On Enter button. Click the button again to cancel the feature.

Remember: Your PC must have the appropriate equipment for the text-to-speech feature to work. At minimum, you must have a sound card with speakers or a pair of headphones.

The Speak On Enter tool persists across Excel sessions -- that is, if you enable it during one session and shut down Excel, it's still enabled the next time that you start Excel.

PUTTING ON THE PROTECTION

After you've more or less finalized a worksheet by checking out its formulas and proofing its text, you often want to guard against any unplanned changes by protecting the document.

Each cell in the worksheet can be locked or unlocked. By default, Excel locks all the cells in a worksheet so that, when you follow these steps, Excel locks the whole thing up tighter than a drum.

Choose Tools, Protection, Protect Sheet on the menu bar. Excel opens the Protect Sheet dialog box where Contents, Objects, and Scenarios check boxes are all selected.
If you want to assign a password that must be supplied before you can remove the protection from the worksheet, type the password in the Password (optional) text box.
If you typed a password in the Password (optional) text box, Excel opens the Confirm Password dialog box. Reenter the password in the Reenter Password to Proceed text box exactly as you typed it into the Password (optional) text box in the Protect Sheet dialog box and click OK or press Enter.

REFERRING TO R1C1 REFERENCE STYLE

Most worksheets that you'll run across use the normal A1 style cell address system (because this is the program's default setting for cell addressing and most people don't even know the R1C1 system exists). Suppose that someone in the know switches to the R1C1 system and saves the Excel file with it turned on. When you open it, all cell references in the formulas of its worksheets use that system and the Column header contains numbers just like the Row header. You can always switch back to the normal cell reference style by choosing Tools, Options and then clicking the R1C1 Reference Style check box on the General tab (to remove its checkmark). This check box is located at the top of the View tab in the Options dialog box. After you choose OK, all cell references in the formulas change back to the A1 style reference system and the Column header once again uses letters.

ABSOLUTE REFERENCES

You need to be able to distinguish between relative and absolute cell references. By default, Excel creates relative cell references in formulas except when the formula includes cells in different worksheets or workbooks. The distinction becomes apparent when you copy a formula to another cell.

Sometimes you want a cell reference to be copied verbatim. Let's say the formula in cell D2 is as follows:

=(B2*C2)*$B$6

Notice that the reference to cell B6 has dollar signs preceding the column letter and the row number. These dollar signs indicate to Excel that you want to use an absolute cell reference. When you copy this formula to the two cells below, Excel generates the following formulas:

* Cell D3: =(B3*C3)*$B$6
* Cell D4: =(B4*C4)*$B$6

In this case, the relative cell references were changed, but the reference to cell B6 wasn't changed, because it's an absolute reference.

RELATIVE REFERENCES

Let's say the following formula, which uses the default relative references, appears in cell D2:

=B2*C2

Now when you copy this formula to the two cells below it, Excel doesn't produce an exact copy of the formula; rather, it generates these formulas:

* Cell D3: =B3*C3
* Cell D4: =B4*C4

Excel adjusts the cell references to refer to the cells that are relative to the new formula. Think of it like this: The original formula contained instructions to multiply the value two cells to the left by the value one cell to the left. When you copy the cell, these instructions get copied, not the actual contents of the cell. Usually, this is exactly what you want. You certainly don't want to copy the formula verbatim; if you did, the new formulas would produce the same value as the original formula.

When you cut and paste a formula (move it to another location), the cell references in the formula aren't adjusted. Again, this is what you usually want to happen. When you move a formula, you generally want it to continue to refer to the original cells.

OPENING REMARKS

Click the History button to open workbook files saved in the Recent folder (located inside the Office folder within the Microsoft folder).
Click the My Documents button to open workbook files saved in the Personal folder inside the Windows folder. (In fact, on some computers, the My Documents button in the Excel 2000 Open dialog box appears as the Personal button.)
Click the Desktop folder to open workbook files saved directly on the desktop of your computer.
Click the Favorites button to open workbook files saved in the Favorites folder inside the Windows folder.
Click the Web folders button to open workbook files (especially those saved as Web pages) saved in any Web folders that are created on your hard disk.

CHANGING ROW HEIGHT

Row height is measured in points (a standard unit of measurement in the printing trade; 72 points equal one inch). Changing the row height is useful for spacing out rows; it's better to change the row height than to insert empty rows between rows of data. If you want, you can select several rows before using the following techniques to set row height:

Drag the lower row border with the mouse until the row is the desired height.
Choose the Format, Row, Height command and enter a value (in points) in the Row Height dialog box.
Double-click the bottom border of a row to automatically set the row height to the tallest entry in the row. You also can use the Format, Row, AutoFit command for this.

Remember: The default row height depends on the font defined in the Normal style. Excel adjusts row heights automatically to accommodate the tallest font in the row. So, if you change the font size of a cell to, say, 20 points, Excel makes the row taller so that the entire test is visible.

FREEZING ROW OR COLUMN TITLES

Many worksheets (such as budgets) are set up with row and column headings. As you scroll through such a worksheet, you can very easily get lost after the row and column headings scroll out of view. Excel provides a handy solution to alleviate this problem: freezing rows and/or columns.

To freeze entire rows or columns, follow these steps:

Move the cell pointer to the cell below the row that you want to freeze and to the right of the column that you want to freeze. To freeze row 1 and column A, for example, move the cell pointer to cell B2.
Choose Window, Freeze Panes from the menu bar.

Excel inserts dark lines to indicate the frozen rows and columns. These frozen rows and columns remain visible as you scroll throughout the worksheet.

To remove the frozen rows or columns, choose the Windows, Unfreeze Panes command from the menu bar.

ROW AND COLUMN HEADINGS AS "NAMES"

If you like the idea of using meaningful names in your formulas -- but don't like the idea of going through the trouble of creating the names -- you'll like the Excel feature that lets you use "names" without actually defining them.

Imagine a typical table with row and column headers. Excel lets you use the row and column headers as names in your formulas (and you don't have to define the names). For example, suppose your sales table has column headers for names of months, and your row headers are days of the week. To refer to the cell that holds the February sales for Monday, use the following formula:

=February Monday

In other words, the formula returns the cell that intersects the February column and the Monday row. You can also use this technique with functions. Here's a formula that returns the total sales for March:

=SUM(March)

Excel handles all the details for you. If you change a row or column heading, all the formulas are changed automatically to use the new label.

DELETING ENTIRE ROWS AND COLUMNS

In certain circumstances you may want to delete entire rows or columns from your worksheet. If you delete row(s), the rows below the deleted row(s) shift upward to fill the gap. If you delete a column(s), the columns to the right of the deleted column(s) shift to the left to fill the gap.

To delete entire rows or columns, follow these steps:

Select a cell or range in the row(s) or column(s) that you want to delete.
Choose the Edit, Delete menu command, right-click the selected row or column and choose Delete from the shortcut menu, or Press Ctrl+- (the minus sign). Excel displays the Delete dialog box.
Select the Entire Row or Entire Column radio button.
Click OK.

You can also delete entire rows or columns by selecting these rows or columns first. Then use any of the methods in Step 2 to select the Delete command. Excel deletes the rows or columns without displaying the Delete dialog box.

HIDING ROWS OR COLUMNS

Although you can hide worksheet columns and rows by just adjusting them out of existence, Excel does offer an easier method of hiding them via the F[o]rmat pull-down menu or column or row shortcut menus. Suppose that you need to hide column B in the worksheet because it contains some irrelevant or sensitive information that you don't want printed. To hide this column, follow these steps:

Click anywhere in column B to select the column.
Click F[o]rmat, [C]olumn, [H]ide on the menu bar.

The same works for hiding rows. To unhide the column:

Position the mouse pointer on column letter A in the frame and drag the pointer right to select both columns A and C. You must drag from A to C to include hidden column B as part of the column selection -- don't click with the Ctrl key or you won't get B.
Choose F[o]rmat, [C]olumn, [U]nhide on the menu bar.

INSERT A NEW ROW OR COLUMN

You can add a row or column to your worksheet when you want to add additional data. To insert a row, follow these steps:

Click the row number of the row just below where you want the new row to appear. Excel highlights the row.
Click Insert, Rows. The new row appears and all the rows that follow shift downward.

To insert a column, follow these steps:

Click the column letter of the column to the left of where you want the new column to appear. Excel highlights the column.
Click Insert, Columns. The new column appears and all the columns that follow shift to the right.

You'll be happy to know that Excel automatically updates any formulas affected by the insertion.

PRINTING ROW AND COLUMN HEADINGS

If you want to make it easy to identify specific cell addresses from a printout, you'll want to print the row and column headings. To do so, follow these steps:

Select the File, Page Setup command.
Click the Sheet tab of the Page Setup dialog box.
Place a check mark in the Row and column headings check box.

MULTIPLE AND MOVEABLE QUERIES

A single workbook can hold as many external data ranges as you need. Excel gives each query a unique name, and you can work with each query independently. Excel automatically keeps track of the query that produces each external data range.

After performing a query, you may want to copy or move the external data range, which you can do by using the normal copy, cut, and paste techniques. However, make sure that you copy or cut the entire external data range -- otherwise, the underlying query is not copied, and the copied data cannot be refreshed.

STRAIGHTENING OUT THE SHEETS

Spreadsheet programs like Excel 2002 refer to their electronic sheets as worksheets rather than spreadsheets. And, although it is perfectly acceptable (even preferable) to call one of its electronic sheets a worksheet, you never, never refer to Excel as a worksheet program -- it's always called a spreadsheet program. So you can think of Excel as a spreadsheet program that produces worksheets, but not as a worksheet program that produces spreadsheets.

STAMPING OUT SPELLING ERRORS

Excel has a built-in spell checker that can catch and get rid of all those embarrassing little spelling errors. To check the spelling in a worksheet, choose Tools, Spelling on the menu bar, click the Spelling button (the one with a check mark under ABC) on the Standard toolbar, or press F7.

Any way you do it, Excel begins checking the spelling of all text entries in the worksheet. When the program comes across an unknown word, it displays the Spelling dialog box.

Excel suggests replacements for the unknown word, with a likely replacement in the Suggestions list box appearing in the "Change to" list box of the Spelling dialog box. If that replacement is incorrect, you can scroll through the Suggestions list box and click the correct replacement. If you think that the word is fine as-is, then click the Ignore button (or the Ignore All button if you don't want the spell checker to bother you with this word again).

If you want to add the unknown word (such as your name) to a custom dictionary so that it won't be flagged when you check the spelling in the worksheet later on, click the Add button.

Keep in mind that you can check the spelling of just a particular group of entries by selecting the cells before you choose Tools, Spelling on the menu bar, click the Spelling button on the Standard toolbar, or press F7.

MERGING STYLES FROM OTHER WORKBOOKS

You may create one or more styles that you use frequently. Although you could go through the motions and create these styles for every new workbook, a better approach is to merge the styles from a workbook in which you previously created them.

To merge styles from another workbook, open both the workbook that contains the styles that you want to merge and the workbook into which you want to merge styles. From the workbook into which you want to merge styles, choose Format, Style and click the Merge button. Excel displays a list of all open workbooks. Select the workbook that contains the styles you want to merge and click OK. Excel copies styles from the workbook that you selected into the active workbook.

When you're merging styles, colors are based on the palette stored with the workbook in which you use the style. Therefore, if the two workbooks involved in the merge use different color palettes, the colors used in the styles may not look the same in each workbook.

VIEWING THE TOP TEN RECORDS

Excel contains an AutoFilter option called Top 10. You can use this option on a numerical field to show only a certain number of records (like the ones with the ten highest or lowest values in that field or those in the ten highest or lowest percent in that field). To use the Top 10 option to filter a database, follow these steps:

Choose Data, Filter, AutoFilter (Alt, D, F, F) on the menu bar.
Click the drop-down list button in the field that you want to use in filtering the database records.
Select the Top 10 option in the drop-down list box. Excel opens the Top 10 AutoFilter dialog box. By default, the Top 10 AutoFilter chooses to show the top ten items in the selected field. You can, however, change these default settings before filtering the database.
To show only the bottom ten records, change Top to Bottom in the pop-up list box.
To show more than the top or bottom ten records, enter the new value in the text box that currently holds 10 or select a new value using the spinner buttons.
To show those records that fall into the top or bottom 10 (or whatever) percent, change Items to Percent in its pop-up list box.
Click OK or press Enter to filter the database using your Top 10 settings.

THE ROLE OF SCENARIO MANAGER

Excel's Scenario Manager feature makes automating your what-if models easy. You can store different sets of input values (known as changing cells) for any number of variables and give a name to each set. You can then select a set of values by name, and Excel displays the worksheet by using those values. You can generate a summary report that shows the effect of various combinations of values on any number of result cells. The summary report can be an outline or a PivotTable.

SAVING SEARCH CRITERIA

You can save the search criteria that you've gone to all of that trouble to set up so that you can reuse it later on. To save the search criteria listed in the Find files that match these criteria list box of the Advanced Find dialog box, select the Save Search button, type in a descriptive name for your search ("All files on C: with keywords 'For My Eyes Only,'" or something like that), and click OK. To reuse the search criteria later to find files in the Open dialog box, click the Commands and Settings button on the Open dialog box toolbar, select the Saved Searches command and select the name of the search from its cascading menu.

SAVE SEARCH CRITERIA

You can save the search criteria that you've gone to all the trouble to set up so that you can reuse it later on. To save the search criteria listed in the "Find files that match these criteria" dialog box, select the Save Search button, type in a descriptive name for your search ("All files on C: with keywords 'For Eyes Only,'" or something like that), and click OK. To reuse the search criteria later to find files in the Open dialog box, click the Open Search button in the Find dialog box and select the name of the search in the Open Search dialog box.

SEARCHING FOR DATA IN EXCEL

If your Excel worksheet contains lots of data, you may find it difficult to locate a particular piece of information. A quick way to do so is to let Excel do it for you.

To locate a particular value or sequence of text, follow these steps:

Select the area of the worksheet that you want to search. If you want to search the entire worksheet, just select a single cell (any cell will do).
Choose the Edit, Find command or press Ctrl+F.
Excel displays its Find dialog box.
In the Find what box, enter the characters to search for. (If you want to make your search case sensitive, put a check mark in the Match case check box.)
In the Look in box, specify what to look in: Formulas, Values, or Comments.
Click the Find Next button.
Excel selects the cell that contains what you're looking for.
If there is more than one occurrence, repeat Step 5 until you find the cell that you're looking for.
Click the Close button to end.

For approximate searches, use wildcard characters. An asterisk represents any group of characters in the specified position, and a question mark represents any single character in the specified position. For example, w*h represents all text that begins with w and ends with h. Similarly, b?n matches three-letter words such as bin, bun, and ban.

SHORT SHEET NAME

Although Excel allows up to 31 characters (including spaces) for a sheet name, you'll want to keep your sheet names much briefer for two reasons:

First, the longer the name, the longer the sheet tab. And the longer the sheet tab, the fewer tabs that can be displayed. And the fewer the tabs, the more tab scrolling you'll have to do to select the sheets you want to work with.
Second, should you start creating formulas that use cells in different worksheets, Excel uses the sheet name as part of the cell reference in the formula. (How else could Excel keep straight the value in C1 on Sheet1 from the value in cell C1 on Sheet2?!) Therefore, if your sheet names are long, you'll end up with unwieldy formulas in the cells and on the Formula bar even when you're dealing with simple formulas that refer only to cells in a couple different worksheets.

So remember: As a general rule, the fewer characters in a sheet name, the better.

EDIT SHORTCUT KEYS

Here are a handful of helpful shortcut keys you can use to keep your fingers on the keyboard as you edit in Excel:

F2: Begins editing the active cell.
F3: Pastes a name into a formula.
Esc: Cancels the editing.
Home: Moves the cursor to the beginning of the line.
End: Moves the cursor to the end of the line.
Ctrl+right/left arrow: Moves the cursor one word to the right or left.
Ctrl+Del: Deletes all characters from the cursor to the end of the line.

TAKING A SHORTCUT

Here is a handful of helpful shortcut keys you can use to keep your fingers on the keyboard as you work on formatting in Excel:

Ctrl+1 -- Prepares to format the selected object
Ctrl+B -- Sets or removes boldface
Ctrl+I -- Sets or removes italic
Ctrl+Shift+~ -- Applies the general number format
Ctrl+Shift+! -- Applies the comma format with two decimal places
Ctrl+Shift+@ -- Applies the time format (hour, minute, a.m./p.m.)

USING A CUSTOM SORT ORDER

Excel, by default, has four custom lists, and you can define your own. To sort by a custom list, click the Options button in the Sort dialog box; then select the list from the First Key Sort Order drop-down list.

Excel custom lists are as follows:

Abbreviated days: Sun, Mon, Tue, Wed, Thu, Fri, Sat
Days: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
Abbreviated months: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
Months: January, February, March, April, May, June, July, August, September, October, November, December

ASCENDING AND DESCENDING SORT ORDERS

When you use the ascending sort order with a key field that contains many different kinds of entries, Excel places numbers (from smallest to largest) before text entries (in alphabetical order), followed by any logical values (TRUE and FALSE), error values, and finally, blank cells.

When you use the descending sort order, Excel arranges the different entries in reverse: numbers are still first, arranged from largest to smallest; text entries go from Z to A; and the FALSE logical value precedes the TRUE logical value.

SORTING RECORDS

To sort records in an Excel database, follow these steps:

Position the cell pointer in the first field name of the database.
Choose [D]ata, [S]ort on the menu bar.
Excel selects all the records of the database (without including the first row of field names) and opens the Sort dialog box. By default, the first field name appears in the "Sort by" drop-down list box and the [A]scending radio button is selected at the top of the Sort dialog box.
Select the name of the field you want the database records sorted by first in the "Sort by" drop-down list box.
If you want the records arranged in descending order, remember also to choose the [D]escending radio button to the right.
If the first field contains duplicates and you want to specify how these records are sorted, select a second field to sort on in the "Then by" drop-down list box and select either the As[c]ending or Desce[n]ding radio button to its right.
If necessary, specify a third field to sort the records by, using the second "Then by" drop-down list box and decide on the sort order to use.
Click OK or press Enter.
Excel sorts the selected records. If you see that you sorted the database on the wrong fields or in the wrong order, choose the Edit, Undo Sort command on the menu bar or press Ctrl+Z to immediately restore the database records to their previous order.

UNEXPECTED RESULTS FROM SORTING

If you're not careful when using the Sort function in Excel, you can accidentally sort a small section of an Excel spreadsheet when you intended to sort the entire spreadsheet. It is possible to highlight a single column and inadvertently sort only that column, thereby breaking any relationship the information in that column had with information in the rest of the spreadsheet. This is not really a glitch in the system -- the folks at Microsoft were smart enough to realize that there would be times that more customized sorts were necessary. Here's an example about how this works:

Assume the formulas in column D are linked to cells in columns A and B. When you sort the data in columns A and B, the formulas in column D are not affected. To use this example, follow these steps:

On a new worksheet, enter the following values:
A1: 10 B1: 0 C1: D1: =Offset($A$1,B1,0)
A2: 20 B2: 1 C2: D2: =Offset($A$1,B2,0)
A3: 30 B3: 2 C3: D3: =Offset($A$1,B3,0)
A4: 40 B4: 3 C4: D4: =Offset($A$1,B4,0)
Select cells A1 to B4.
On the Data menu, click Sort.
Click Column A in the Sort By box, click Descending, and then click OK.

The linked cells in column D remain linked to the data as it appeared in the original order, even after you sort the data in columns A and B. Note: You must select both column A and column B to sort the list in the example. The formula uses column B as the original offset row number.

Sometimes Excel will give you a nice little warning message when it detects that you are about to tamper with the relationship of cells. The warning will read:

Microsoft Excel found data next to your selection. Since you have not selected this data, it will not be sorted. What do you want to do?
Expand the selection
Continue with the current selection

Then there are other times -- like the example above -- where you won't get the nice message. So be careful when it comes to sorting, if a sort doesn't look right you can undo it as long as you haven't made any changes since the sort. Or if you are still apprehensive about sorting you can make a backup of your spreadsheet before you sort and revert back to it if things go wrong.

SHOW YOUR STRIPES

Here's a quick way to apply shading to every other row in your worksheet. This technique assumes that you want to shade every odd-numbered row in the range A1:f100. Start by shading A1:F1 with the color that you want. Then, select A1:F2 (row 1 is shaded and row 2 is not) and copy the range to the clipboard. Next, select A3:F100 and choose Edit, Paste Special (with the Formats option).

MERGING STYLES

Follow these steps if you want to merge styles that were created for other workbooks saved on disks into the workbook you're currently creating:

Open the workbook that contains the styles you want copied into the current workbook.
Choose the Window command and select the workbook that is to receive these styles.
Choose Format, Style on the menu bar to open the Style dialog box in the workbook that is to receive a copy of the styles from the other workbook.
Click the Merge button to open the Merge Styles dialog box, and double-click the name of the workbook from which to copy the styles in the Merge Styles From list box.

If the workbook you're currently working on contains styles (other than the standard six that are part of every new workbook) with the same names as some of those in the workbook whose styles you're copying, Excel displays an alert dialog box asking if you want it to go ahead and merge styles that have the same name. To overwrite the styles in the current workbook whose names are the same, click the Yes button. To merge only the styles with different names, click the No button. To abandon the whole bloody merger of styles, click the Cancel button.

FORMATTING A PIVOT TABLE

When you create a pivot table, the default action is to apply an AutoFormat to the table (you can change this by clicking on the Options button in Step 4). After the pivot table is created, you can always specify a different AutoFormat.

To change the number format for the pivot table data, use the following procedure:

Select any cell in the pivot table's data area.
Right-click and choose Pivot Table Field from the shortcut menu. Excel displays its Pivot Table Field dialog box.
Click on the Number button.
Select the number format you need.

If you want Excel to preserve all the formatting that you perform on individual cells, make sure that the Preserve formatting option is turned on. You do this in the Pivot Table Options dialog box (right-click on a cell, and select Options from the shortcut menu). If this option is not turned on, Excel returns the formats to the default formats when the pivot table is refreshed.

PIVOT TABLE TERMINOLOGY

If you're new to Excel, the concept of a pivot table may be a bit baffling. As far as anyone knows, Microsoft invented the name pivot table. Understanding the terminology associated with pivot tables is important.

Column field: A field that has a column orientation in the pivot table. Each item in the field occupies a column. Column fields can be nested.
Data Areas: The cells in a pivot table that contain the summary data. Excel offers several ways to summarize the data (sum, average, count, and so on).
Grand totals: A row or column that displays totals for all cells in a row or column in a pivot table. You can specify that grand totals be calculated for rows, columns, or both (or neither).
Group: A collection of items that are treated as a single item. You can group items manually or automatically (group dates into months, for example).
Item: An element in a field that appears as a row or column header in a pivot table.
Page field: A field that has a page orientation in the pivot table -- similar to a slice of a three-dimensional cube. Only one item in a page field can be displayed at one time.
Refresh: To recalculate the pivot table after changes to the source data have been made.
Row field: A field that has a row orientation in the pivot table. Each item in the field occupies a row. Row fields can be nested.
Source data: The data used to create a pivot table. It can reside in a worksheet or an external database.
Subtotals: A row or column that displays subtotals for detail cells in a row or column in a pivot table.

CHANGING THE CASE OF TEXT

Excel provides three handy functions to change the case of text:

UPPER: converts the text to ALL UPPERCASE
LOWER: converts the text to all lowercase
PROPER: converts the txt to "proper" case (The First Letter In Each Word Is Capitalized)

These functions are quite straightforward. The formula that follows, for example, converts the text in cell A1 to proper case. If cell A1 contained the text MR. JOHN Q. PUBLIC, the formula would return Mr. John Q. Public.

=PROPER(A1)

These functions operate only on alphabetic characters; they simply ignore all other characters and return them unchanged.

CONCATENATION TEXT

Concatenation, a useful technique for combining text information from different cells, isn't really a function, but is frequently used with text functions and often with pure text strings. The entry =A3&" "&A4 extracts the content of cell A3, adds a space, and then appends the content of cell A4.

BREAKING LONG TEXT ENTRIES

You can break a long text entry into separate lines by positioning the insertion point in the cell entry (or on the Formula bar) at the place where you want the new line to start and pressing Alt+Enter. Excel expands the row containing the cell (and the Formula bar above) when it starts a new line. When you press Enter to complete the entry or edit, Excel automatically wraps the text in the cell, according to the cell's column width and the position of the line break.

TEXT TO SPEECH

Many will be excited to learn that Office XP has a new speech recognition system. Well Excel 2002 takes that feature to the next level. With Excel's Text to Speech feature you can have Excel 2002 convert the content of your cells into speech. It will actually read the workbook back to you! This comes in handy when trying to verify data entered into the workbook, but it is also just fun play with!

SPLITTING TEXT STRINGS WITHOUT FORMULAS

In many cases, you can eliminate the use of formulas and use Excel's Data, Text to Columns command (Alt, D, E) to parse strings into their component parts. Selecting this command displays Excel's Convert Text to Columns Wizard -- a series of dialog boxes that walk you through the steps to convert a single column of data into multiple columns.

TO EXCEL, TEXT IS NOTHING BUT A BIG ZERO

You can use the AutoCalculate indicator to prove to yourself that Excel gives all text entries the value of 0 (zero). Simply enter a number like 10 in one cell and then some stupid piece of text, like "Excel is like a box of chocolates," in the cell directly below. Then drag up so that both cells (the one with 10 and the one with the text) are highlighted. Then take a gander at the AutoCalculate indicator on the status bar, and you see that it reads SUM=10, proving that the text adds nothing to the total value of these two cells.

ELAPSED TIME ACROSS DAYS

If you're trying to measure elapsed time (hours and minutes) across more than a day, you need a special format -- [h]:mm -- to produce the correct result. If you subtract two cells containing the date/time entries 8/7/99 2:43 PM and 8/5/99 4:49, the result is 9:54 with standard time formatting. Use the [h]:mm format and you get the correct answer: 57:54 -- 57 hours and 54 minutes.

TOOLBAR CUSTOMIZATION

Any customizations you make to an Excel toolbar, either built-in or custom, are "permanent." In other words, the changes remain in effect even when you restart Excel. These toolbar changes are not associated with a particular workbook. To restore a toolbar to its original state, you must reset it.

TOOLBAR TALK

One of the great timesaving features in Excel is its toolbars. By default, Excel displays the Standard and Formatting toolbars. The Standard toolbar contains buttons for such common tasks as opening and saving files, cutting and pasting data, and printing. Depending on the task that you're performing, Excel may automatically display additional toolbars on-screen.

If you're unsure of the function of a particular toolbar button, hovering the mouse over the button displays a short description of the button's purpose.

To display additional toolbars, choose the View, Toolbars menu command and choose the appropriate toolbar from the list of names that appears.

A HOW-TO ON MOVABLE TOOLBARS

Movable toolbars are a cool feature in Office XP. Toolbars usually appear at the top of the screen. Or, you may find two or more toolbars smashed together (which can look confusing). So feel free to tear off your toolbars and move them anywhere around the screen, including the side, bottom, or right in the middle of the screen instead.

MUSICAL TOOLBARS

Don't get too used to the button arrangement that you experience when first using the Standard or Formatting toolbars. Excel 2002 uses the intelli-(non)-sense feature whereby the program automatically keeps promoting the button that you last used to a higher position on the toolbar. For instance, if you use a button on the Toolbar Options palette, the button is immediately added to the regularly displayed portion of the toolbar. Excel 2002 banishes one of the unused buttons onto the Toolbar Options palette. The result is musical toolbars wherein you can never be sure where a needed button will appear (or not appear if it happens to be exiled temporarily to the Toolbar Options palette)!

Unfortunately, Excel 2002 doesn't give you an option for fixing the position of the buttons on the toolbars so that you can always tell where a needed button appears. You can, however, restore the original arrangement of the buttons on the toolbars (as well as the commands on the menus) by right-clicking somewhere on the menu bar or the bar with the Standard and Formatting toolbars and then selecting the Customize command on the shortcut menu that appears. Doing this opens the Customize dialog box from which you select the Options tab. Click the Reset My Usage Data button on the Options tab. Excel then displays an alert box indicating that the record of the commands you've selected in Excel is about to be deleted. Click the Yes button, and the program restores the buttons on your toolbars (and the commands on your menus) to their previous order.

TRANSIENT TOOLBARS

Normally, the Standard and Formatting toolbars appear side by side on the second bar at the top of the Excel program in a stationary position politely referred to as being in a docked position. Although, Excel automatically docs these toolbars together at the top of the screen, you are free to move them (as well as other toolbars that you open) around by dragging them into new positions.

When you drag the Standard or Formatting toolbar down from its perch and into the work area containing the open workbook, the toolbar then appears in a separate little window. Such toolbars-in-a-window are referred to as floating toolbars because they float like clouds above the open workbook below (how poetic!). And not only can you move these little dears, but you can resize them as well:

You can move a floating toolbar into new positions over the worksheet document by dragging it by its tiny title bar.
You can resize a floating toolbar by dragging any one of its sides. Wait until the mouse pointer changes to a double-headed arrow before you start dragging.
To close a floating toolbar when you no longer want it in the document window, click the Close box (the small box in the upper-right corner of the toolbar window).

KEEP TOOL TIPS PRESENT

Even experienced Excel users are fond of those pop-up tool tips that appear when you point to a toolbar button. If they're not there, click View, Toolbars, Customize. In the Toolbars dialog box, click the Options tab and check the box next to Show screen tips on toolbars.

Voila!

TAMING TYP-OHS WITH AUTOCORRECT

The AutoCorrect feature in Excel is a godsend for those of us who tend to make the same silly typos over and over again. With AutoCorrect, you can alert Excel 2000 to your own particular typing gaffes and tell the program how it should automatically fix them.

To add to the list of replacements Excel already has, follow these steps:

Choose Tools, AutoCorrect on the menu bar to open the AutoCorrect dialog box.
Enter the typo or abbreviation in the Replace text box.
Enter the correction of full form in the With text box.
Click the Add button or press Enter to add the new typo or abbreviation to the AutoCorrect list.
Click the OK button to close the AutoCorrect dialog box.

MUCH ADO ABOUT UNDO

The [U]ndo command on the [E]dit menu changes in response to whatever action you just took. Because it keeps changing after each action, if you forget to strike when the iron is hot, so to speak -- by using the Undo feature to restore the worksheet to its previous state before you choose another command -- you then need to consult the pop-up menu on the Undo button on the Standard toolbar to select the specific action that you want undone. To open this menu, you click the drop-down button that appears to the right of the Undo icon (the curved arrow pointing to the left). After the Undo drop-down menu is open, click the action on this menu that you want undone. Excel will then undo this action and all actions that precede it in the list (which are automatically selected).

MUCH UNDO ABOUT SOMETHING

One very useful feature in Excel is its multilevel undo. This means that you can reverse your recent actions, one step at a time. For example, if you discover that you accidentally deleted a range of data several minutes ago, you can use the undo feature to "backtrack" through your actions until the deleted range reappears.

Undoing your actions can only be done in a sequential manner. In other words, if you want to undo an action you must also undo all of the actions that you performed after the action that you want to undo. You can undo the past 16 operations that you performed.

GAINING THE UPPERCASE

HARD-CODING VALUES

When you create a formula, think twice before using a literal value in the formula. For example, if your formula calculates 7.5 percent sales tax, you may be tempted to enter a formula such as:

=A1*.075

A better approach is to insert the sales tax rate into a cell and use the cell reference in place of the literal value. This makes it easier to modify and maintain your worksheet. For example, if the sales tax rate changes to 7.75 percent, you need to modify every formula that uses the old value. If the tax rate is stored in a cell, you simply change one cell and all the formulas automatically get updated.

EXPORTING AND IMPORTING WITH VBA

Every object in a project can be saved to a separate file. Saving an individual object in a project is known as exporting. And it stands to reason that you can also import objects into a project. Exporting and importing objects might be useful if you want to use a particular object (such as a VBA module or a UserForm) in a different project.

To export an object, select it in the Project Explorer window, and choose File, Export File (or press Ctrl+E). You'll get a dialog box that asks for a filename. Note that the object remains in the project (only a copy of it is exported). If you export a UserForm object, any code associated with the UserForm is also exported.

To import a file into a project, select the project's name in the Explorer window, and choose File, Import File. You'll get a dialog box that asks for a file. You can import only a file that has been exported using the File, Export File command.

If you would like to copy a module or UserForm object to another project, it's not really necessary to export and then import the object. Make sure both projects are open. Then simply activate the Project Explorer, press Ctrl, and drag the object from one project to the other.

GETTING THE WORD ON VBA LANGUAGE

With the introduction of VBA in Excel 5, the terminology used to describe the Excel programmable features got a bit muddy. For example, VBA is a programming language, but it also serves as a macro language. So what do you call something written in VBA and executed in Excel? Is it a macro, or is it a program? Excel's online help often refers to VBA procedures as macros, so you're safe to go with that terminology. But this stuff also is a program. If you really want to make sense of how Visual Basic for Applications works with Excel, you need to be flexible, remain calm, and make friends with a reliable resource of inside information.

(By the way, macro does not stand for Messy And Confusing Repeated Operation. Rather, it comes from the Greek makros, which means large -- which also describes your paycheck after you become an expert macro programmer.)

LEARNING VBA

Now a few words about reality: Excel is a moving target. Excel's upgrade cycle is approximately 18 months, which means that you have one and one-half years to get up to speed with its current innovations before you have even more innovations to contend with.

Excel 5, which introduced VBA, represented a major paradigm shift for Excel developers. Thousands of people up until that point earned their living developing Excel applications that were largely based on the XLM macro language in Excel 2, 3, and 4.

Beginning with Excel 5, dozens of new tools became available and developers -- for the most part -- eagerly embraced them.

When Excel 97 became available, developers faced yet another shift. This new version introduced a new file format, the Visual Basic editor, and UserForms as a replacement for dialog sheets.

VBA is not difficult to learn, but it definitely takes time to become comfortable with it and even more time to master it. The VBA language is still evolving. Consequently, it's not uncommon to be in the process of learning VBA while you're developing applications with it. It's very difficult, perhaps impossible, to learn VBA without developing applications. You may find it much easier to learn VBA if you have a project that requires it. Learning VBA just for the sake of learning VBA usually doesn't work.

THE VISUAL BASIC EDITOR

In Excel 5 and Excel 95, a Visual Basic for Applications (VBA) module appeared as a separate sheet in a workbook. Beginning with Excel 97, VBA modules no longer show up as sheets in a workbook. Rather, you use the Visual Basic Editor (VBE) to view and work with VBA modules.

VBA modules are still stored with workbook files; they just aren't visible unless you activate the VBE.

The VBE is a separate application that works seamlessly with Excel. Seamlessly meaning that Excel takes care of the details of opening the VBE when you need it. You can't run VBE separately; Excel 2000 must be running in order for the VBE to run.

When you're working in Excel 2000, you can use any of the following techniques to switch to the VBE:

Press Alt+F11.
Select Tools, Macro, Visual Basic Editor.
Click the Visual Basic Editor button, which is located on the Visual Basic toolbar.

Don't confuse the Visual Basic Editor with the Microsoft Script Editor. These are two entirely different animals. The Script Editor is used to edit HTML scripts written in VBScript or JavaScript.

INTRODUCING THE VISUAL BASIC EDITOR

Before you can begin creating custom functions, you need to become familiar with the Visual Basic Editor, or VB Editor for short. The VB Editor enables you to work with VBA modules, which are containers for your VBA code.

In Excel 5 and Excel 95, a VBA module appeared as a separate sheet in a workbook. Beginning with Excel 97, VBA modules no longer show up as sheets in a workbook. Rather, you use the VB Editor to view and work with VBA modules. In Excel 97 and later versions, VBA modules are still stored with workbook files; they just aren't visible unless you activate the VB Editor.

EXCEL AND VISUAL BASIC FOR APPLICATIONS

Excel 5 was the first application on the market to feature Visual Basic for Applications. VBA is best thought of as Microsoft's common application scripting language, and it's now included with all Office 2000 applications -- and even applications from other vendors. Therefore, if you master VBA using Excel, you'll be able to jump right in and write macros for other Microsoft (and non-Microsoft) products. Even better, you'll be able to create complete solutions that use features across various applications.

The secret to using VBA with other applications lies in understanding the object model for each application. VBA, after all, simply manipulates objects, and each product (Excel, Word, Access, PowerPoint, and so forth) has its own unique object model. You can program an application using the objects that the application exposes.

Excel's object model, for example, exposes several very powerful data analysis objects, such as worksheets, charts, pivot tables, scenarios, and numerous mathematical, financial, engineering, and general business functions. With VBA, you can work with these objects and develop automated procedures. As you work with VBA in Excel, you'll gradually build an understanding of the object model. Warning: It can be very confusing at first. Eventually, however, the pieces will come together and all of a sudden you'll realize that you've mastered it!

VENTURING INTO UNFAMILIAR TERRITORY

Before making changes to an unfamiliar worksheet, use the Zoom button on the Standard toolbar to zoom out to 50 percent or 25 percent so that you can get a good idea of how the information is laid out. When inserting or deleting cells in an unfamiliar worksheet, resist the temptation to insert or remove entire columns and rows, which can damage unseen tables of data. Instead, just insert or cut out the cell ranges in the region you're working in -- you know, think globally, but act locally!

SAVING AN INTERACTIVE WEB PAGE

The Interactive Web pages feature is one of the coolest features in Excel 2000. This is because interactive Web pages enable the users who view your Web pages with the Microsoft Internet Explorer (Version 4.0 or later) to make changes to the worksheet data -- all without any kind of scripting or programming on your part. Here's how to save you worksheet as an Interactive Web page:

Open the worksheet to be saved as a Web page.
Choose File, Save Web Page to open the Save As dialog box.
Click the selection Sheet radio button and the Add Interactivity check box, and then click the Publish button.
Enter the name for the new Web page in the File Name text box.
To see the new Web page in the browser, click the "Open Published Web page in the browser" check box before you click the Publish button.
In Internet Explorer, click the Property Toolbox button in the toolbar that appears above the worksheet data to open the Spreadsheet Property Toolbox.

SAVING AN INTERACTIVE WEB PAGE

The Interactive Web pages feature is one of the coolest features introduced with Excel 2000. This is because interactive Web pages enable the users who view your Web pages with the Microsoft Internet Explorer (Version 4.0 or later) to make changes to the worksheet data -- all without any kind of scripting or programming on your part (put that in your Java cup and drink it!). These changes can include any of the following elements:

Worksheet data tables: In interactive worksheet tables, you can edit the values and have the formulas automatically (or manually) updated in the tables. You can also change the formatting of the data and what parts of the worksheet are displayed in the Web page.
Database lists: In interactive database lists, you can sort and filter the records more or less as you do in normal Excel database as well as edit the data and change the list's formatting.
Charts: In interactive charts, you can edit the supporting data and have the chart automatically redrawn on the Web page. You can also make changes to the chart itself, including chart type, titles, and certain chart formatting.

PREVIEWING WEB PAGES WITH YOUR BROWSER

To get an idea of how the worksheet data and charts will look should you save all or part of its workbook in a Web page, you can use the [F]ile, We[b] Page Preview command on the menu bar. When you choose this command, Excel 2000 launches your Windows Web browser showing all the worksheet data and charts in the workbook more or less as they will appear in the final Web page. If the workbook you're previewing contains data and charts placed on different sheets and you preview the Web page, you can use the sheet tabs at the bottom of the browser's window to browse the information on the individual sheets.

CREATING AN EMPTY WORKBOOK FILE IN EXCEL

After you start Excel, it automatically creates a new (empty) workbook that it calls Book1. If you're starting a new project from scratch, you can use this blank workbook.

You can create another blank workbook in any of the following three ways:

Click the New button on the Standard toolbar.
Press Ctrl+N.
Choose File, New and click Blank Workbook in the New Workbook task pane.

Any of these methods creates a blank default workbook.

FINDING LOST WORKBOOKS

A common problem among computer users is "losing" a file. You know that you saved a file, but you don't remember the folder that you saved it in. Fortunately, Excel makes it fairly easy to locate such lost files by using the Open dialog box.

The procedure for finding files is much different in Excel 2000 -- and also much easier. Select File, Open, and then click Tools, Find (or just press Ctrl+F).You'll see the Find dialog box. Although this dialog box looks a bit complicated, its purpose is to collect two pieces of information from you:

Search criteria (what to look for)
Where to look (the search scope)

You can search for files based on virtually any criteria (or combination of criteria) you can think of, including name, size, file type, contents, and so on. And after you define a search, you can save it, so that you can search later by the same criteria. When searching for a file, your search scope can be very broad (a complete hard drive) or very narrow (a specific folder).

If your searches aren't very complex, you may find it faster to use the Windows Find feature. You can search by filename, date, size, and even contents. Click the Windows Start button and then select Find, Files or Folders. Enter your search criteria and click Find Now. A list of matching files will be displayed. To open a file in Excel, just double-click it.

ADD WORKSHEET TO YOUR EXCEL VOCABULARY

CHECKING FOR SPECIFIC WORKSHEET ERRORS

Excel 2002 comes with a new feature that alerts you to specific types of errors or potential errors in your worksheet. Excel 2002 can flag the following types of errors:

Formulas that evaluate to error values (for example, #DIV/0!, #VALUE!, #N/A, and so on).
Text dates (that is, dates that you format as text or precede with an apostrophe) that you enter with two-digit years.
Numbers that you store as text (that is, numbers that you format as text or precede with an apostrophe).
Inconsistent formulas in a region (that is, different from formulas in surrounding cells).
Formulas that omit cells in a region (for example, a formula that sums a range but omits the last cell or cells in the range).
Unlocked cells containing formulas if the worksheet is protected.
Formulas containing references to cells that are empty.

Remember: Use Excel's suggestions to serve as a guide only to alert you of potential errors on your worksheet because some of the data that you enter intentionally Excel may flag as errors.

Excel's error-alert feature isn't foolproof. You should use it in conjunction with other techniques to the most out of it.

CHANGING A WORKSHEET'S NAME

Excel names its worksheets, by default, Sheet1, Sheet2, and so on. Providing more meaningful names helps you identify a particular sheet. To change a sheet's name, click the sheet tab to select that worksheet and use any of the following methods to rename it:

Choose Format, Sheet, Rename from the menu bar.
Double-click the sheet tab.
Right-click the sheet tab and choose the Rename command from the shortcut menu that appears.

Any of these methods selects the text in the tab. Just type the new sheet name directly on the tab.

Sheet names can run up to 31 characters. Names can include spaces but not the following characters: [] (square brackets); : (colon); / (slash);\ (backslash); ? (question mark); and * (asterisk).

WORKSHEET PRINTOUT: THE EXCEL SWELL

If you were to produce an entire Excel worksheet grid on paper, you would need a sheet that was approximately 21 feet wide by 1,365 feet long to do it! On a 14-inch computer screen, you can normally see no more than between 10 and 12 complete columns and between 20 and 25 complete rows of the entire worksheet. With columns being about 1 inch wide and rows about 1/4 inch high, 10 columns represent a scant 3.9 percent of the total width of the worksheet, while 20 rows fill only about 0.03 percent of its total length. This should give you some idea of how little of the total worksheet is visible on the screen as well as just how much area is available.

All spreadsheet information is stored in the individual cells of the worksheet. You can, however, enter information into only the cell that is current (that is, selected with the cell pointer).
Excel indicates which of the over 16 million cells in the worksheet is the current (active) one by displaying its cell reference on the formula bar and displaying the cell pointer in the worksheet itself.
The system for referencing cells in a worksheet -- the so-called A1 cell reference system -- combines the column letter (or letters) with the row number.

COLORING A WORKSHEET TAB

Select the worksheet tab that you want to color.
Use any of the following methods to open the Format Tab Color dialog box:
- Choose Format, Sheet, Tab Color from the menu bar.
- Right-click the sheet tab and choose Tab Color from the shortcut menu that appears.
Select a color for the tab from the Tab Color palette.
Click OK.

To undo the tab color, follow the same procedure and click the No Color option in the Format Tab Color dialog box and click OK.

Remember, if you select a tab that you color-code, the sheet name appears underlined with the color. If a tab displays a background color, it's not currently selected.

DISPLAYING FORMULAS ON A WORKSHEET

One way to audit your workbook in Excel 2002 is to display the formulas rather than the results of the formulas. Then you can examine the formulas without needing to scroll through the worksheet.

To display formulas instead of the formula results (values), choose the Tools, Formula Auditing, Formula Auditing Mode menu command (or press Ctrl+~).

You may want to create a new window for the workbook before issuing this command. That way, you can see the formulas in one window and the results in the other.

CONSOLIDATING WORKSHEETS

Data consolidation refers to the process of merging data from multiple worksheets or multiple workbook files. A division manager, for example, may consolidate various departmental budgets into a single workbook.

Whether you lay out the information exactly the same way in each worksheet is the main factor that determines how easy a consolidation task is. If you do so, the job proves relatively simple.

If the worksheets have little or no resemblance to each other, your best bet may be to edit each sheet so that they match each other. In some cases, simply reentering the information in a standard format may prove more efficient than editing each sheet.

Any of the following techniques enable you to consolidate information from multiple worksheets or workbooks:

Using formulas (link formulas if the data is in multiple workbooks).
Using the Data, Consolidate menu command.
Using a PivotTable.

REARRANGING WORKSHEETS

Sometimes, you may find that you need to change the order in which the sheets appear in the workbook. Excel makes this possible by letting you drag the tab of the sheet you want to arrange in the workbook to the place where it should be inserted. As you drag the tab, the pointer changes to a sheet icon with an arrowhead on it, and the program marks your progress among the sheet tabs. When you release the mouse button, Excel reorders the worksheets in the workbook by inserting the sheet at the place where you dropped the tab off.

If you hold down the Ctrl key as you drag the tab, Excel inserts a copy of the worksheet at the place where you release the mouse button. You can tell that Excel is copying the sheet, rather than just moving it in the workbook, because the pointer shows a plus on the sheet icon containing the arrowhead. When you release the mouse button, Excel inserts the copy in the workbook, which is designated by the addition of (2) after the tab name. For instance, if you copy Sheet5 to another place in the workbook, the sheet tab of the copy is named Sheet5 (2). You can then rename the tab to something civilized.

TEMPLATE WORKBOOKS

If you have a particular workbook that you want to use as a skeleton, or template, for other workbooks, you can save such a workbook by using the File, Save As (Alt, F, A) command and selecting template as the file type. In the future, these files will then appear in the New dialog box on the General tab when you use the File, New (Alt, F, N) command. When you open a template file from the General tab, Excel opens a copy of the template file and gives it a temporary name that looks like the template name with a numerical suffix. For example, after you create a template and close it, open the template. What you see on your screen is a workbook named "templatename1".

If you want to store template files in a tab in the New dialog box, create a subfolder of the Templates folder, located in the Microsoft Office folder. When you save Excel files as templates, you can store them there.

SWITCHING BETWEEN WORKSHEETS USING THE KEYBOARD

The folks at Microsoft wanted to make it easy for you to navigate around in Excel. As a result, you don't have to worry about using those darned tab scrolling buttons and sheet tabs just go back and forth through the sheets in a workbook. You can take care of that with the keyboard. To move to the next worksheet in a workbook, press Ctrl+PgDn. To move to the previous worksheet in a workbook, press Ctrl+PgUp. The nice thing about using the keyboard shortcuts Ctrl+PgDn and Ctrl+PgUp is that they work whether or not the next or previous sheet tab is currently displayed in the workbook window!

TOGGLING WORKSHEETS USING THE KEYBOARD

You can forget all about the darned tab scrolling buttons and sheet tabs and just go back and forth through the sheets in a workbook with the keyboard. To move to the next worksheet in a workbook, press Ctrl+PgDn. To move to the previous worksheet in a workbook, press Ctrl+PgUp. The nice thing about Ctrl+PgDn and Ctrl+PgUp is that these keystroke shortcuts work whether or not the next or previous sheet tab is currently displayed in the workbook window!

ZOOMING WORKSHEETS

Excel enables you to scale the size of your worksheets from 10 percent to 400 percent. Using a small zoom percentage can help you get a bird's-eye view of your worksheet to see its layout. A large zoom percentage can help you decipher very small text.

The easiest way to change the zoom factor of the active worksheet is to use the Zoom Control on the Standard toolbar -- look for the box showing a percentage with a drop-down arrow beside it. Just click the arrow and select the desired zoom factor from the drop-down list. Your screen transforms immediately. (You can also choose View, Zoom to open the Zoom dialog box or type a number directly into the Zoom text box on the Standard toolbar.)

The Selection option in the toolbar's Zoom Control drop-down list zooms the worksheet to display only the selected cells. This option is useful if you want to view only a particular range. For finer control over the zoom factor you can click the Zoom Control, enter a zoom factor directly, and press Enter.

JUST A BIG ZERO

Use the AutoCalculate indicator to prove to yourself that Excel gives all text entries the value of 0 (zero). As an example, enter the number 10 in one cell and then some stupid piece of text, such as Excel is like a box of chocolates, in the cell directly below. Then drag up so that both cells (the one with 10 and the one with the text) are highlighted. Take a gander at the AutoCalculate indicator on the status bar, and you see that it reads SUM=10, proving that the text adds nothing to the total value of these two cells.

COME ON AND ZOOM

You can use the Zoom feature to locate and move to a new cell range in your worksheet. First, select a small magnification, such as 50%. Then locate the cell range you want to move to and select one of its cells. Finally, use the Zoom feature to return the screen magnification to 100% again. When Excel returns the display to normal size, the cell you selected and its surrounding range appear on-screen.

ZOOMING

The Zoom pop-up menu and dialog box offers five precise magnification settings (200%, 100% [normal screen magnification], 75%, 50%, and 25%). To use other percentages besides these, you have the following options:

If you want to use other precise percentages in between the five preset percentages (such as 150% or 85%) or settings greater or less than the highest or lowest (such as 400% or 10%), select the Zoom button's edit box on the Standard toolbar, type the new percentage, and press Enter. (You can also do this by opening the Zoom dialog box and entering the percentage in its Custom edit box.)
If you don't know what percentage to enter in order to display a particular cell range on the screen, select the range, choose Selection at the very bottom of the Zoom button's pop-up menu or open the Zoom dialog box, select the [F]it Selection radio button, and then click OK or press Enter. Excel figures out the percentage necessary to fill up your screen with just the selected cell range.

You can use the Zoom feature to locate and move to a new cell range in the worksheet. First, select a small magnification, such as 50%. Then locate the cell range you want to move to and select one of its cells. Finally, use the Zoom feature to return the screen magnification to 100% again. When Excel returns the display to normal size, the cell you selected and its surrounding range appear on-screen.