How to Find and Replace New Data with Excel 2013

show more Finding and replacing data provides you with in-depth training on Business. Taught by Dennis Taylor as part of the Excel 2013 Essential Training show less
please wait ...

Finding and replacing data

On the Home tab in the ribbon the editing group on the right-hand side contains a number of features including a binoculars icon for Find & Select. Some of the things we're about to show you in this movie could be done more efficiently with a filter, but not always. Sometimes we're simply looking for data. Let's imagine that we're looking for somebody named Rick. Before searching for data in Excel it's best to narrow down the search. If you click a single cell and then activate the Find feature, you will automatically be looking in the entire worksheet.

If you select a range--possibly you have this range already selected for a different reason-- if you start a Find now, we're only looking for data within the range. Now let's say we are looking for someone named Rick. The only location in this worksheet where we see names like that is column A. Let's go to column A, select that first, and now our search process using Find will only be looking in column A. Notice it's "Find and Replace". We're simply going to do Find here. We're looking for Rick. So find what? Rick.

Notice I'm not capitalizing it although I could. Let's just see where we can find here. Find All, find all occurrences of Rick. As we move this around a little bit and expand it, well, perhaps more than we would have expected. But recognize that we found Strickland and Patrick and Frederick and Erickson. There's an "R-I-C-K" in these names. But since the list is relatively small, that's not so bad. We could easily figure out the person we were trying to find. Now what we could have done here, we could have used a capital "R" in our search and then chose the option "Match Case".

Now at times when you're using Find and Replace, you do see these choices but if you click the options tab, it might collapse. So there could be times when you're using this you don't see those options--choose options. If we Match Case and then choose Find All, the list will be substantially smaller. Only three entries. Now we also found Ricky, but that's okay too, probably, in the case here. So be sensitive to that idea. Recognize another choice here "Match Entire Cell Content". Sometimes that's helpful too particularly with larger lists it helps narrow down the search faster.

Now step outside into the data again-- another use of this and now for Replace situations-- let's imagine that in this list here the company has decided to call the half-time status, "part-time". This is a number of people who work, maybe three quarters or 60% or 40% of the time, we simply want to use the more encompassing term, part-time instead of half-time. So what is that we're looking for here? We're looking for half. Now do we want to match the entire cell contents? In no case, let's say in column D, do we have the word "half" and only the word "half", so we do not want that box checked.

At other times it will make sense to check the box. If we want to match the case, yes. Now, is it critical here? Probably not. Probably half is always capitalized. So don't worry about it too much, but let's say we want to replace half. We go to the Replace button half. Now, do I want to replace it with full-time? No, I don't think so. The reason that's there is that maybe a recent search included that. So let's instead say we're going to replace "half" with "part" and we want to replace all of them. So we'll do that.

So what happened? Ninety-six replacements were made and we can see in the background part-time, part-time. No more half-time in column D. So we click out here. Now another use of this--and this also implies we can use formatting and we can-- we've got some hourly employees in here, but two of them have been selected here. We've simply made them yellow. We're going to replace their status. We are going to change it from Hourly, for example, into Contract-- so we want to make a difference there. Sometimes what we want to do is replace data using a combination of not only the content, but also the format.

We want to replace the word Hourly. Now not all of them. For example in row five, you can see Hourly. So we are going to take the Hourlies here and replace it with Contract--but not all the Hourlies--just the Hourlies where we've got a format here. In other words, the format of yellow cells; the fill effect yellow. Let's say although we don't really have to do this, say that when we do replace the yellow, we want it to be blue. It's not really necessary, but we want to highlight the data.

So everytime we see Hourly that's yellow we want to replace it with Contract and make it blue. Replace all of these. Now we didn't necessarily select any part of the worksheet-- so the active cell is in one cell or another-- if we make this a little bit more efficient, let's click in column D because that's only where we want to make the change. We'll do a Replace All. Two replacements. Click OK and as we put down the screen here, these both had been Hourly and they were yellow and now they are Contract in blue.

Again, I use the blue here simply to highlight the idea that we sometimes want to use the Replace technique along with formatting as well. Another use of Replace, the Taft building has been renamed the Harding building. Let's make a wholesale change here. We'll simply select column B, go back to Find & Select, this time a Replace. Nothing to do with formats here. So clear the format in both cases and we simply want to change Taft to Harding.

Now it's probably the case that we'd never see the word Taft there along with another word, but just in case we want to match entire cell contents. Matching case here isn't really relevant this time, but matching entire cell contents is. All occurrences of Taft when it's the entire entry we want to replace with Harding. We'll simply do a Replace All. So we've taken care of that--63 replacements. So as you can see, Search and Replace, a valuable tool for making wholesale changes or in some cases just finding data that meet your criteria.

Finding and replacing data
Video duration: 6m 4s 6h 32m Appropriate for all


Finding and replacing data provides you with in-depth training on Business. Taught by Dennis Taylor as part of the Excel 2013 Essential Training

Business Education + Elearning
please wait ...