What Is the Find Tool in Excel?

Learn about the find tool in this Howcast Excel tutorial with expert Shir Moscovitz.

Transcript

Hi, my name is Shir and I'm the founder and CEO of shirconsulting.com where we focus on converting the existing data from your business into massive savings and extraordinary profits. Today we're going to learn the basics of Excel. Let's get started.

So now let's talk about the Find Tool in EXCEL. This tool is actually universal to all of Microsoft's products. You can do this in Word and Outlook and PowerPoint as well. The idea is to search for something without actually manually looking for it. So let's say we have a whole bunch of data where, somewhere else in the worksheet and we want to find the last name Johnson without actually looking for it. So we'll do a Find right - we can go here on the RIBBON to do a FInd and choose this option - Find over here or preferably we can do CTRL+F for FIND. That brings us to here. It will start out in this Format and we can do a simple Find by just typing in the last name. Hitting the Find Next button or pressing ENTER by default that's the one that's going to happen. And now closing or hitting the X and as you see it brings me to that option. If I do Find again and I do Find Next or hit ENTER again, it brings me to the next instance of that string of text. If I keep hitting it, I cycle through the options. Alternatively, I can click on the Find All option which shows me a list, sometimes it's very large, of all the different results of that search. It shows me which Workbook it is, which Worksheet it is, and which CELL and what Value it has. Sometimes if it has a Formula or sometimes if it has a Name to it. Very useful if we have a lot of different instances.

Let's do a more advanced Find. Let's do CTRL+F and to get more options, we're simply going to click on this Options button. Now we can actually change a few things. We can search for only a specific case right. So it has to match exactly the right upper and lower case. So if we change this to a lower case, we will not find anything because there is no Johnson with a lower case J. If instead we make it like so with a upper case, it will now find properly the right spot. We can also match the entire cell contents so it has to match exactly cause right now if we do a search for part of a word like "arisen" or "arris", "arise" which is part of Harrison, it will not work. There will be nothing. If we uncheck this match entire cell contents, it will find that string within all the CELLS in this Worksheet.

Let's take it up a notch. Let's go not just within this Worksheet but within this entire Workbook. If we do Find Next now, it will cycle through all the different Worksheets and show me all instances of that search. If I do Find All, I'll get a very large list cause there are a lot of different results and I get to see exactly where they are at a glance. SO I see - Oh I want the one in the Find tool. I'm going to click on that and that brings me right here. I'm going to hit close to get out this. So it's a very useful feature to save a lot of time with searching for things. You can also search inside of Formulas if you have specific strings of TEXT within a Formula.

Next, we'll talk about the Find and Replace option. This beats doing a lot of changes to a SPREADSHEET manually. If you want to change all instances of Johnson to something else, we can either do CTRL+F and then go to the Replace tab. Or, I'm going to hit X to get out that, I can straight up hit CTRL+H. So if I hit CTRL+H right away, it brings me here. Every time I Find the string of TEXT Johnson, let's Replace it with Smith. Let's say I got the name wrong. Then instead of just doing a Find Next which I can do here and see it, I'm going to go back to Worksheet view only, search only within Worksheet. I'm going to replace this one instance and then this other instance. Let me undo and get that back. I'm going to do CTRL+H and now I'm going do the Replace All button which immediately switches everything that it found. So there were two replacements that were made and it shows me that little notification here. So that saves you a lot of time as you're going through.

Next, we're going to Replace Smith with Jackson. And not only that, we're going to change the Formatting as well. So the destination Formatting is going to have a Fill of yellow. Let's do Find Next and let's do Replace or we can simply Replace All and all instances of Smith are now Jackson. Three replacements have been made. Close. Close. And that's the Find tool in EXCEL.

Popular Categories