Calculating Letter Grades in Excel 2010

As you may already know, one of the many benefits of Microsoft Excel is the ability to generate and produce calculations through the use of formulas. Instructors can easily benefit from this feature by setting up a grade book within Excel and using formulas to quickly and easily calculate student grades. In addition, they can also use a formula to automatically generate a letter grade.

The VLOOKUP function in Excel allows you to automatically calculate a letter grade for each student by utilizing a grade chart within the spreadsheet. Here’s how to put it together:

    1. Open the grade book.
    2. Create a column where the calculated letter grade will appear.
    3. Somewhere within the spreadsheet, create a separate table. In the first column, you’ll input the minimum score required for a letter grade and in the second column, you’ll put the actual letter grade. When creating the table, make sure grades are ordered from lowest to highest.
    4. Select the corresponding Grade Letter cell for the first student and click on the Function icon above the spreadsheet.
    5. In the dialog box that appears, enter VLOOKUP in the search field and click on Go.
    6. From the provided selections, click on VLOOKUP to highlight it and click on OK.
    7. In the Function Argumentsdialog box, you are required to enter three values:
      • Lookup_value: This field is the cell that contains the final grade for the student. Click on the cell to enter the correct value. (In our example grade book, we’ll start with the first student’s final grade, which is cell G6).
      • Table_array: This is the letter grade table you created in Step 3. In the spreadsheet, click and drag to highlight the entire able. Now, enter a dollar sign ($) before each letter and each number in the field (it should look like $D$17:$E$22). This will prevent the table range from changing when we later apply the formula to for other students in the spreadsheet.
      • Col_index_num: This field tells Excel which column to use in the letter table to return a value. Since we want to display the actual letter grade, enter the number 2.
    8. When all values are entered, click on OK.

You’ll now see that a letter grade has been entered in the cell you selected. To apply the formula for all other students, you can do a simple copy/paste into each cell in the Letter Grade column.


Tagged with: , , ,
Posted in General I.T. Knowledge
2 comments on “Calculating Letter Grades in Excel 2010
  1. Kelly Anderson says:

    I publish letter grades, but I use “nested ifs” to accomplish it. It might be slightly less complicated than the table look-up. You have to be careful of rounding issues but otherwise it works pretty well.

    • Stacy Divin says:

      Thanks for pointing that out, Kelly! One of the great things about Excel is the fact that there are multiple ways of accomplishing a single task. Your use of nested ifs to create letter grades is a perfect example of that. If you’d like, send an example of the formula you use to (or you could even post it here for other readers). We could work up a similar companion piece to post on the blog to demonstrate your method.

Leave a Reply

Your email address will not be published. Required fields are marked *