Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
If you have a worksheet with a lot of data on it, knowing how to find and how to replace data is very important. So let's take a look. Right now this worksheet is sorted by Last Name, and let's say we have to leave it that way, but you want to find everybody who is in California, and you can see here in Column D we have the States listed. So you want to make sure you are on the Home tab and over here on the right, the little binoculars, choose Find & Select and the first option is Find. I am just going to cancel out of here for a second just to show you the shortcut is Ctrl+F, F for Find.
So let's type in CA for California, click Find Next and look what happens. It finds someone whose name is Carol. Click Find Next again, going to find somebody whose name is Carly. Hmm. Click Find Next again, okay, finally it finds California. Well, there has got to be a better way. So let's click back up here on the top. You can leave that CA in there. But click this Options button, and you see we have a couple of helpful options, Match case. Now, you can match case and say okay, find every state is in capital letters, but let's say you have a worksheet one day where everybody's name is written in uppercase.
So instead of that let's choose Match entire cell contents. Now when you click Find Next, it finds California and every time you click Find Next, it finds California. Okay. Well, that's nice also, but let's say you want to find every single instance of California all at the same time. That is, rather than bouncing from cell to cell to cell, wouldn't it be nice if you can see them all right in front of you? So again, maybe let's click back up here, and instead of Find Next, click Find All and let's just move this and stretch this out, and now this shows you which worksheet and which cell contains California.
And if you notice, these kind of look like links, don't they? Well, click one and each one you click will bring you right to that particular cell. So that's a nice, handy way of finding every instance of what you are searching for. Okay. That's great! Let's close this and I am just going to press Ctrl+Home so I get back up to the top of the worksheet. Let's say we want to replace some data, and if you look down here in Column C, where we have the Department listed, we have some people that are in the HR Department. Maybe we want to spell that out, we want that to say Human Resources.
So again, let's go up here at the top and click on the binoculars button again and choose Replace. And by the way, the shortcut for that is Ctrl+H. Don't worry about that too much. So over here, where it says Find what, let's change that to say HR. Let's press Tab and we will Replace that with Human Resources and we can leave that here as Match cell contents. Now, choose Find Next. It finds it. Click Replace. It replaces it and it goes onto the next one. Now, you could keep going and going, but maybe you want to do this all in one shot.
Well, if you are pretty sure that you won't replace any data that you don't really want to replace, you could choose here Replace All. It replaces everyone and it tells you how many it made. Click OK, you can close out, and now if you scroll down, you see each instance of HR has now changed to Human Resources. So this is a pretty handy feature, I think.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.