Replacing Data

The Edit Replace command lets you quickly locate and replace numbers, text, data formats, and special characters. Suppose you have text that you entered incorrectly or that has changed throughout a large worksheet. You can use the Edit Replace command to have Excel search for and replace all occurrences of the incorrect information with the correct information. You can find and replace data in three ways:

  • Replace the first occurrence of the data

  • Replace specific occurrences of the data

  • Replace all occurrences of the data

In the next To Do exercise, you search for the first instance of the word cover and replace it with the word volume.

To Do: Replace Data

  1. Choose Edit, Replace. Excel opens the Find and Replace dialog box.

  2. Click the Replace tab.


    You can use the keyboard shortcut Ctrl+H to display the Find and Replace dialog box with the Replace tab selected.

  3. In the Find What text box, type Cover, if necessary.

  4. In the Replace With text box, type Volume.


    You can specify the format of the data you are searching and replacing. To do so, click the Format button next to the Find What or Replace With text box and select the format options you want.

  5. Click the Find Next button. Excel finds the first occurrence of the information and makes it the active cell.

  6. Click Replace. Excel replaces the word. The word Volume appears after the word Hard in the cell.

  7. If you want to continue replacing specific instances of the data, click the Find Next button and click the Replace button.

  8. To replace all occurrences of the data, click the Replace All button.

If you want to specify how to find the data, click the Options button. You can search within a sheet or workbook, search by rows or columns, select a worksheet element such as Formulas, distinguish to match upper- and lowercase, and find only cells with the exact characters in Find What text box. Figure 8.14 shows the search options for finding data you want to replace.

Figure 8.14. The Find and Replace dialog box with the Find options on the Replace tab.


    Part I: Excel Basics