Using Excel to Manage Grades | Tips & Shortcuts
Sections 1-4: Getting Started | Sections 5-6: Range Names & VLOOKUP
Sections 7-9: Averaging | Sections 10-12: Post Averaging
6. Entering the VLOOKUP Function
In Excel, you can use Range Names to describe a cell or a group of related cells instead of stating their physical location (address) within the spreadsheet. This makes the formulas you will enter much more readable. Range Names are especially handy when moving data from one worksheet to another.
You will need a number of Range Names:
For the MasterList worksheet create a Range Name for the ID column (include the Column Heading Row).
For each exam create a Range Name for the entire exam (including the Column Heading Row, all IDs, names, and scores).
How to Define a Range Name:
The name must start with a letter or an underscore character. You can use letters, numbers, or special characters except spaces and hyphens. It can be up to 255 characters long, but as a rule, the shorter the better. Examples of Range Names are OvID, AllTest2, Project1, Qz4, etc. (The usual minimum length is 3.)
Highlight the cell or area you want to name. You can use the mouse to highlight the entire area or just highlight the first row (include the column headings) then use the shortcut combination of Shift + Ctrl + DownArrowKey. This should highlight to the bottom of the data.
Shortcut: You can use the Name Box to assign the Range Name. (The Name Box is located directly above the "A" for Column A. If you touch it will your mouse it will say "Name Box". It will have a cell address in it.) Highlight the area for the range then highlight the cell address that is showing in the Name Box and type over it.
Hit the Enter/Return key. Make sure that you do this.
Dialog Box Method: Click Insert on the Menu, click Name, then click Define. A Dialog box will appear. It may have a name already in it but you can type over it. Hit the Enter/Return key.
How to Check to See If the Name Has Been Correctly Assigned:
In the Name Box (it is directly below the Font Name Box) click on the scroll down button then highlight the Range Name you want to check. The area represented by the Range Name should highlight. Scroll down the file to make sure that all the information you wanted to include is highlighted.
How to Delete a Range Name:
Click Insert on the Menu, click Name, then click Define. A Dialog box will appear. Highlight the Range Name, then click Delete. Click Close.
What the VLOOKUP Function Does:
VLOOKUP is one of a two functions that "looks up" values in a table. This value is compared to a value in an array. Based on whether you are looking for a match or not the function decides to retrieve a value from the array. The "V" in VLOOKUP means that the lookup values must be in first column (V=vertical). This is why IDs must go there.
This function will be used to check the IDs of the students who took an exam against the MasterList column of IDs. For our purposes we want the IDs in the 2 worksheets to match, if so, then the score from the exam is copied to the MasterList worksheet.
How to Use the VLOOKUP Function:
The function is typed into the blank cell of the MasterList worksheet for that exam. For example, if the columns in your MasterList worksheet are:
Column A = IDs
Column B = Student Last Name
Column C = Student Initials
Column D = Exam 1 Percents
Column E = Exam 2 Percents
then, if you want to fill in the grades for Exam 1 (they are located in the worksheet named Exam1) you would put the cursor in column D Cell 2, since cell 1 should have the column header in it. Each part of the function is explained in detail below. Here is an example of what you would type in:
=vlookup(OVID,'Exam1'!T1,4,false)
OVID is the Range Name for the ID on the MasterList worksheet.
'Exam1'! is the worksheet where the scores are located. Since all worksheet names are considered text they are put between quote (') marks. The exclamation mark (!) is placed after the worksheet name. Note: There is no comma between the worksheet name and the Range Name that follows.
T1 refers to the Range Name for the data on the Exam 1 worksheet. The range should include all IDs, names, and scores. Include the Column Heading row in the range also.
4 tells the program to move the data from the 4th column (D) of the Exam 1 worksheet to the cell where you are writing the function.
False says to move the data only if the ID of the MasterList worksheet and the ID of Exam 1 matches exactly.
If you have typed in the function correctly and all the arguments (such as the Range Names) have been defined when you hit the Enter/Return key you should see a number or #N/A instead of the formula.
If you get a number, check this against the score of the student for Exam 1. If it is the same then your function is working correctly.
If the value is #N/A (meaning number not available) check to see if the student took Exam 1 (he shouldn't be in the list). Find a student who did take Exam 1. Try the formula for him. If the number is correct then the function is working correctly. If you do not get the number then your function is wrong.
Copying a Function from a Cell to Other Cells Using the Fill Handle:
If the function works you will want to copy it down the column into each cell. Excel has an easy way of doing this.
Highlight the cell that has the function in it. (Even though it has a score in it you can see the function in the formula line on the Toolbar.) Move the mouse pointer to the lower right of the cell border. (This spot is called a Fill Handle.) You will see the mouse pointer change to a thin cross. Click and drag the mouse pointer down until you have highlighted all the cells you need. Release the pointer. The cells will be filled with scores. If you need to fill more cells in the column just click on any cell with a score--all the cells have a formula in them.
Function Error Messages:
If the function did not work you may get an error message such as:
#NAME means that the function is wrong or wasn't typed in correctly. Usually this means that a Range Name was used in the function but hadn't been defined. See the section under Range Names for a method of checking this.
An Open File Dialog box appears. This means that the filename (or worksheet name) is incorrect. 'Test1' is not the same as 'Test 1'. It could be that the name is typed in correctly but you forgot the quote marks or an exclamation mark.
#REF! means that a cell or range reference is incorrect. It could be because you have deleted some of the data needed for the function or have entered the wrong column number in the function.
#VALUE! means that the column number you used in the function is less than 1. Usually this will mean that you have your arguments for the function in the wrong order.
CAUTION:
Do not use the insert command on the menu bar to add rows at the top of your masterlist after you have used the vlookup function. If you do the ID and the grades will no longer match. Use the insert paste method described in section 12 instead.