Tracking student grades in Google Sheets is something a lot of teachers do, but most grade trackers people build are messier than they need to be. This guide shows you how to set up a clean, reliable grade tracker that calculates weighted averages, assigns letter grades automatically, and gives you a summary view of how the whole class is doing.
Setting Up Your Grade Book
Start with one sheet per class, or one sheet with a column for the class name if you're tracking multiple sections. The basic structure is: student name in column A, then one column per assignment or assessment. Keep row 1 as headers.
A clean structure looks like this:
Student | HW1 | HW2 | Quiz1 | Midterm | Final
Jane Park | 88 | 92 | 78 | 85 | 91
Marcus Lee | 74 | 80 | 83 | 79 | 88
Enter grades as numbers out of 100. Avoid percentages in the raw data — it's easier to calculate later.
Calculating Weighted Averages
Most grading systems weight different categories differently — homework might be 20%, quizzes 30%, midterm 20%, final 30%. Use SUMPRODUCT to calculate weighted averages without needing helper columns:
=SUMPRODUCT(B2:F2, {0.1, 0.1, 0.3, 0.2, 0.3})
The array {0.1, 0.1, 0.3, 0.2, 0.3} contains the weights for each column. Make sure the weights add up to 1. This gives each student a single weighted average in one formula.
Assigning Letter Grades
Once you have weighted averages, add a Letter Grade column using nested IFs:
=IF(G2>=90,"A",IF(G2>=80,"B",IF(G2>=70,"C",IF(G2>=60,"D","F"))))
Where G2 is the weighted average column. Copy this down for every student.
Flagging Students Who Need Attention
Use conditional formatting to highlight students below a threshold. Select the weighted average column, go to Format, Conditional formatting, and add a rule: less than 70, red fill. Add another: between 70 and 79, yellow fill. This gives you an instant visual of who's struggling.
Class Summary Stats
At the bottom of your grade book, add summary rows using standard functions:
- Class average: =AVERAGE(G2:G35)
- Highest grade: =MAX(G2:G35)
- Lowest grade: =MIN(G2:G35)
- Count below 70: =COUNTIF(G2:G35,"<70")
These give you a quick read on how the class performed overall without scrolling through every row.
Tracking Grade Distribution
To see how grades are distributed across letter grades, use COUNTIF:
=COUNTIF(H2:H35,"A")
Repeat for B, C, D, F. Put these in a small table and insert a bar chart to visualize the distribution. This is useful for spotting if an assessment was too hard or too easy.
The Easy Way: Using SheetXAI in Google Sheets
If you'd rather describe what you need than build it formula by formula, SheetXAI works directly inside Google Sheets as a sidebar.
Example 1: You already have scores in the sheet.
"I have student scores in columns B through F. Homework is 20%, quizzes 30%, midterm 20%, final 30%. Calculate weighted averages, assign letter grades, and add a class summary at the bottom."
SheetXAI reads your data, adds the weighted average formula, assigns letter grades, and builds the summary section.
Example 2: You need to pull grades from your school's system.
"Pull student assessment scores from our student information system and build a grade tracker with weighted averages and a class performance summary."
SheetXAI connects to the data source, pulls the scores, and builds the full grade book ready for review.
Try SheetXAI free and see what it builds for you.
Published May 2026. See also: How to Make a Student Summary Report in Excel, How to Calculate GPA in Excel, and Google Sheets AI Guide.
