Digital Skills Hub
Menu

How to Build a Gradebook in Excel With Weighted Scores

Person working on a laptop in an office
Photo via Pexels

Combine scores from quizzes, assignments and exams even when each assessment uses a different number of points.

Start with scores and possible points

Enter a learner’s quiz score of 8 out of 10, assignment score of 17 out of 20, and exam score of 36 out of 50. Put the earned scores in B2:D2 and the possible points in B3:D3. Keep the maximum points in their own row so another teacher can see how each result was calculated.

Apply the weights

Enter 20%, 30% and 50% in B4:D4. In E2 enter =IF(COUNT(B2:D2)<3,"Pending",B2/B$3*B$4+C2/C$3*C$4+D2/D$3*D$4). Format E2 as a percentage. Your three contributions should be 16, 25.5 and 36 percentage points, for a final grade of 77.5%. Check that each maximum is greater than zero and the weights add to 100%.

Decide how to handle missing scores

Leave an ungraded assessment blank. Enter zero only once you have established that zero is the actual score under your grading rules. Temporarily blank the exam cell to test the Pending message, then type zero to test the difference. The result should become 41.5%.

Add a second learner without moving the reference rows

For a working gradebook, place maximums and weights above the learners, then adjust the references before copying your formula down. Keep a small sample such as the one above to audit future changes, especially if you add a fourth assessment or change weights partway through a term.