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 2003/2004

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. At any time you wish to change the values, just 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 values possible 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: If you want the gradebook 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 right.
    Sample Grading ScaleSample Grading Scale - Rounding Example

  5. Select the entire 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: caleCS100
    NOTE: Spaces are not allowed in names.

  7. Windows: Press [Enter]
    Macintosh: Press [return]

return to topUsing the Lookup Table

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, enter the VLOOKUP function:
    The value in cell E2 is looked up on the table named 'scaleCS100'.
    NOTES: The 2 following the grading scale name does not mean column 2 on the worksheet, but rather the 2nd column of the lookup table being used. Once found, the value in the 2nd 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. Windows: Press [Enter]
    Macintosh: Press [return]
    The grade appears in the cell.

Excellence. Our Measure. Our Motto. Our Goal.