Using the Replace Feature

Suppose you've entered a particular label or value into the worksheet and find that you have consistently entered it incorrectly. A great way to change multiple occurrences of a label or value is using Excel's Replace feature; you can locate data in the worksheet and replace it with new data. To find and replace data, follow these steps:

1. Select the Edit menu, and then select Replace . The Find and Replace dialog box appears, as shown in Figure 8.1.

   Figure 8.1. Find and replace data with the Find and Replace dialog box.

   

2. Type the text or value that you want to find into the Find What text box.

3. Click in the Replace With text box and type the text you want to use as replacement text.

4. To expand the options available to you in the dialog box, click the Options button (Figure 8.1 shows the dialog box in its expanded form).

5. If you want to match the exact case of your entry so that Excel factors in capitalization, click the Match Case check box. If you want to locate cells that contain exactly what you entered into the Find What text box (and no additional data), click the Match Entire Cell Contents check box.

6. To search for entries with particular formatting, click the Format button on the right of the Find What box. The Find Format dialog box appears (see Figure 8.2). You can search for entries that have been assigned number, alignment, font, border, patterns, or protection using the appropriate tab on the Find Format dialog box. After making your selection, click the OK button.

   Figure 8.2. The Find Format dialog box enables you to search for entries that have been assigned a particular formatting.

   

7. You can also replace your entries with a particular formatting. Click the Format button on the right of the Replace With box. The Replace Format dialog box appears. It is the same as the Find Format dialog box. Simply select any formats you want to assign to your replacement, and then click OK .

8. Click Find Next to find the first occurrence of your specified entry.

9. When an occurrence is found, it is highlighted. Click Replace to replace only this occurrence and then click Find Next to find the next occurrence.

10. If you want to find all the occurrences, click Find All ; you can also replace all the occurrences of the entry with Replace All .

11. When you have finished working with the Find and Replace dialog boxclick Close .

Search an Entire Workbook If you want to search an entire workbook for a particular entry, click the Within drop-down list in the Find and Replace dialog box and select Workbook .

If you don't need to replace an entry but would like to find it in the worksheet, you can use the Find feature. Select Edit , Find , and then type the data you want to locate into the Find What text box and click Find Next .