Adding Calculations to your Excel Grade Book

You can use Excel to calculate some simple statistics on your assignment grades. These statistics can then be shared with your students.  Although there are many functions you can use, we'll just look at how to calculate the mean and standard deviation for each of our assignments in this lesson.

  1. Let's first add some labels. Put your cursor in cell C7 and type "Mean". Hit the enter key and type "Std deviation" in cell C8.  Your spreadsheet should look like the one below:
    graphic illustrating adding labels to spreadsheet
  2. Now we need to type in cell D7 a formula for calculating the average (mean) grade. Put your cursor in D7 and type (without the quotation marks) "=average(D2:D5)" and hit the Enter key. This formula tells Excel to calculate the average grade for Quiz 1.
    illustration of a calculation
  3. Now put your cursor in D8 and type in the formula to calculate the standard deviation. Type "=stdev(D2:D5)". Hit the Enter key. Your spreadsheet should look like this:
    graphic showing mean and std deviation
  4. Finally, we need to COPY the formulas we created for Quiz 1, across for all of our assignments. Put your cursor in D7 and drag down to D8 to highlight those two cells.
    graphic illustrating copying a formula to multiple cells
  5. Click on Edit in the menu at the top of the page and select Copy. Click in E7 cell, hold down the cursor and drag it to I8, highlighting two rows beneath the remainder of assignments. Let go of the cursor.
    copying a formula illustration
  6. Click on Edit and then select Paste. Means and standard deviations for all of your assignments should appear:
    pasting content

Note: another quick way to copy a formula is to click on the cell that holds the formula, move your cursor over the cell until it turns to a solid plus sign (+) and then drag over the cells that you wish to copy the formula to.

Calculate total points

Once you have all your grades entered, you may use Excel to add up each of the student's total points. This score can then be used to calculate a final letter grade if you want. For this exercise, we will assume that the total possible points for all of the assignments adds up to 345 pts and that there is no weighting of assignments. You may weight assignments in Excel, but weighting will not be covered in this lesson.

  1. In the column to the right of Term Paper, type the column label Total Points. Hit the Enter key so that you are in the cell directly below the column label (J2 in this example).
  2. Type the following into J2 exactly, without the quotation marks "=d2+e2+f2+g2+h2+i2" and hit the enter key when done. This is a formula that will add all of the columns together. The total should appear in the cell J2.
    typing in formulas
  3. Now you will need to copy the formula you typed in J2 into the other remaining cells in column J. To do this, click on the cell with the formula (e.g., J2) to highlight it.
    illustration of copying the formula to other cells
  4. Then click on Edit in the menu at the top of the page and select "Copy". Click in the J3 cell, hold down the cursor and drag it to J5. Let go of the cursor. Click on Edit and then select "Paste". The new totals in the remaining cells should appear.
    copy function
Calculate Percentages

Many of instructors like to use percentages as a way to calculate final grades. Let's go ahead and do this now for the grades we have entered.

  1. To the right of Total Points, type a new column label that says Percentage.
  2. Put your cursor in the cell directly below the column label and type the following (without the quotation marks) "=j2/345*100" and hit the enter key. In this formula, the asterisk (*) represents the multiplication symbol and 345 is the total number of points the students could have obtained.
    percentages in cells
  3. Copy and paste this formula into the cells below following the same procedure we used for the other formula. Note that this second formula could have been incorporated into total points formula if you wanted. The formula would have read "=(d2+e2+f2+g2+h2+i2)/345*100".
Assign a Final Letter Grade

To assign letter grades based on these percentages, you will have to determine a grading scheme. In all fairness to your students, this scheme should be presented to them in your course syllabus at the beginning of the term.

  1. Type a new column label to the right of Percentage called "Final Grade".
  2. Based on your grading scheme, enter a letter grade for each student. Your roster should now look like this the one at the right.
    final grade

Congratulations! You now know how to use Excel to manage your grades. Be sure to save your file.

Go to top of page