How to find and replace a value

In this lesson, we'll look at how to replace what you've found. Find and Replace is a powerful tool when making global changes to worksheets.

Let's take a look.

Here we have the worksheet we looked at previously. Let's run through a few examples to explore how Find and Replace work.

First, let's replace every occurrence of Ann with Anna. We need to enter Ann in the "Find what" box, and enter Anna in the "Replace with" box.

As before, we'll use "Match entire cell contents" to restrict our search to cells that only contain Ann. 

At this point, we have two options. We can replace each match one at a time, or we can replace all matches in one step using "Replace All."

Let's replace one cell at a time first. If we try to click Replace, Excel complains that it can't find a match. That's because we haven't yet asked Excel to find anything.

Before we can click Replace, we need to click "Find Next." Excel takes us to the first occurrence of Ann, and now we can press Replace.

Each time we click Replace, Excel replaces Ann with Anna and moves on to the next match. At the last match, Excel stops, since there are no more cells that contain Ann only.

Let's undo these changes and try again using "Replace All."

Using the keyboard shortcut control-H, we can quickly open the Find and Replace dialog with the Replace tab selected. 

When using "Replace All," there's no need to Find first. We can just click the "Replace All" button.

Excel makes the changes and reports that 8 replacements were made.

Before using "Replace All," check Find and Replace settings carefully. If possible, use options like "Match entire cell contents" to tightly control what Excel matches.

After using "Replace All," make sure you review changes. If you see that you've replaced values you didn't mean to, just undo and try again, with more restrictive search settings.