Join Dennis Taylor for an in-depth discussion in this video Finding and replacing data, part of Excel 2016 Essential Training.
- Sometimes you're looking for data in a large list, sometimes you're actually trying to replace data. We're looking at a sheet called Find Replace, those two features are actually located in the same place in the Excel menu system. Let's imagine that, in this list we've got in Column D, Status, and the company that operates this organization has said, "We don't want to use the term half-time anymore. "We've got a number of people who are "working quarter time, three quarters time, "we'd rather use the term 'part-time'." So we want to replace the word "half" with "part," and it occurs many times in this list, and the list can be huge.
It's always best, if you're trying to find or replace data, to narrow the search first, so rather than looking in the entire list, and that's what would happen if we click a single cell and then start to look. Let's click just Column D. On the Home tab in the ribbon, the far right button, Find and Select. Let's simply replace. Now, we can do a Find times, of course, that makes sense, we're just looking for data. Let's do that first, actually, since it appears first here. What if we're looking for "half"? Notice there's an options button here.
If we were trying to look for only that content and nothing else in the cell, "match entire cell contents" is what we would choose. We're not trying to do that, no cell here contains half, and half only. "Match case," sometimes important, here it's not important, we don't care about it. We just want to search automatically here. "Find the next" or "find all". If we choose "Find all," and we'll expand this dialogue box, there's quite a few of them in there. We can see the list. Now, if we do want to replace these, let's click the Replace tab here, let's replace "half" with "part".
We don't have to worry about the hyphens, they're gonna stay in place. Again, be careful here with whether you want to match case or not, we don't, and nor do we want to match entire cell contents. And we'll simply do a replace all. And Excel tells us 96 replacements. We can certainly see that's happened in Cell D-3, and certainly others on the screen as well. Now, you can also find and/or replace based on formatting as well. We see the word "hourly" there, it's in yellow. There's another one. Maybe these people have been tagged earlier, because they've been offered full-time positions.
So what we might want to do is replace all the hourly's that are yellow. Find what? Hourly. Replace with "Full Time". That's the way it's spelled in the other entries. Find what? We're looking for the format here... Where it's filled with yellow. Now, when we replace it, we don't necessarily want to replace it with a color, so we can leave this blank. Now, if, for whatever reason, we said, "Let's replace this with blue format," fine, we could do that. We would continue to have them highlighted, but now they'd be blue instead of yellow.
But "hourly" would be replaced by "full time". Now, in this case, match entire cell contents? Yes. Now, it wouldn't really make a huge difference here, because in no other entry here do we see "hourly" along with other text, but nevertheless, it makes sense here, match entire cell contents. Let's replace all. There are two of them. And you see what's happened on the screen? There's "full time," it's in blue. We replaced "hourly" with "full time," we changed the yellow to blue. Again, we didn't have to use color in the replacement, we did in the find.
We see it's happened right there. So there are a number of reasons for using Find and Replace, here's another one. The Taft Building has just been renamed the Harding Building. We're selecting Column B again to narrow it down, find and select, replace. Find what? "Taft". Tab. "Harding". And here, no format. Clear find format, clear find format. Match entire cell contents, makes sense here.
Replace all. 63 replacements. Now, you can also use the Find feature, although, possibly, at times, filtering might work better. We're looking for "Rick". The only place we see people's names are in Column A, so let's select Column A, find and select, find "Rick". Here we want to make sure we're not matching entire cell contents, so let's uncheck that. And moving this up, because we might have a few, find all.
Make this a bit larger. Well, we've found him, but we also found "Strickland" and "Patrick," in other words, R-I-C-K is found in these entries. Now, we don't have that many, so let's say we probably found the Rick that we were looking for, but do recognize how R-I-C-K here is found within words, not just any word. I didn't choose "match case," possibly that would help. Use "match case" here. I'll again select Column A. And look for Rick that way, see if that narrows it. Find all. And it does, substantially.
We also picked up "Ricky" along the way too, but nothing wrong with that. So you can begin to see how this is working. And those of you who know Filter might compare it and contrast it with this particular way of doing it, too. We see these in the different kind of list here. More often than not, Replace has a role to play here, where it doesn't when you're doing filtering. So, great tools here, Find and Replace, found on the Home tab under Find and Select. The far right button.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros