Join Dennis Taylor for an in-depth discussion in this video Finding and replacing data, part of Excel Essential Training (Office 365).
- [Instructor] Whether you're looking for data within a list or maybe trying to replace data with other data, the Find and Select button that's found on the right side of the Home tab, magnifying glass, is where we find these features. Now, in this worksheet here, I'm looking for people who have retirement benefits. I'm looking for the letter R here. Now do notice the letter R appears with some of these D's and M's. Sometimes all three of them, sometimes with two of them. But I'm thinking out loud here. I want to find the R's. Any time you are trying to find and possibly replace, be sure to narrow down the scope of where you're searching.
We're only looking here in Column H so let's select that. And then go to the button Find and Select. Let's do a Find here. Let's find these R's. When you come to this dialog box, it might have been expanded. There's an Options button to the right. Sometimes you will want to get to some of these other features, sometimes not. I'll collapse it for now. So I'm looking for a capital R here. I could Find Next, I'll click this. And the active cell is now H2. Why? Because there's an R in there.
Find the Next, there's an R. There's an R by itself, but that's what I was looking for all along. I just those that are R by themselves. So what can I do here? That Options button, Match Entire Cell Contents, meaning I want to find R when that's the entire cell. With that check on and now, Find Next. We'll go to the next cell there, that's in cell H6. But we're gonna skip over, as I click Next here, the entry in Row 8 and go all the way down to Row 16, where we see an R by itself.
So you can imagine certain situations where you do want this box checked. And you could probably imagine scenarios where the data you're looking for must be uppercase or must be lowercase. At certain points, this box needs to be checked as well, too. And at other times, you could even be checking for content and a format. If we wanted to find only the R's that have blue backgrounds, we could go into the Format button. So we've got a lot of different combinations here. Now, sometimes what we want to do is Find and Replace. Let's imagine that this company has decided that they no longer want to use the phrase Half-time because some of the half-time people are really point four or point six.
We want to use the phrase Part-time. So we want to make sure that every time we see Half in here, we change it to Part. And we don't have to refer to the entire entry here. So we click Column G, Find and Select, this time Replace. And actually, it's the same dialog box. Every time we see Half, we want to replace it with Part. Match Entire Cell Contents, not this time, because no cell in Column G has just the word Half. So we do not want to Match Entire Cell Contents here.
And let's just replace them all. It's done, 83 replacements. And you can see, there's a Part-time. There's another one down there. We don't have Half-time in the list at all anymore. So, Find and Replace, ultimately the same dialog box. We get there by way of the Find and Select command. An easy feature to use and yet a vital one as we're trying to repair data or fix data or find information within a list.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting your data
- Adjusting rows and columns
- Finding and replacing data
- Inserting and deleting sheets
- Sorting and filtering data
- Creating charts and PivotTables
- Printing and sharing worksheets
- Protecting worksheets and workbooks
Skill Level Beginner
Q: This course was updated on 1/7/2019. What changed?
A: A new video was added that covers working with Excel Ideas.