This browser does not support basic Web standards, preventing the display of our site's intended design. May we suggest that you upgrade your browser?

Microsoft Excel 2007

Gradebook: Phase 5
Assigning Grades

You can assign grades quickly and efficiently by using a Lookup table in Excel gradebook. A Lookup table contains your grading scale. Your grading worksheet can then reference the Lookup table and automatically assign appropriate grades. Excel will match number grades to letters and display the appropriate result.

NOTE: It is easy to share grade information with your students through personalized emails created with Word Mail Merge and your gradebook.

return to topEstablishing a Lookup Table

Assigning grades is easy with a Lookup table. Since the same table may be used for multiple tests, exams, and classes, it is best to use one sheet within your workbook for all Lookup tables. Perform the following steps to establish your Lookup table:

NOTE: You may repeat the following steps to create multiple grading scales for your use. If at any time you want to change the values, change the item in the table. More than two columns may be used. When using the VLOOKUP function, you can specify which columns are to be used.

  1. Open a desired Excel gradebook

  2. In your worksheet, select an unused sheet for your table by clicking a sheet tab in the lower left corner
    Sheet tabs

  3. In the new sheet, in the first column, list the possible values in ascending order (from lowest to highest)
    NOTE: The Lookup function will not properly select grades if listed from highest to lowest.

  4. In the second column, list the grades to be assigned to the values in the first column
    OPTIONAL: To round a score like 89.6 up to 90 and thus receive an "A-", enter the value .5 after the derived values in the Lookup table. See the sample Lookup table below.
    Example of grade scales

  5. Select the table of grades and values

  6. With the table selected, on the toolbar above the A column, in the Name Box text box, type the name of your grading scale
    EXAMPLE: scaleMATH110
    Example of the range name for VLOOKUP
    NOTE: Spaces are not allowed in names.

  7. Press [Enter]

return to topUsing the Lookup Table

VLOOKUP looks for a designated value from your gradebook in the lookup table. It then returns the letter grade associated with that value. Follow these steps to use VLOOKUP function in your Lookup table:

  1. Return to the grade sheet (the sheet with all the student grades)

  2. Select the cell where you want the first letter grade to appear

  3. In the selected cell, type the VLOOKUP function:
    VLOOKUP function
    The value in cell C2 of the gradebook is looked up on the table named 'scaleMath110'.
    NOTES:
    The 2 following the grading scale name does not mean column 2 on the worksheet, but rather the second column of the lookup table being used. Once found, the value in the second column of the table is returned.
    The value TRUE states that the values are in ascending order and finding the nearest value is allowed. In this example, this means a score of 82 would return a B- grade.

  4. Press [Enter]
    The grade appears in the cell.
    Example of grade assigned

Excellence. Our Measure. Our Motto. Our Goal.