Cellwise Try it free

Excel guide · 4 min read

How to make a gradebook in Excel

Quick answerPut students in rows, assignments in columns and each assignment's weight in a row above the scores. Then the final percentage is =SUMPRODUCT(B3:F3,$B$2:$F$2)/SUM($B$2:$F$2), and a small cutoff table with =VLOOKUP(score,$H$2:$I$6,2,TRUE) turns it into a letter grade.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEFGHI
1StudentQuiz 1Quiz 2Quiz 3MidtermFinalMin %Letter
2Weight0.10.10.10.30.40F
3Jordan Ellis827590788460D
4Priya Sharma958892919470C
5Marcus Bell6071655880B
6Hannah Cole705585807690A
7Tomás Rivera8892798590
8Lena Fischer4562505861

Lay out the sheet

A gradebook only stays manageable if the layout is boring and consistent. One row per student, one column per assignment, scores as percentages out of 100, and a Weight row directly above the scores.

  1. Type Student in A1 and each assignment name across row 1.
  2. In row 2, enter each weight as a decimal or percentage (10% for each quiz, 30% midterm, 40% final). They should add up to 100%.
  3. Enter scores from row 3 down. If a quiz is out of 20, convert it: 16 out of 20 is 80.
  4. Select B3 and choose View > Freeze Panes > Freeze Panes, so names and headings stay visible.

Simple average vs weighted grade

AVERAGE treats every assignment equally, so a 10% quiz counts as much as a 40% final. For Jordan the two methods give 81.8 and 81.7, but they drift apart for students whose quiz and exam scores differ a lot.

SUMPRODUCT multiplies each score by its weight and adds the results. Dividing by the sum of the weights keeps the grade correct even if the weights do not total exactly 100%. Lock the weight row with $ so the formula copies down.

=SUMPRODUCT(B3:F3,$B$2:$F$2)/SUM($B$2:$F$2)

Result: 81.7

Jordan's weighted grade is 81.7.

Drop the lowest quiz

Take the quiz total, subtract the lowest quiz with MIN, and divide by one fewer quiz. Jordan's 75 on Quiz 2 is dropped, leaving (82 + 90) / 2.

Use COUNT rather than a fixed 2 in the divisor, so the formula still works when a student has missed a quiz. To drop the two lowest, subtract SMALL(B3:D3,1) and SMALL(B3:D3,2) and divide by COUNT minus 2.

=(SUM(B3:D3)-MIN(B3:D3))/(COUNT(B3:D3)-1)

Result: 86

With the lowest quiz dropped, Jordan's quiz average is 86.

Missing and excused work

Marcus has no Quiz 2 score. SUMPRODUCT treats the blank as 0, so his weighted grade is 55.8. That is right if he simply did not hand it in. If the quiz was excused, divide by the weights of the work he actually did instead, which removes Quiz 2 from the calculation entirely.

Decide the policy before you start, and enter 0 for missing work on purpose. A blank should mean excused; a 0 should mean not submitted.

=SUMPRODUCT(B5:F5,$B$2:$F$2)/SUMPRODUCT(--(B5:F5<>""),$B$2:$F$2)

Result: 62

Counting only the 90% of weight he completed, Marcus's grade is 62.0 instead of 55.8.

Letter grades from a cutoff table

Put the lowest percentage for each letter in a small table sorted from smallest to largest (H2:I6). VLOOKUP with TRUE finds the largest cutoff that is not above the score: 81.7 falls between 80 and 90, so it returns B.

A table beats a long nested IF because you can change a cutoff without touching any formula. For plus and minus grades, add rows and keep the table sorted. The letter grade formula guide covers IFS and XLOOKUP versions.

=VLOOKUP(SUMPRODUCT(B3:F3,$B$2:$F$2),$H$2:$I$6,2,TRUE)

Result: B

Jordan's 81.7 is at least 80 but below 90, so the letter is B.

Class summary

Below the students, add a row of AVERAGE, MAX and MIN for each assignment. A class average far below the others usually points to a confusing question, not a weak class. =AVERAGE(F3:F8) shows the final exam averaged 77.2.

Count students at risk without a helper column: SUMPRODUCT can build each student's weighted grade inside the test. Once you add a Weighted % column, =COUNTIF(G3:G8,"<60") is simpler. For a gradebook with all of this already built, download the free grade book template at /templates/grade-book-template/.

=SUMPRODUCT(--(B3:B8*0.1+C3:C8*0.1+D3:D8*0.1+E3:E8*0.3+F3:F8*0.4<60))

Result: 2

Marcus (55.8) and Lena (57.5) are below 60, so the count is 2.

Common mistakes

Forgetting the $ on the weight row

Without $B$2:$F$2 the weights slide down a row each time you fill the formula, and every student after the first gets a wrong grade.

Mixing raw points and percentages

A 16 out of 20 entered as 16 counts as 16%. Convert every score to a percentage, or add a row of maximum points and divide by it.

Leaving blanks with no policy

SUMPRODUCT counts a blank as 0 while AVERAGE skips it. Use 0 for missing work and the excused-work formula for blanks.

Unsorted cutoff table

VLOOKUP with TRUE needs cutoffs from smallest to largest, starting at 0. Otherwise it returns wrong letters with no error.

FAQ

How do I make a weighted gradebook in Excel?

Put the weights in a row above the scores and use =SUMPRODUCT(scores,weights)/SUM(weights) for each student, with the weight range locked by $ signs.

How do I drop the lowest score in Excel?

=(SUM(B3:D3)-MIN(B3:D3))/(COUNT(B3:D3)-1) averages the scores without the lowest one.

Does Excel have a gradebook template?

Excel's own template gallery changes over time, but you can download the free Cellwise grade book template, which has weighted grades, letter grades and a class summary built in.

Should I use AVERAGE or SUMPRODUCT for grades?

Use AVERAGE only when every assignment counts equally. As soon as quizzes, tests and exams have different weights, use SUMPRODUCT.

Functions used

Related guides

Updated