Viewers: in countries Watching now:
In Excel 2010 Essential Training, Bob Flisser demonstrates the core features and tools in Excel 2010. The course introduces key Excel skills, shows how to utilize these skills with in-depth tutorials on Excel functions and spreadsheet formatting. It also covers prepping documents for printing, working with large worksheets and workbooks, collaborating with others, using Excel as a database, analyzing data, charting, and automating and customizing Excel. Exercise files are included with the course.
When you have a lot of data in a worksheet, there's a great set of functions called Lookups that can help you extract specific data. Let's take a look. We have two or sort of three related functions. One function is called VLOOKUP. V stands for vertical and you do that if you have your data arranged down by column. There's also an HLOOKUP, horizontal just in case your data are arranged across rows. Those are really the only two functions you want to deal with. They're sort of a third simply called Lookup and it'll work but it's legacy.
It's from older versions of Excel. You really don't want to use it but I just have it here because you might encounter it once or twice. Well, let's talk about the syntax of the Lookup function. Now whether its HLOOKUP or VLOOKUP, the syntax works the same and vertical is much more common. So we say =VLOOKUP and then you have a Lookup value, which is the way you're going to find the data, the key to your data area. Your data range is where you have all your data, and then you can figure out which column you want to get your data from and the true/false we will talk about in a minute. So let's take a look.
The Lookup value is something typical like a product code, maybe a Social Security Number, maybe an employee ID. It's really whatever is the key that will find your record, and if you're familiar with databases, this is what you would call a primary key. It's some unique identifier that tells us which row we want. Now, the data range is the entire data area, all of the columns that you're looking for. Now, once you have that in place, you want to find which column you are going to match. And columns are done by number.
So let's say you have Column A through F where you have all your data. Well Column A is column 1, Column F is column 6 and so on. But it doesn't have to be this way. So for example, if you start with your data in Column B, Column B is column 1 and than Column C is column 2 and so on. So try not to get confused there. Now, the true/false will let you decide, do you want an exact match for the data you're looking for or is that an approximate match? And if you put in true and you literally put the word true, true means an approximate match.
And if you put literally the word false, Excel will give you an exact match. So let's go ahead and put this to work. In this worksheet, here we have product codes, descriptions, some monthly sales, and we have some totals. What we want to do is we want a plug- in the product code here, let Excel read down the column and once it finds the product code we wanted, it'll read across and find okay here's the description and here's the total. So we are going to put our Lookup function in B4. But before we do that, we want to identify this data area.
Click anywhere inside that data area and let's select all the data. The easiest way to select all the data just press Ctrl+A. We are going to give this a range name. Now, you don't have to give it a range name, but believe me it's a lot easier to deal with. So click up here in the name box and when that becomes highlighted just type the word data and press Enter. And if you're not familiar with range names, that means you can click somewhere here, have nothing selected, click this down arrow on the name box, and when you select data that selects all your data.
You don't have to name it data. You can call it anything you want. So let's click in cell B4 and will say =VLOOKUP. Now as you are typing it, by the time you type =vlo, Excel recognizes that you want the VLOOKUP. So you can continue typing it or if you want to save yourself the trouble of typing, just press the Tab key and it will fill in for you. So the first thing we want is our Lookup value. So that's this code we were talking about that's going to look down here. So right now that's an A4 and that's the first argument. Type a comma. The second argument is the entire table that we were dealing with.
Now, since we know that it's called data, you can type it in and you see Excel even confirms it for you. But let's say you're doing several months down the road and forgot what you called it. I am just going to Backspace over here. If you don't remember what your range name is called, you could press the F3 key and in the Paste Name dialog box it'll tell you, then you could just double-click. Whichever way you like is fine. So type a comma. Now, we need to decide which column number. Well, we want to put in the description, and when you look at the data area, you see the Description is Column 2. So literally type in a 2.
Now, that's that argument so type in at comma and here's the true/false. We want an exact match so you can either type the word false or if it comes up here you can just double- click it, and that's it. In the parenthesis, I'll just press Ctrl+ Enter so I just stay there, and then we can see. Code C0123 is Cooking oil standard 8 ounces. But before we go and play with this, let's put in another one just for practice. Click here in Cell C4 and again type = VLOOKUP and again I'll just press Tab and Excel does the typing for me.
Now, the Lookup value is the same as the last one we did. It's still going to be here so Excel can find it down there. So A4 is the same. Type a comma. The table array is the same. I'll just type in the word data. We talked about that before. Type in it a comma. Now, the index number, well we are going to count it 1-2-3-4-5-6. So Total is the sixth column. So in your formula, literally type in a 6 and a comma, and just like before we want an exact match. So instead of clicking it this time, I literally type in the word false.
Close the parentheses, press Ctrl+ Enter, and now we can see C0123. Our product code is C0123. Cooking oil standard eight ounces. The total is 6153. Well, now let's change the product code in Cell A4 and see what happens. I'll type then S123 and this is not case-sensitive. Press Ctrl+Enter. Now S123 it finds, seasoning is extra origin 12 ounces, and the total is 60,192. We are going to click in here, and we will type SH124, and enter in SH124 is 6 ounce shampoo and the total is 70,098.
So if you have a lot of data and you want a quick way of querying this little database, it's hard to be the Lookup functions.
There are currently no FAQs about Excel 2010 Essential Training.
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.