Excel worksheets often contain a lot of data, much more than you can look through easily if you want to find a specific value. Learn how to quickly locate specific content in this video.
- [Tutor] Excel worksheets often contain a lot of data, much more than you can look through easily if you want to find a specific value. In this movie, I will use a much smaller worksheet to demonstrate how to use Find and Replace. My sample file is the Find Workbook and you can locate it in the Chapter Three folder of your Exercise Files collection. In this workbook I have a set of values and you can see that they are customer names, and the category of product that they tend to buy.
Let's say that I want to search for values within this workbook, for example CA which is the abbreviation for the State of California. To do that, I can go to the Search Sheet box which is here at the top right corner, and then type in CA, and press Return. Doing so displays the first instance of CA and I click the next button here, it displays the next instance, then I have landscape lighting and I see here that there is a CA within that cell value; landscape has CA so that was identified as well so we know it's not case sensitive, then I'll click it again and we get CA in cell D5, click it again, and we go up to category which also contains CA.
So you notice that this tool will every instance of CA and it's not that particular. You can also move back to your previous instances by clicking the Previous button, and you can clear the search term by clicking the Close button. If you wanted a little bit more power, you can use the Advanced Search functionality. To do that, click the Search button - it looks like a magnifying glass at the left side of the Search Sheet box - and from there, you can click Advanced Search.
Here you have the Find dialog box. In the Find What box you could type say the same string we looked for before which would be CA, capital letters, and then you can set whether you look within the sheet or the entire workbook, search by rows, look in values, all those sorts of things. You can also indicate whether you want to match case or find entire cells only. In this case they would have the same result because I'm looking for the State of California which is CA, capital letters, and it would take up the entire cell value.
So I will select find entire cells only and then drag the Find dialog box down into the left, and click Find Next. It goes back up to cell D2, then to D3, then to D5, and if I click Find Next again, it wraps around to the first one. You can also replace values. You can do that by opening the Find dialog box like we did before, and then clicking the Replace button.
Doing so changes the contents of the dialog box which is now called Replace. The first thing I'll do is I will clear find entire cells only; I don't need it in this case. So let's say that I know that Daria Robespierre has changed her last name to Patrick, so I will in the Find What box type Robespierre - R, O, B, E, S, P, I, E, R, R, E - and then I'll do Replace With, and her last name is Patrick; P, A, T, R, I, C, K. And now I can either do a Replace which replaces the next occurrence and then moves onto the next one, or I can click Replace All.
Also note that I could click Find Next to verify whether I do want to change a particular item. In this case I'll just click Replace All, all done, we made two replacements, great okay, except before I click Okay I realize that I only wanted to make one change, so what happened? I'll click Okay and when I look at my data I see that in fact the city of Robespierre, California, has been replaced with the city of Patrick, California; well that's unfortunate.
So what I'll do is I will click Close, and then in cell C2 I'll type Robespierre to change it back. Using Replace All can be dangerous because Find and Replace work with strings that occur within cell values and often don't care about matching case unless you tell it to, you can replace important strings with data that makes your original data, nonsense. The illustration that I've used here of changing a last name to another name and using Replace All, is a common error; don't fall for it.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks