Finding and Replacing Data


Excel 2007 worksheets can contain more than one million rows of data, so it's unlikely that you would have the time to move through a worksheet a row at a time to locate the data you want to find. You can locate specific data on an Excel 2007 worksheet by using the Find and Replace dialog box, which has two tabs (one named Find; the other named Replace) that enable you to search for cells that contain particular values. Using the controls on the Find tab finds the data you specify; using the controls on the Replace tab enables you to substitute one value for another. As an example, one of Consolidated Messenger's customers might change the company name. If that's the case, you can change every instance of the old name to the new name.

When you need more control over the data that you find and replace, such as if you want to find cells in which the entire cell value matches the value you're searching for, you can click the Options button to expand the Find and Replace dialog box.

One way you can use the extra options in the Find and Replace dialog box is to identify data that requires review using a specific format. As an example, Consolidated Messenger VP of Marketing Craig Dewar could make corporate sales plans based on a projected budget for the next year. After the executive board finalizes the numbers, he could use Find Format in the Find and Replace dialog box to locate the old prices and then change them by hand.

To change a value by hand, select the cell and then either type a new value in the cell or, on the Formula Bar, select the value you want to replace and type the new value.

The following table summarizes the Find and Replace dialog box controls' functions.

Control

Function

Find what field

Contains the value you want to find or replace.

Find All button

Selects every cell that contains the value in the Find what field.

Find Next button

Selects the next cell that contains the value in the Find what field.

Replace with field

Contains the value to overwrite the value in the Find what field.

Replace All button

Replaces every instance of the value in the Find what field with the value in the Replace with field.

Replace button

Replaces the next occurrence of the value in the Find what field and highlights the next cell that contains that value.

Options button

Expands the Find and Replace dialog box to display additional capabilities.

Format button

Displays the Find Format dialog box, which you can use to specify the format of values to be found or to replace found values.

Within list box

Enables you to select whether to search the active worksheet or the entire workbook.

Search list box

Enables you to select whether to search by rows or by columns.

Look in list box

Enables you to select whether to search cell formulas or values.

Match case check box

When checked, requires that all matches have the same capitalization as the text in the Find what field (for example, cat doesn't match Cat).

Match entire cell contents check box

Requires that the cell contain exactly the same value as in the Find what field (for example, Cat doesn't match Catherine).

Close button

Closes the Find and Replace dialog box.


In this exercise, you will find a specific value in a worksheet, replace every occurrence of a company name in a worksheet, and find a cell with a particular formatting.

USE the Average Deliveries workbook from the My Documents\Microsoft Press\Excel SBS\Data and Data Tables folder.

OPEN the Average Deliveries workbook.


1.

If necessary, click the Time Summary sheet tab.

The Time Summary worksheet appears.

2.

On the Home tab, in the Editing group, click Find & Select and then click Find.

The Find and Replace dialog box appears with the Find tab displayed.

3.

In the Find what field, type 114.

4.

Click Find Next.

Excel 2007 highlights cell B16, which contains the value 114.

5.

Delete the value in the Find What field and then click the Options button.

The Find and Replace dialog box expands to display additional search options.

6.

Click Format.

The Find Format dialog box appears.

7.

Click the Font tab.

The Font tab appears.

8.

In the Font Style list, click Italic.

9.

Click OK.

The Find Format dialog box disappears.

10.

Click Find Next.

Excel 2007 highlights cell D25.

11.

Click Close.

The Find and Replace dialog box disappears.

12.

On the tab bar, click the Customer Summary sheet tab.

The Customer Summary worksheet appears.

13.

On the Home tab, in the Editing group, click Find & Select and then click Replace.

The Find and Replace dialog box appears with the Replace tab displayed.

14.

On the Format button to the right of the Find what field, click the Format button down arrow and then click Clear Find Format.

The format displayed next to the Find what field disappears.

15.

In the Find what field, type Contoso.

16.

In the Replace with field, type Northwind Traders.

17.

Click Replace All.

18.

Click OK to clear the message box that appears, indicating that Excel 2007 made three replacements.

19.

Click Close.

The Find and Replace dialog box disappears.

CLOSE the Average Deliveries workbook.





MicrosoftR Office ExcelR 2007 Step by Step
MicrosoftВ® Office ExcelВ® 2007 Step by Step (Step By Step (Microsoft))
ISBN: 073562304X
EAN: 2147483647
Year: 2004
Pages: 143
Authors: Curtis Frye

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net