Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Excel worksheets often contain a lot of data, much more that you can look through easily if you want to find a specific value. Rather than work by hand, you can use the Find and Replace functionality to locate the values you are looking for. If you want to find a value in your worksheet, you can type it in the Search in Sheet box, which is here on the right side of the toolbar. Let's say that I want to find the letters C-A. If I type in ca and press enter, Excel moves from the starting cell, which was here, and then looks through your data, going by rows, to find the first occurrence of CA.
If you want to find the next occurrence, you can go up to the Search in Sheet box and click the Next button. It moves down. Click Next again. It finds the final one. If there are no more values to be found, it cycles back around to the first one. There is also the Previous button. So, for example, if you want to go back to the previous example of the letters ca, you can press the Previous button and move back. If you want more advanced options, you can click the Edit menu header and then click Find, or press Command+F, depending upon your preference.
You can use the controls in the Find dialog box to search your worksheet. So, for example, let's say that I wanted to find the term lemon, in the case of the lemon infused olive oil, put out by Two Trees. If I type it in, in Find what, and then click Find Next, Excel locates the next occurrence of the term lemon. But now let's see what happens when I press Find Next again, because as you'll see, the word lemon doesn't occur again. But when I click Find Next, it highlights this cell, and you can see why that happened just by looking at it.
The gentleman's last name is Lemontree, and because the character string l-e-m-o-n occurs in his name, just as it does here, that cell was identified as containing a target value. If you want to avoid that, you can click Find entire cells only. So when you select that option, Excel will only find examples of this entire value, as opposed to being part of a longer string. So, for example, if I click Find Next again, Excel highlights Lemon. If I click Find Next again, it no longer finds Lemontree, because it's only looking for matches where the word lemon, or the character string l-e-m-o-n, is the entire content of a particular cell.
There is only one cell like that in this worksheet, and that is cell D2. You can also match case. So, for example, if I have l-e-m-o-n all lowercase, and I click Find Next, Excel will say that it can't find the data we're searching for. The reason is because the first letter of lemon in this cell is a capital L. I'll click OK to clear the notification, backspace over the lowercase l, type out an uppercase L, and when I click Find Next, Excel once again highlights this cell, which contains exactly the string.
Now, let's say that I want to replace values; in other words, instead of finding them, I want to replace them completely with other values. So I'll close here, and then just to show you once again, to get to the Replace dialog box, you can click Edit > Replace. The Replace dialog box has two fields: one where you identify what you want to find, and the second where you type in the value that you want to replace the value that you found. Just for now, I will clear the Match case and Find entire cells only check boxes. So let's say that my friend Daria Robespierre has changed her last name, and it's now Kathaway.
So I'll type Robespierre, and I will replace it with Kathaway. So now we're going to replace it. When I click Find Next, Excel locates the next occurrence of the term Robespierre. I can click Replace, and Excel changed it to Kathaway. But now notice that it also identified a city named Robespierre. I don't want to replace that, so I can click Find Next. It finds it again. I don't want to change it. So now I can click Close. But now, I want to show you something that's extremely tempting and very dangerous.
So I'm going to replace Kathaway with Robespierre again. I just pressed Command+Z to undo my last change. Now I'm going to show you the dangers of Replace All. So Edit > Replace. We're replacing Robespierre with Kathaway. Let's say that I click Replace All. When I do, Excel tells me that it replaced two instances, which is the first indication, because there is only one change I wanted to make. But in addition to changing Daria's last name to Kathaway, the City of Robespierre also had its name changed.
That's not correct. So you need to be very, very careful if you're using Replace All, especially if you're replacing a short string like CA - you can have a lot of unintended errors in your documents. Finding and replacing values in an Excel worksheet is very straightforward. To locate a value, type it in the Search in Sheet box or the Find dialog, and use the controls to find every cell where the value occurs. Replacing one value with another is just as easy, but be sure you're replacing exactly the values you want. If you're not careful, you could make a mistake.
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.