A teacher gradebook in Google Sheets does one thing a paper mark book never can: it tells you who has changed. A paper book shows you that Amara got 62. A spreadsheet shows you that Amara got 62 after getting 78 and 74, which is the conversation worth having on Monday. Everything below is the setup that makes that visible, and it takes an afternoon. If you would rather not build it, the Teacher Planner for Google Sheets has a gradebook already wired in.
Want it done rather than built? The Teacher Planner for Google Sheets includes a gradebook with automatic class averages, undated and reusable every year.
Shop Classroom Organisation Google Sheets Template, Teacher Class List & Seating Plan Spreadsheet with Job Rota, Supplies Inventory & Budget Tracker →Lay It Out Once, Properly
One row per pupil, one column per assessment. That is the whole structure, and the discipline is not adding anything else to it — no notes column that becomes a diary, no merged cells for tidiness. Merged cells break sorting, and sorting is half the value.
Leave row 1 for assessment names and row 2 for weightings. Freeze the top two rows and the name column (View → Freeze) so both stay visible when you scroll into November.
Initials, not full names
Most schools require pupil data to stay on school systems, and a personal gradebook in a personal Google account is exactly the grey area their policy exists to cover. Initials or admission numbers work perfectly for your own working copy and sidestep the whole question. Check your policy before you type a single surname.
The Four Formulas You Need
That is genuinely all of them.
Straight average, ignoring blanks so a missed test is not counted as zero:=IFERROR(AVERAGE(C3:H3),"")
Weighted average, with weightings in row 2:=IFERROR(SUMPRODUCT(C3:H3,$C$2:$H$2)/SUM($C$2:$H$2),"")
Class average per assessment, across the column:=IFERROR(AVERAGE(C3:C34),"")
Movement since last time — the one that earns its keep:=IF(OR(H3="",G3=""),"",H3-G3)
That last formula is the difference between a record and a tool. Sort by it and the pupils who have dropped rise to the top, regardless of whether they are high or low attaining. A pupil going 78 → 62 needs you more urgently than one steadily sitting at 55.
Making It Show You Things
Conditional formatting is where a gradebook stops being a table. Three rules cover almost everything:
| Rule | Applies to | What it surfaces |
|---|---|---|
| Less than your threshold | Average column | Who is below expectation right now |
| Less than −5 | Movement column | Who has dropped since last assessment |
| Is empty | All mark columns | Missing marks before the data drop, not after |
That third rule is quietly the most useful. Highlighting blanks means you find the assessment you never entered while you can still do something about it, rather than at 9pm the night reports are due.
Protect the columns you calculate
Data → Protect sheets and ranges, select your average, weighted and movement columns, and set them to show a warning on edit. You can still override deliberately; you just cannot type over a formula while tabbing through marks. Ask any teacher who has flattened a whole column at half past four.
Entering marks without losing your place
Two settings make a whole evening of marking less painful. Freeze the name column so you can always see whose work you are entering when you scroll right to assessment eight. And set the enter key to move right rather than down — in the sheet, that means selecting the row range first, then tabbing across; Sheets wraps to the start of the next row automatically when you reach the end of the selection.
Enter marks in the order the books are stacked, not in register order. Sorting is free and instant afterwards; reshuffling thirty exercise books to match a spreadsheet is not. This is the single biggest time saving in the whole system, and it costs nothing to adopt.
What it cannot do for you
A spreadsheet will happily calculate an average of things that should never have been averaged. A reading age, a piece of extended writing and a low-stakes recall quiz are not the same kind of number, and a mean across them produces a figure with no real meaning attached to it.
Keep assessments of genuinely different types in separate blocks with their own averages, rather than one grand total. The column that matters is usually the trend within a type, not a composite across all of them. That is a judgement the sheet cannot make for you — it will produce a number either way, and only you know whether that number means anything.
Shop Student Assignment Tracker Google Sheets, Homework Planner Spreadsheet with Deadline Countdown, Exam & Grade Tracker, Academic Organizer →What to Do With It on Monday
Sort by the movement column, descending. Look at the bottom five. That is your intervention list, and it took four seconds to produce.
Then sort by average and look at the middle, not the ends. The top and bottom of a class are usually well known; it is the quiet middle where a slide goes unnoticed for a term. A spreadsheet is the only practical way to see that group as a group.
Before parents' evening, sort alphabetically again and read across each row — three assessments and a direction of travel gives you something specific to say about every child, which is far better than a general impression.
Where It Fits With Everything Else
The gradebook is the marking half of the job. Our guide to a teacher planner in Google Sheets covers lesson planning and the wider week, and the printable teacher gradebook printable is the paper version if you would rather not have a laptop open while marking.
For the room rather than the marks, classroom organisation ideas covers setup, and the Classroom Organisation OS holds your class list, seating and supplies in the same place — with the same names, so you type them once. Secondary teachers tracking coursework deadlines alongside marks may find the student assignment tracker a better fit for that half.
Shop Teacher Planner Google Sheets, Digital Lesson Planner Spreadsheet, Gradebook with Class Averages, Student Tracker, Undated & Reusable →Gradebook FAQs
How do I calculate a class average in Google Sheets?
Use AVERAGE across the row or column of marks, wrapped in IFERROR so blanks do not break it: =IFERROR(AVERAGE(C2:H2),""). Google Sheets ignores empty cells automatically, so a pupil who missed one assessment is not scored zero for it.
How do I weight assessments differently?
Put your weightings in their own row, then use SUMPRODUCT: =SUMPRODUCT(C2:H2,$C$1:$H$1)/SUM($C$1:$H$1). That way an end-of-unit test can count for 40 per cent and a quick quiz for 10, and you change the weighting in one place rather than rewriting formulas.
Can a spreadsheet flag pupils who are falling behind?
Yes, and it is the main reason to use one. Conditional formatting on the average column will colour anyone below your threshold, and a simple subtraction between this term's average and last term's shows who has dropped rather than who is simply low.
Should I put pupil names in a cloud spreadsheet?
Check your school's data-protection policy first — many require pupil data to stay on school systems. Initials or admission numbers work perfectly well for a personal working gradebook, and avoid the question entirely.
How do I stop myself breaking the formulas?
Protect the calculated columns. Data → Protect sheets and ranges, select the average and flag columns, and set them to warn on edit. You will still be able to change them deliberately, but not by accident while entering marks.
Is a spreadsheet better than the school's system?
Not better, different. The school system is the record of truth for reporting. A personal gradebook is a working tool — you can sort, filter and model "what if they get 70 on the next one" in ways most school platforms will not let you.
Set it up before the first data drop. Open the Teacher Planner for Google Sheets, put your class in once, and let the averages look after themselves — or browse the full Google Sheets collection.