Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
One of the powerful features of working with Excel, is the analysis you can do on your data, with only a few clicks. Open up the worksheet for StoreA from your Exercise Files. We're going to concentrate on the Find & Select commands in the Editing group of your Home tab. By clicking on Find & Select, it's going to allow me to find and select specific text, formatting, or types of information within my worksheet. By clicking on the button, I will see a bunch of commands that I can work with. And I'm going to choose the find command. The find command brings up a dialog box with a search bar, and in that particular search bar, I'm going to put the word or item that I'm looking for. Now you can put in numbers, you can put in parts of words, if you're not quite sure how the spelling of something is working you can put in just the first three letters, and it will find the first instance of that particular information that you've put in that bar. I'm going to put in the Word Dutch, because I'm interested in finding out how many mixes I have that are of the Dutch chocolate variety. And I'm not a very good speller so I'm not going to put in the work chocolate, I'm just going to put in the word Dutch.
So I put in my search in my search bar, and I click the Find Next button. And if you notice, over on your spreadsheet, it's going to pop to the first instance of that information. And that's in cell B12. And it's highlighted that information with a black wire around that cell. So there's my first piece. Now I'm wondering, do I have more instances of that Dutch chocolate in my spreadsheet? I click Find Next again, and I pop down to row 26. So you see that the information is now shown up twice within my spreadsheet.
If I keep clicking on the Find Next button, it's going to pop between row 12 and row 26, to show the two instances of information in that spreadsheet. Similarly, if I just put in the letters of Du, I can do the same kind of search. And it's going to pop me to the same areas because it's looking for the Du in Dutch. If I have quite a large spreadsheet and just clicking on Find Next takes me through rows, and rows, and rows, and rows of information, and if you're looking with a million rows of possible pieces of data, that's going to take an awfully long time.
You have this Find All button that you can work with. If you click on that, what it does is it presents all of the areas where it's found and instance of the word Ducth in your spreadsheet. And you can pop to that by clicking on the link, and it takes you to that particular cell where that information's found. Once you get familiar with the cell addresses as you see here, you'll know that the information that you were looking for is farther down on your spreadsheet, so you can pop right down to that. So it's a really quick way to move back and fourth through the spreadsheets. If we click on the Options button here, you can see that there are other options that you can set when you're doing your finding in your spreadsheet. You could identify what particular format you want to look for that information in, so if you click on the Format button, and click on the Format command, it brings up your Number formatting dialog box. And you can identify what particular format you're looking for. why is this helpful? This is helpful when you're looking for a number that's been placed into the spreadsheet as a piece of text. And that would usually come under the General category.
That's important when you're doing analyses and when you're doing formulas, because sometimes the formulas are not going to work correctly if the number is in an incorrect format. So this helps pull out that kind of information and zero in on it very, very quickly. I'm just going to cancel this because our particular search is quite simple, and we don't need to set that formatting. Something I might want to look at here though is, where am I going to look for this information? Am I going to look at just in the sheet that I'm looking, so just on the sheet for StoreA, or if I have quite a few sheets in my workbook, do I want to search the whole workbook for the instance of this information? So you can choose that. You can determine whether you're going to search down the rows first or are you going to search across the columns? And are you also going to look in the formulas for this information? If you've used names in your formulas that might be worth while looking at. As well, you could also look in Values or the Comments for this kind of information, so you can really search deep into your Excel spreadsheet.
You can also see if you're going to be case sensitive in your matching, and are you going to manage the entire contents of the cells, or are you just going to look for instances as we've done here. Those are all the different choices that you can do when you're trying to look for data within your spreadsheet. Next, let's see how you can quickly replace information when you'd like to update your spreadsheets.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 70024 Viewers
80 Video lessons · 127422 Viewers
52 Video lessons · 62364 Viewers
59 Video lessons · 48067 Viewers
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.