skip the i-GuideIllinois State UniversityAdmissions at Illinois StateAcademics at Illinois StateEvents at Illinois StateMap of Illinois StateIllinois State A to Z ListingIllinois State University Accessibility Information
Center for Teaching, Learning & Technology

Microsoft Excel Gradebook Tips

Assigning Letter Grades

This Tip Sheet discusses how to use Excel to assign letter grades based either on total points or on percentages.

One of Excel's most powerful built-in functions is the set known as lookup functions: LOOKUP, HLOOKUP, and VLOOKUP. Lookup functions are useful when one wants to take a value, look it up in a table, and return another value based on the original. In this application, a teacher would use Excel to take a student's total points, look up that number (or percentage) in a grading scale table, and return the corresponding letter grade.

Each variant of the lookup function differs only in its expectation of the layout of the grading scale table. LOOKUP returns the value either from a one-row or one-column table or from an array (a table with many rows and columns). HLOOKUP looks for the value in the top row of a table and returns the value in the same column from the row you specify. Use of the HLOOKUP function requires that the grading scale table have cutoff scores in the first row and letter grades in the second row. VLOOKUP looks for the value in the left column of a table and returns the value in the same row from the column you specify. Use of the VLOOKUP function requires that the grading scale table have cutoff scores in the first column and letter grades in the second column. Both HLOOKUP and VLOOKUP require that the cutoff scores be sorted in ascending order (lower scores on the left side of a row or at the top of the column).

The following step-by-step example assumes the grading scale has cutoff scores (percentages in this case) in the first column and letter grades in the second column. So we will use the VLOOKUP function.

Let's start with the simple grade book shown below. (You may download a copy of the worksheet from our course descriptions & materials page.) While the Test Avg has been determined for each student, it remains to assign a letter grade based on that score and the Grading Scale shown in cells B12:C16.

Figure 1: A Simple Grade Book
A Simple Grade Book

 

The VLOOKUP Function

  • Click in cell D5.
  • Pull down the Insert menu and select Function… Click Lookup & Reference in the Function Category list box. Click VLOOKUP in the Function Name list box, then click OK to move to Step 2 of the Function Wizard and display the following dialog box:
Figure 2: The VLOOKUP dialog box
The VLOOKUP dialog box
  • Enter the arguments for the VLOOKUP function shown in the figure above. You can enter the arguments directly, but you are better off using the mouse to point to the relevant cells. Press the OK button to complete the lookup test.
  • Click in cell D5 and point to the fill handle in the lower right-hand corner of the cell. The mouse pointer changes to a thin black crosshair.
  • Drag the fill handle over cells D6 through D9.
  • Save the workbook

 

If you have followed each step correctly, the finished worksheet should look like the one in Figure 3:

Figure 3: The Finished Grade Book
The Finished Grade Book