How to find and replace a value

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 ofAnnwithAnna.

We need to enterAnnin the “Find what” box, and enterAnnain the “Replace with” box.

At this point, we have two options.

Let’s replace one cell at a time first.

If we have a go at 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 ofAnn, and now we can press Replace.

Each time we click Replace, Excel replacesAnnwithAnnaand moves on to the next match.

At the last match, Excel stops, since there are no more cells that containAnnonly.

Let’s undo these changes and hit it one more time using “Replace All.”

When using “Replace All,” there’s no need to Find first.

We can just nudge the “Replace All” button.

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

Before using “Replace All,” check Find and Replace options carefully.

If possible, use options like “Match entire cell contents” to tightly control what Excel matches.

After using “Replace All,” confirm you review changes.