Finding and Replacing Data in a Worksheet

   Overview   

You can use the search feature in a worksheet to locate data values. This can be helpful if you are working with large amounts of data.

You can also direct Excel to locate all instances of a value (such as a name) in order to replace them with another value. This is known as the Replace feature.

   Instructions   

  1. Step 1: Click the Find & Search magnifying glass on the Home tab.
  2. Step 2: Click the Find... option.
  3. Step 3: Enter the appropriate information you want to search for into the Find what: field.  
  4. Step 4: Click the Find Next button.
  5. Step 5: Click the Close button.
  6. Step 6: Click the Replace tab.
  7. Step 7: Enter the appropriate information you want to find into the Find what: field.   
  8. Step 8: Enter the value you want to replace with into the Replace with: field.
  9. Step 9: Click the Replace All button.
  10. Step 10: Click the OK button.
  11. Step 11: Click the Close button.

In this article, you will learn the steps to find or replace data values within an Excel worksheet.

1. Step 1

step image

To search for text or numbers in a worksheet, click the Find & Search magnifying glass on the Home tab.

2. Step 2

step image

A pop-up menu is displayed.

Click the Find... option.

3. Step 3

step image

The Find and Replace search box is displayed.

Enter the appropriate information you want to search for into the Find what: field.   

For this example, you want to find the employee Starwood Demopolis. In the Find what: field, enter " Starwood ".

4. Step 4

step image

Click the Find Next button.

Note: The Find Next option finds every occurrence of the information, displaying each instance in the search results one at a time. To scroll through multiple results, continue pressing Find Next until the option you need is displayed.

5. Step 5

step image

An instance of the search criteria is displayed.

Within the Find and Replace window, click the Close button.

6. Step 6

step image

You might also search for values in a worksheet if you want to replace them with different values.

In the Find and Replace window, click the Replace tab.

7. Step 7

step image

Enter the appropriate information you want to find into the Find what: field.    

Example shown here: "Starwood Demopolis" into the Find what: field.

8. Step 8

step image

Enter the value you want to replace with into the Replace with: field. This value will replace all of the instances of the data entered into the Find what: field.

Example shown here: "Glenn Hadley" in the Replace with: field.

9. Step 9

step image

Click the Replace All button.

10. Step 10

step image

A confirmation of the replacements and the number of instances replaced is displayed.

Click the OK button.

11. Step 11

step image

Click the Close button.