Part IV: Using Advanced Excel Features

Click the Evaluate button to show the result of calculating the expressions within the formula. Each click of the button performs another calculation. This feature may seem a bit complicated at first, but if you spend some time working with it, you’ll understand how it works and see the value.

Excel provides another way to evaluate a part of a formula:

1.Select the cell that contains the formula.

2.Press F2 to get into Cell Edit mode.

3.Use your mouse to highlight the portion of the formula you want to evaluate. Or, press Shift and use the navigation keys.

4.Press F9.

The highlighted portion of the formula displays the calculated result. You can evaluate other parts of the formula or press Esc(ape) to cancel and return your formula to its previous state.

Caution

Be careful when using this technique because if you press Enter (rather than Esc), the formula will be modified to use the calculated values. n

Searching and Replacing

Excel has a powerful search-and-replace feature that makes it easy to locate information in a worksheet or across multiple worksheets in a workbook. As an option, you can also search for text and replace it with other text.

To access the Find and Replace dialog box, start by selecting the range that you want to search. If you select any single cell, Excel searches the entire sheet. Choose Home Editing Find & Select Find (or, click Ctrl+F). You’ll see the Find and Replace dialog box shown in Figure 32.14. If you’re simply looking for information in the worksheet, click the Find tab. If you want to replace existing text with new text, use the Replace tab. Also note that you can use the Options button to display (or hide) additional options. The dialog box shown in the figure displays these additional options.

FIGURE 32.14

Use the Find and Replace dialog box to locate information in a worksheet or workbook.

668

Chapter 32: Making Your Worksheets Error-Free

Searching for information

Enter the information to search for in the Find What text box and then specify any of the following options.

•Within drop-down list: Specify where to search (the current sheet or the entire workbook).

•Search drop-down list: Specify the direction (by rows or by columns).

•Look In drop-down list: Specify what cell parts to search (formulas, values, or comments).

•Match Case check box: Specify whether the search should be case sensitive.

•Match Entire Cell Contents check box: Specify whether the entire cell contents must be matched.

•Format button: Click to search for cells that have a particular formatting (see the upcoming “Searching for formatting” section).

Click Find Next to locate the matching cells one at a time or click Find All to locate all matches. If you use the Find All button, the Find and Replace dialog box expands to display the addresses of all matching cells in a list (see Figure 32.15). When you select an entry in this list, Excel scrolls the worksheet so that you can view it in context.

Tip

After using Find All, press Ctrl+A to select all the found cells. n

FIGURE 32.15

Displaying the result of a search in the Find and Replace dialog box.

669

Part IV: Using Advanced Excel Features

Note

Because the Find and Replace dialog box is modeless, you can access the worksheet and make changes without the need to dismiss the dialog box. n

Replacing information

To replace text with other text, use the Replace tab in the Find and Replace dialog box. Enter the text to be replaced in the Find What field and then enter the new text in the Replace With field. Specify other options as described in the previous section.

Click Find Next to locate the first matching item and then click Replace to do the replacement. When you click the Replace button, Excel then locates the next matching item. To override the replacement, click Find Next. To replace all items without verification, click Replace All. If the replacement didn’t occur as you planned, you can use the Undo button on the Quick Access toolbar (or press Ctrl+Z).

Tip

To delete information, enter the text to be deleted in the Find What field but leave the Replace With field empty. n

Searching for formatting

From the Find and Replace dialog box, you can also locate cells that contain a particular type of formatting. As an option, you can replace that formatting with another type of formatting. For example, assume that you want to locate all cells that are formatted as bold and then change that formatting to bold and italic. Follow these steps:

1.Choose Home Editing Find & Select Replace to display the Find and

Replace dialog box (or, press Ctrl+H).

2.Make sure that the Replace tab is displayed.

3.If the Find What and Replace With fields are not empty, delete their contents.

4.Click the top Format button to display the Find Format dialog box. This dialog box resembles the standard Format Cells dialog box.

5.In the Find Format dialog box, click the Font tab.

6.Select Bold in the Font Style list and then click OK.

7.Click the bottom Format button to display the Replace Format dialog box.

8.In the Replace Format dialog box, click the Font tab.

9.Select Bold Italic from the Font Style list and then click OK. At this point, the Find and Replace dialog box resembles Figure 32.16. Notice that it displays previews of the formatting that will be found and replaced.

670

Chapter 32: Making Your Worksheets Error-Free

10.In the Find and Replace dialog box, click Replace All. Excel locates all cells that have bold formatting and changes the formatting to bold italic.

You can also find formatting based on a particular cell. In the Find Format dialog box, click the Choose Format from Cell button and then click the cell that contains the formatting you’re looking for.

FIGURE 32.16

Use the Find and Replace dialog box to change formatting.

Caution

The Find and Replace dialog box cannot find background color formatting in tables that was applied using table styles, or formatting that is applied based on conditional formatting. n

Spell Checking Your Worksheets

If you use a word processing program, you probably take advantage of its spell checker feature. Spelling mistakes can be just as embarrassing when they appear in a spreadsheet. Fortunately, Microsoft includes a spell checker with Excel.

To access the spell checker, choose Review Proofing Spelling, or press F7. To check the spelling in just a particular range, select the range before you activate the spell checker.

If the spell checker finds any words it does not recognize as correct, it displays the Spelling dialog box, shown in Figure 32.17.

Note

The spell checker checks cell contents, text in graphic objects and charts, and page headers and footers. Even the contents of hidden rows and columns are checked. n

671