By Richard Rost 14 years ago
This is the full text listing of one of our handbooks. There is a lot more to this handbook. The full-color screen shots have been removed for this page. This text is simply provided so that the search engines will index the course contents. This is so any customer searching for a topic can find what class it's covered in. If you are interested in more about information about our courses, click here for our complete course listing. For details on how to purchase a handbook, visit our handbooks page.
Microsoft Excel 202
Course Handbook Supplement
By Richard Rost
PO Box 1308, Amherst NY 14226 USA
First Printing 8/2/2005
Copyright 2005 by Amicron Computing
All Rights Reserved
Welcome to the 599CD Microsoft Excel 202 Handbook. This course follows Excel 201.
This handbook is designed to be a supplement to the full 599CD video course for Microsoft Excel 202. We recommend you use this handbook to follow along with the class videos. This handbook is not meant as a stand-alone study guide.
We do recommend that you watch the course videos one time through, paying attention to the lessons covered. Follow along with the course videos using this guide. Take notes on the pages where needed. Then, watch the videos a second time, practicing the examples yourself on your computer.
Table of Contents
Table of Contents 2
Lesson 1. Review & Sheet Setup 4
Lesson 2. Named Cells 7
Lesson 3. Named Ranges, part one 14
Lesson 4. Named Ranges, part two 23
Lesson 5. Nested IF Functions 33
Lesson 6. VLOOKUP, part one 39
Lesson 7. VLOOKUP, part two 45
Welcome to Microsoft Excel 202, brought to you by 599CD.com. I am your instructor, Richard Rost.
Objectives for today’s class:
· Named Cells
· Named Ranges
· Nested IF Functions
Pre-Requisites: Microsoft Excel 101 through 201.
In this class we will be using Microsoft Excel XP and Windows XP for our live-action videos. However, the version of Excel and Windows you have does not matter. Most, if not all of these examples should be applicable regardless of what version of Excel you’re using.
Lesson 1. Review & Sheet Setup
In previous classes, we’ve been working with a student gradebook. Let’s quickly set up a sample gradebook – or you can work from the gradebook we’ve been using.
If you recall from the last class, I set up a subtable with our passing grade, and the actual text to go next to each student indicating whether they passed or failed.
Then, I can build my IF statement…
If you don’t remember how to do this, review Excel 201. This is very important for today’s class!
Remember when I tried to AutoFill the function down, it didn’t work…
That’s because I have to use absolute references for my formula if I’m referring to cells in a subtable that don’t move with the function (rows/columns)…
Now the formula works fine.
Save this file as Gradebook for Excel 202.
I’ll save a copy of this file to the class web site at www.599cd.com/Excel/202. If you want, you can download a copy of it there (both it’s pre- and post-class versions).
If you have the full version of the 599CD course, it will also be installed on your system in the Program Files / 599CD / Excel 202 / Help folder. Hit the HELP button from your 599CD video player to find it.
Lesson 2. Named Cells
Every cell already has a name, like A4 or G2. You can assign your own names to cells in Excel, like Subtotal or ClassAverage. These have two functions, first they make it easier to find information in your sheet. Second, they make it easier to create formulas and functions using these custom names. What makes more sense to you: A2+A3 or Subtotal+SalesTax. I would like to be able to refer to cell A8 as PassingGrade. First click on the cell. Then click on Insert > Name > Define from the menubar.
The Define Name box opens. This box allows you to define your custom cell names. Notice on the bottom, it already has the cell listed in the Refers To box that you clicked on beforehand (this saves you some work).
Let’s type the name of this cell – or the name we want to give it…
You cannot use punctuation or spaces. You can use the underscore if you want to: like Passing_Grade. However I prefer just to use no punctuation and capitalize the first letter of each word. Click OK. Now you can see the name in your Name Box.
Now, if you happen to be sitting over in some far-off cell, say F9, you can just drop down the Name Box and then click on the PassingGrade…
That will jump to the cell indicated by PassingGrade. This doesn’t seem like a big deal here, but if you’ve got a huge spreadsheet with lots of different named cells, you can use this trick to quickly jump between them.
Likewise, even if you’re on a different sheet, you can still do this trick. Named cells work across the entire workbook, not just a single sheet. The drawback of this, of course, is that you can’t have two SubTotal cells on two different sheets (we’ll see how to work around this in a future lesson).
The real benefit of this, however, is that you can now refer to this cell as PassingGrade in your formulas. Edit your IF function as follows:
Notice how the cell works fine once you press ENTER.
Now, go ahead and AutoFill that formula down, and notice how it works fine for all of the cells. Why? Because PassingGrade is the same no matter where you happen to be on the sheet.
Let’s set up another Named Cell. Here’s a new technique. Click on cell A9. Now, just go up to the Name Box and type in “PassingText” and press ENTER. (Very important that you remember to press ENTER when you’re done).
Now drop the Name Box down and you’ll see that there are two defined names in there.
You can click on either one to jump to its location.
Tip: For those of you who are keyboard freaks (like me), CTRL-F3 brings up the Define Name box.
Now that we have PassingText defined, we can change our IF function again…
Now, if you need to specify a different cell than the one you have selected to define your cell, just press the Collapse Dialog button, click on the cell you want, and then Expand the dialog. We saw how this worked when we did Page Setup.
Go ahead and define FailingText as cell A10.
Re-expand the dialog and you’ll see the correct value in the Refers To box.
Now, let’s update our IF function. AutoFill it down.
You can set up named cells anywhere you want. For example, I can make cell B6 named Test1Avg.
I’ll also set up Test2Avg, Test3Avg, FinalAvg, and ClassAvg. The benefit? Let’s say somewhere in your sheet you want to calculate half of the class average. You can type the formula as: =FinalAvg/2 instead of having to remember that the final average is in cell E6.
Tip: A note on naming your cells… remember that you cannot create named cells that could be possible real cell addresses. For example, if you’ve got a sheet with the quarters of 2005, you couldn’t create a named cell called FQ2005. That’s a real cell location! Type it in to the name box and see for yourself. Excel will jump you to that location. Just remember, CTRL-HOME will bring you back to the top of the sheet.
Lesson 3. Named Ranges, part one
Like naming individual cells, you can also name ranges of cells. For example, highlight B2:B5. In the name box, type in Test1Grades.
You’ve now set up a named range. That named range can be substituted anywhere you would normally type in that range of cells. I’ll insert some blank rows after my Average row. Let’s say we want to calculate the SUM of the test 1 grades. I could now type in my formula as:
Note, however that you wouldn’t do this if you were planning on AutoFilling the formula across. When you do, every column will have the Test1Grades in it – which is not what we’d want.
If you want, you can name each range in your sheet… for example, Test2Grades, Test3Grades, etc.
Now you can update your other Average functions (if you want to – you don’t have to follow this with me). Here’s a nifty trick… highlight the range you’re planning to change in the Test 2 Average function…
Then we can insert the named range in by clicking on Insert > Name > Paste.
You get the Paste Name dialog up. You also get this window if you hit F3 in your keyboard. Simply select the name you want to paste in and hit OK.
I’ll pick Test2Grades and hit OK.
I’ll get rid of that SUM row I inserted – that was only for demonstration.
Let’s name this sheet tab Gradebook.
Let’s come over to Sheet2 for another example of named ranges. Set up the following Income and Expenses sheet. It’s real simple – should only take you a minute:
Now we can set up our named ranges. Highlight B5:D7 and name that range Income.
Likewise, I’ll set up B9:D11 as Expenses.
Now, you can create a Net Profit cell. Instead of having to type in the ranges, you can use your named ranges.
For example, your net profit would be: =Sum(Income)-Sum(Expenses)
Here’s a cute Excel tip… in case you never noticed this, if you highlight a range of cells, Excel automatically just shows you the SUM of the range down on the status bar.
Let’s create some more ranges… I’ll highlight B5:D5 and call that range PCSales.
I’ll create a Service range which is B6:D6. Also, let’s make Training equal to B7:D7.
Let’s change our Expenses values so they’re negative numbers (they’re really dollars OUT, aren’t they?) This way we can just add the values together. Watch this… Change all of the expenses to negative numbers, and change the net profit formula so it’s Income + Expenses.
While we’re at it, set up the named expense ranges too: Rent (B9:D9), Payroll (B10:D10), and Utilities (B11:D11).
Now, let’s set up B5:B11 as the named range January.
Do the same for February (C5:C11) and March (D5:D11).
If you drop down the Name Box you’ll see all of the different ranges we have available. And remember, if you click on one, it will jump you to that range.
I’m going to delete the Net Profit cells. We don’t really need them. Now you can write formulas like this… let’s say you want to see the net profit/loss for January. Just type in: =Sum(January)
Here’s a new concept… Range Intersections. Let’s say you want to see January’s Rent. Type in:
Yes, there is a SPACE between January and Rent.
Excel will then provide you with the intersection (where they meet) of the January range and the Rent range. Let’s say you want to see March’s PCSales…
Lesson 4. Named Ranges, part two
Sometimes you goof when creating named ranges. Let’s say you set up April wrong… you accidentally had D5:E11 highlighted and set the name April in the name box.
To fix it, click on Insert > Name > Define.
You’ll get your Define Name dialog. Find April. Click on the Collapse Dialog button.
Highlight the proper range (E5:E11 in this case).
Now expand the dialog again and hit OK. Now, notice your April range is now correct.
You can also create new ranges based on existing ones. For example, if you open up the Define Name dialog (Insert > Name > Define). Click on April. Now change the name to April2. Hit OK.
Now, notice your name box has April and April2 in it.
You can bring up that Define dialog box and Delete names from it. Just click on the April2, for example, and click on Delete.
Here’s a cool tip that’s really kind of trivial… I don’t use it a lot, but you might. Go to a blank sheet, like Sheet3. Click on Insert > Name > Paste.
Click on the Paste List button.
This will paste in a big long list of all of the named ranges and cells in your workbook. This is handy if you are going to be printing your sheets out – perhaps with the formulas showing up in them - remember that trick from one of the early Excel classes… hit CTRL-SINGLE-LEFT-QUOTE to show the sheet with the formulas? This will allow someone else (or you!) to follow along with your named ranges on the sheet.
I’m actually going to UNDO all of that. J
Let’s instead set up this small little sales chart for three employees on Sheet3…
Note: make your column headings exactly the same as mine (you’ll see why in just a minute). Make sure you’ve got Q1Sales, Q2Sales, and so on.
Now, highlight the whole block from A1 to E4.
Now click on Insert > Name > Create.
Now you get the Create Names dialog. This will allow you to automatically create named ranges without having to do it yourself! Neat, huh? You can select whether you want to use the top row and/or the left column, and/or the bottom row, and/or the right column to get your names. Let’s use the top row (our column headers) and the left column (or row headers).
Now, hit OK. Drop the Name Box down and see all of the new named ranges… Q1Sales, Q2Sales, etc.
Click on Q2Sales and notice that it jumps to that range for you.
Notice there’s also a range for each of our sales reps…
And remember, if you want to figure out Mark’s 2nd Quarter sales, it’s real easy:
Let’s come back to our Gradebook sheet and set up named ranges for each of our students:
Chris is B2:E2, Alex is B3:E3, and so on. Now, notice that my student’s averages are already set with the old-style ranges.
I don’t want to go and have to retype them all. I would just like to be able to apply the new named ranges I’ve already set up to the old ranges that are already in use throughout the sheet. Watch this trick: click on Insert > Name > Apply.
Excel will bring up the Apply Names dialog. Whatever ranges Excel finds on the sheet should be highlighted. If they’re not, just click on them yourself. When done, click on OK and all of the ranges in your sheet will be changed to the named ranges you’ve selected.
Now, notice how Excel went through your sheet and changed the old-style ranges to the new named ranges.
Lesson 5. Nested IF Functions
You can nest IF functions inside of other IF functions to allow Excel to decide between more than 2 options. IF by itself gives you a true or false answer…
=IF (expression, TRUE, FALSE)
However, if you want to have more than two possible outcomes, you can use more than one IF function…
=IF (expression1, TRUE1, IF (expression2, TRUE2, FALSE2))
So, if you follow along with me… Excel will evaluate expression1. If it’s true, the value TRUE1 will be used. If it’s false, Excel will then evaluate expression2. If that is true, TRUE2 will be used, otherwise FALSE2 will be used. You can even nest more than two IF functions, as you’ll see in this lesson…
For this example, we want to compare the number of tests the student has taken with the total possible number of tests. If the student has taken all of her tests, give her a pass/fail grade. Otherwise, give her an incomplete. We’ll need to check two conditions to do this. We can use nested IF functions.
Begin by setting up a cell to track how many tests the students are supposed to take. In cell A11, put the number 4, followed by “Number of tests” in cell B11. Let’s also name cell A11 as NumberOfTests.
Let’s create a new column to display how many tests this student has taken. In column H, I’ll use the COUNT function to count the number of tests.
Remember, if I use the named range that Excel gives me and I AutoFill down, I get Count(Chris) in each of the cells. That’s not good for this example.
That’s why it’s sometimes handy just to type in the formula yourself…
AutoFill it down, and everything works (notice to demonstrate how this works, I’ve deleted the final exam grade for one of my students).
Now we can compare the number of tests the student has taken with the number of tests he’s supposed to have taken. I can now modify my IF function to take this into account. It’s long and scary, but don’t worry, we’ll break it down…
OK, here it is:
= IF ( H2 <> NumberOfTests, “Incomplete”, IF ( F2 >= PassingGrade, PassingText, FailingText ) )
“If the number of tests this student has taken is not equal to the number of tests he’s supposed to have taken, then put an ‘Incomplete’ in this cell otherwise if his average is greater than or equal to the passing grade, give him the passing text, otherwise give him the failing text.”
Note that <> means “is not equal to.”
Now, I could have also written the formula like this:
= IF ( H2 = NumberOfTests, IF ( F2 >= PassingGrade, PassingText, FailingText ) , “Incomplete”)
Which would have read, in English:
“If the number of tests this student has taken is equal to the number of tests he’s supposed to have taken, then check to see if his grade is higher than the passing grade. If so, give him the passing text, otherwise give him the failing text. If he has not taken the proper number of tests, give him an incomplete.”
You can manipulate these functions in a number of ways, but if you take the time to look at them, you’ll see how easy they really are to comprehend.
Finish the formula on your worksheet and AutoFill it down…
Notice we have one incomplete… he is missing a test.
In the video, I break it down like this… if we only had one IF function, we would have…
=IF (H3<>T, “Incomplete”, “P/F”)
Which is saying if the number of tests is not correct, give an incomplete, otherwise, give the pass/fail grade. However, that pass/fail grade requires another IF function to evaluate.
Rick’s Tip: yes, we could have solved this problem using multiple columns – that’s the easy way and it would have prevented me from showing you how to nest IF functions. Nesting IF functions can come in handy if you know how to do them properly.
If you think of the formula like this:
=IF (H3<>T, “Incomplete”, XXXXXXXX)
You need to replace that unknown “XXXXX” with a completely new function…
=IF (H3<>T, “Incomplete”, IF(Logic, Pass, Fail) )
Now, notice that if this person later takes his test, he gets a passing grade…
If you give the student a grade of zero (0), then Excel will not treat it as an incomplete… incompletes have to be missing values.
Now, I’m going to show you how to use nested IF functions to give the students letter grades (A, B, C, D, F). I will also tell you that this is NOT the best way to do this. We will learn a better technique later on today with the VLOOKUP function… but this should give you a good appreciation for how nested IF functions work.
I’m going to hide my TestsTaken column (resize the width to zero). In column I let’s put the letter grade…
Here’s the formula:
=IF(F2>=90, “A”, IF(F2>=80, “B”, IF(F2>=70, “C”, IF(F2>=65, “D”, “F”))))
If the grade is 90 or above, give her an “A”, otherwise if the grade is 80 or above, give her a “B”, otherwise if it’s 70 or above, give her a “C”, otherwise if it’s 65 or above, give her a “D”, otherwise, give her an “F”.
AutoFill the formula down…
Yes, you could also give the student an “I” if you wanted to make your calculations even more complex… you could add one more nested level in there to check the number of tests as well.
Have fun if you want to try it! J
Lesson 6. VLOOKUP, part one
Now that we learned how to assign the letter grades using nested IF functions, let’s delete them and learn something better.
Let’s move our little yellow subtable with the passing grade and the other numbers on it to a new sheet. I’m going to insert a brand new blank worksheet. I’ll name this new sheet Summary to move all of my summary data and subtables to. I’ll cut out that little yellow subtable…
And paste it into my Summary sheet… just so it’s out of the way for now.
Now I’ve got room on the bottom of my Gradebook to set up the lookup table for my letter grades. How does this work? We basically just make a little list of each letter grade along with the corresponding number value for that grade. We tell Excel to look up the value in the list and return the correct letter. It’s real easy once you know how to do it.
Let’s type in the following little subtable starting at cell A8 in our Gradebook sheet:
That’s all. Nothing fancy. Well, you can make it a little fancy (a little formatting never hurts).
Now it looks much nicer (remember, if it looks good, subconsciously it’s easier to use!)
Now, instead of having to remember to refer to the exact location of this lookup table in all of my functions, I want to set up a named range to identify this table. Let’s set up the named range LookupGrades as A9:B13. Note that I do not want to include the little header row in my named range.
Now, it doesn’t matter where in our workbook that lookup table is! We can refer to it by name, instead of having to remember A9:B13. We can just call it LookupGrades.
I’m going to slide it over to the right a little bit so it’s near my Letter column (column I on my sheet) so you can see it while I’m working (I have a little tiny window to work with in my videos).
OK, here comes the VLOOKUP function. VLOOKUP wants three pieces of information… first it wants to know what value you’re looking up. We’re looking up our student’s Average, so that’s cell F2 for the first student.
Next, VLOOKUP wants to know where your table_array is… in other words, where’s your lookup table? Just highlight your LookupGrades table (or type it in).
Finally, VLOOKUP wants to know which column of that little lookup table you want to bring back and display. Notice our lookup table has 2 columns. In the first column is the grade. The second column has the letter. We want to bring back column 2 and display it.
Close your parentheses and hit ENTER. AutoFill it down.
Notice how everyone should have the correct letter grades.
For now, I’m also going to hide the Pass/Fail column We don’t need to see that right now.
CAUTION: Make sure your lookup table goes from LOW to HIGH values. You want the smallest values at the TOP of your list. That’s just how VLOOKUP wants them. If we had 90 at the top and 0 at the bottom, it wouldn’t work right.
Let’s look at another example. Let’s go back to Sheet3 with our sales reps. In column F calculate the sum of each rep’s sales.
Let’s say that each rep’s commission rate is based on their overall sales (if you sell more, you get a higher percentage). I’ll adjust their sales figures so one of them has a lot of sales and one has very little. Let’s create our lookup table. I’ll name the range ComTable for my commission table.
Lesson 7. VLOOKUP, part two
We’re continuing with our VLOOKUP table for the sales commission table. Here’s the VLOOKUP function…
= VLOOKUP ( F2, ComTable, 2)
In English, this says: “Take the value in F2 – which is the sum of my sales – and look it up in the ComTable lookup table. Bring back column 2, and that will give you my commission rate.”
For this particular sales rep, he gets a 4% commission rate.
I’ll AutoFill that down and click on the Percent Style format button.
With VLOOKUP you can also use multiple columns. Let’s add a third column to the lookup table to indicate a bonus in addition to the commission rate.
Now we need to edit our named range that indicates where the ComTable is located. Click on Insert > Name > Define. Click on the ComTable item and click on the Collapse Dialog button next to the Refers To section.
Highlight the new range for your ComTable. Then, expand the dialog again.
Hit OK. Now if you pick ComTable from your name box, the correct range should be highlighted. Now, let’s add the column for the bonus. We’re still using the VLOOKUP function – same as before – but this time we want to return column 3 of the subtable.
= VLOOKUP ( F2, ComTable, 3)
And you can use this technique for just about as many columns as you want (in fact, you can apply this technique across entire sheets).
I’ll Autofill that down, and format the values as currency.
Now we can calculate their actual commission: sum of sales multiplied by their commission rate plus their bonus.
HLOOKUP works the same as VLOOKUP, but the lookup table is horizontal instead of vertical. Set up the following simple invoice in a brand new sheet:
Now, let’s say we want to give the customer a discount based on the sum of his order. A $50 order gets you a 10% discount, and a $500 order gets you a 20% discount.
Now, let’s do something new… instead of actually typing in the HLOOKUP function, let’s use the Function Wizard. Click on the Function Wizard button.
The Insert Function dialog appears. I’m going to pick ‘All’ from the category listing so I can see all of the functions.
Now find HLOOKUP from the list. Click OK.
The function wizard is framing the function for you. It wants you to input the data it wants in the neat little boxes. First, let’s input the Lookup_value… in other words, what value are you looking up (the order total in this case). So, click on the collapse dialog button next to the lookup value field.
Now click on the cell you want to fill in here (the order total).
Expand the dialog again. You’ll see the cell C8 in the text box. Now, do the same thing for the table array (the location of the lookup table). The row number you can just type in.
Hit OK when you’re done. You should get a .2 discount which I will then format as a percentage. I can then calculate the order total… subtract the discount times the sum from the sum itself…
There’s the total with the discount:
Now, I’ve added a small sales tax table at the bottom. Let’s say you need to figure out what tax rate to charge them based on what state they’re from. Here’s the tax chart, and I’ve also added fields in the invoice to put the sales tax and the customer’s state.
Now, here’s our HLOOKUP function:
= HLOOKUP ( B5, A17:C18, 2, FALSE)
The first item is the customer’s state. The second item is the range for our lookup table. The third item is the row of the table to bring back. The fourth item (false) says it needs to be an EXACT LOOKUP, not just the closest value.
Notice the tax rate comes in correctly if the customer is from NY, TX, or PA:
If he’s from Ontario, the table doesn’t have that value, so you get an error:
Now we can calculate the actual sales tax (total * tax rate).
And, of course, the final total is equal to the subtotal plus the tax.
Perhaps I should clean up my labels / terms a bit, eh?
· Named Cells
· Named Ranges
· Nested IF Functions
Tell us what you think. Log on to www.599cd.com/Survey and take a short survey about this course.
RICK’S NOTE: I really do enjoy getting surveys from you! Make sure you visit the web page above and fill out the survey for this class. Let me know if I’ve moved too fast, and whether or not I covered material that was helpful to you!
Take your skills check quiz at www.599cd.com/Test. If you pass, you can print out a Certificate of Completion.
What’s next? Visit www.599cd.com for our complete list of courses.
Need Help? Visit www.599cd.com/TechHelp for technical assistance.
Make sure you’re on our Mailing List. Go to www.599cd.com/MailingList for details.
Contact Us. If you have any questions, go to www.599cd.com/Contact for information on how you can contact us by phone, email, or live online chat.
Don’t forget to visit our User Message Forums online at: www.599cd.com/Forums. You can chat with our instructors, other users, and even Richard too. You can ask us all of your questions, get answers, and tell us what you thought of our class.
This course, handbook, videos, and other materials are copyright 2002, 2003, 2004, 2005 by Amicron Computing. All rights reserved. No portion of this course, handbook, videos, or other course materials may be reproduced, copied, edited, or otherwise distributed without the express written permission of Amicron Computing. Amicron Computing shall not be held liable for any errors or omissions in this document.
This document may not be used as part of a training course without express, written permission from Amicron Computing and the purchase of an Instructional License. For details, contact:
PO Box 1308
Amherst NY 14226 USA
You may want to read these articles from the 599CD News: