Learn to use Find & Select to quickly find Comments, Conditional Formatting, and more. Author Jen McBee provides tips on how to spend less time finding data in your worksheets using Name Ranges for cells, tables, and objects in your worksheets, focusing o
- View Offline
- [Instructor] Excel is a great tool for analyzing large amounts of data, but it's also perfect for doing simple calculations and tracking almost any type of information. Once you've entered your data, being able to quickly locate specific information in your workbook will be much more efficient if you're using two great tools, the name box, and find and select. In this video, we're going to learn how to use the name box to find named ranges, and tables. We'll also use the find and select tool to find comments and cells with conditional formatting.
Named ranges, tables, and cells with conditional formatting are three elements that you may be asked to locate quickly on the Excel 2010 MOS exam. Notice the MOS icons next to those three. Anytime you see these during the course, this is a reminder, or a heads up, for you to really pay attention to these items because they probably will be on the exam. Are you ready? Well then let's tackle this topic.
Now, because this is our first exercise that we're doing together, I want to take you through the steps of opening the documents that we'll be using in each of the videos. I have Excel open, and let's go ahead and go to our File tab, so we can navigate to the Desktop where we have our Exercise Files saved. I'll go to Open, and here's our Exercise Files, Chapter 2. Go ahead and double-click on the 02_01 file. Here's our document. Now, in this document we have five worksheets, Company, and then a separate worksheet for quarters one, two, three, and four.
Let's first use our name box to find what named ranges we have and if we have any tables in our document. The name box is just above your A and B columns. I'll go ahead and click the drop-down menu, and this shows me all of the named ranges. I have one called Other, Quarter 1, Quarter 2, and TotalSales, and here's my table that I have, tblQ4. Let's go ahead and start with our named range called Quarter 1. I'll click on it to select it, and it takes me right to my Quarter 1 worksheet, and highlights all of the revenue and expenses for Quarter 1.
I'll go back to the name box, and this time, let's find our table, tblQ4. Took me to the Quarter 4 worksheet, and it selected that entire table, so if on the exam you're asked to find a specific table, and then maybe apply some formatting to that table, we would use the name box to find the table. We could select it, we can go to our Table Tools Design tab, and apply a different style, or add the Total Row.
Those are two things that you might be asked to do. Insert the Total Row, apply a specific style. Now, let's look at our other tool we have available to us. We're gonna go to our Home tab, and that's the Find & Select tool. I'm gonna go ahead and click the drop-down menu. These are all of the elements in a workbook that we can find using Find & Select. Formulas, Comments, Conditional Formatting, Constants, and Data Validation. Let's go ahead and go to our Quarter 2 worksheet.
On the Quarter 2 worksheet, we have some conditional formatting in our document. We can see that the cells have icons based on their value. Now, if I go to Find & Select, and go to Conditional Formatting, look how it highlights that entire area. On the exam, you may be asked to apply formatting to cells that have conditional formatting. So in this case, I just want to put a simple box around the conditionally formatted cells.
I'm gonna go to my Borders menu, and let's apply a color. Let's go down to Line Color and let's do green. Now, notice your standard colors are green, and blue, and purple, and red, and the colors above are the Theme Colors, so if it's just a simple green, orange, or red that they ask you to do, you're gonna use the Standard Colors. If you see something that looks more like Olive Green, Accent 3, Lighter 60%, then that's gonna tell you that you need to go up to the Theme Colors.
Most of the time, they're just gonna use Standard Colors, so I'm gonna choose just the standard green. So there's the color. Now I'm gonna go back and I'm going to apply a Thick Box Border. I could do an outside border, or Inside and Outside borders, but let's just do the Thick Box Border, and once I click off of it, you can see my green thick border. So, now you have two ways to quickly find and select cells, cells that have conditional formatting applied to them, or cells that are contained within a named range, or have been converted into a table.
During your Excel 2010 MOS exam, if you can quickly single out cells that have that formatting, or are within a name range, you'll be able to quickly perform whatever task you're asked to do with that selection without wasting any time, and that will give you extra time that you may need on other tasks that you come across in the MOS exam.
Explore the MOS certification program, its cost, and its format and walk through each Excel certification objective in detail. Get hands-on experience with free downloadable practice files and take the challenges to test your skills. The course wraps with a full-length, 50-minute practice exam.
- Preparing for the exam
- Saving Excel files in different formats
- Printing workbooks and worksheets
- Inserting headers, footers, and document properties
- Inserting, copying, and moving data
- Formatting cells and worksheets
- Creating, deleting, copying, and moving worksheets
- Splitting and arranging windows
- Creating basic and conditional logic formulas
- Creating named ranges to use in formulas
- Applying conditional formatting
- Creating charts
- Inserting and modifying SmartArt
- Filtering and sorting data
- Taking a full-length practice exam