Excel templates / Gradebook
Gradebook template with weighted grades
Weighted scores, letter grades from a grade table, class rank, and class averages, highs, lows and pass rate.
Download the gradebook template (.xlsx). Free, no sign-up. Works in Excel 2010 and later, Excel for Mac, Excel on the web, Google Sheets and LibreOffice.
How to use it
- Set how much each assessment counts in
B4toE4. They should add up to 100% (F4checks). - Type the students’ names and scores (out of 100) in the yellow cells. Leave a score empty if it isn’t in yet.
- Change the grade bands in
J6toK10to match your school: each row is the lowest percentage for that grade, smallest first.
How it works
F6:F17=IF(COUNT(B6:E6)=0,"",SUMPRODUCT(B6:E6,$B$4:$E$4)/SUM($B$4:$E$4)): The weighted score: SUMPRODUCT multiplies each score by its weight and adds them up, then dividing by the total weight gives a percentage. The $ signs keep the weights fixed as it’s copied down.G6:G17=IF(F6="","",VLOOKUP(F6,$J$6:$K$10,2,TRUE)): The letter grade. VLOOKUP with TRUE finds the band the score falls in: the last “From %” that isn’t more than the score.H6:H17=IF(F6="","",RANK(F6,$F$6:$F$17)): Each student’s place in the class, 1 for the highest weighted score.B19:F19=AVERAGE(B6:B17): The class average for each column. MAX and MIN below give the highest and lowest.F22=COUNTIF(F6:F17,">=60")/COUNT(F6:F17): The pass rate: students with 60% or more, divided by the number of students with a score.
Learn the skills behind it
- SUMPRODUCT
- Approximate match for tiers and bands
- RANK, LARGE and SMALL
- AVERAGE, MIN and MAX
- COUNTIF
- Absolute references with $
Functions used: SUMPRODUCT, VLOOKUP, RANK, AVERAGE, MAX, MIN, COUNTIF, COUNT, IF, SUM.