Viewers: in countries Watching now:
Excel 2003 Essential Training with Mark Swift is a movie-based workshop for users who are new to working with spreadsheets, or those wanting to improve their skills. This workshop begins with a basic overview of the application and quickly advances to cover useful formulas, functions, techniques for enhancing spreadsheets, charts, and much more. Exercise files accompany the training, allowing you to follow along and learn at your own pace.
Now let's take a look at a bunch of powerful functions that will help you search for data in a large spreadsheet. Of course I'm using much smaller examples, but you can utilize this over any number of cells. Let's go to the Student Folder, and we can open up from the Advanced Functions folder, Lookups. Here we have three tabs, one for each function; the LOOKUP function, VLOOKUP function, and the HLOOKUP function. These three functions allow you to extract data differently and are used depending on the way your cells are laid out. For example here we have a vertical layout. We have the FROM and TO salary range, and then our taxation amount. Here in cell G5, the taxation percentage will appear when you enter a salary here. So let's start by entering a salary of $5,000. You'll notice that $5,000 has a taxation rate of 0 and we've accurately calculated that. Let's go ahead and say $12,000 dollars. $12000 falls into the $8,000-28,999 range, so we have a value of 11%. Let's look at the formula behind this function. The LOOKUP function has three arguments to its statement.
First: Where's the value that you want me to look up? In this case, it's in G4, this cell. Second: What is the range of cells you want me to look up? And that's limited to a single column. And third, what column do you want me to pull the results from? That's here with C3 through C7. When I enter my value here in G4, it's looking down column A and deciding where that value falls. It's a little bit of trickery since the LOOKUP function is always a equal to or greater than. So when I enter 12,000, although it can't find 12,000 on this list, it says well it's greater then eight, and it's less than 29, so it falls into the 11% category. Let's move on to VLOOKUP. Select the VLOOKUP tab and we'll check out this function. VLOOKUP again has three arguments. First of all, where's the information coming from? And again it's in G4. What is the range of cells that I can scan? This time you name the entire range from A3 all the way to A7. And what row am I going to be pulling my results from? Let's enter a value for salary, so we can examine the results. Let's put in $48,000, and it accurately comes up with 28%. Let's stop to examine. Here the value in G4 is being compared against the entire range. When it finds the range value, it simply moves across to row 3 and pulls out that percentage for us. Vertical LOOKUP finds the value that you've specified within the range and then moves vertically along that row to the column you've specified. If you have your data laid out in this fashion, either LOOKUP or Vertical LOOKUP will have its advantages depending on what kind of data you're trying to pull. Now let's take a look at HLOOKUP. HLOOKUP, as the name implies, is a horizontal look up.
Unlike the previous two functions we just saw, Horizontal LOOKUP takes advantage of data that's oriented differently. So if you data is laid out like this from left to right instead of top to bottom, HLOOKUP is the function for you, and if we break this down you'll see that in cell D8, where we're asking it to enter salary, we look at the entire range from C2 to G4, and we return the value that's in row 3. In VLOOKUP, it was going to column 3. In HLOOKUP, it'll go to row 3. Now if we had hundreds of rows and columns within our range of cells that we were searching for, that 3 could be 16 or 78, who knows, but in this particular case we're asking it to find a value. Let's enter a value of 36,000. So 36,000 is greater than 29 but not quite 45, so in this column it's going to pull from row 3 the value 22.25 %. Well that's LOOKUP, Vertical LOOKUP, and Horizontal LOOKUP, three very useful functions for pulling data out of a large spreadsheet.
There are currently no FAQs about Excel 2003 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.