Google Sheets Gradebook Template With Weighted Grades

The real problem

What a Google Sheets gradebook template has to get right

Most gradebooks that go wrong do not have a broken formula. They have a correct formula answering a question nobody meant to ask. The overall grade says 52 %, the student is doing fine, and the reason is a projects category that has no marks in it yet and is still being counted as worth thirty per cent of nothing.

That is why we would judge any Google Sheets gradebook template, ours or anyone’s, on three decisions before the layout. How does it treat a blank cell? Does it average the percentages or add up the points? And what does it do with a category that is still empty? Each one is a single cell’s worth of logic, and each one can move a grade by a full letter.

Layout

One tab, three header rows, one student per row

Keep the whole class on one tab per term. Students run down column A. Each assignment gets a column, and above the scores sit three header rows that do all the work:

• Row 1, category. Tests, Homework, Projects, whatever your syllabus weights. Make it a dropdown (Data, then Data validation) so a stray “Test” never starts a category of its own.
• Row 2, points possible. The out-of number for that assignment. Every formula below reads it, so you never type it twice.
• Row 3, the assignment name and date. For you, not for the maths.

Scores start in row 4, as raw points, not percentages. To the right of the scores, leave a gap column, then one column per category: put the category name in row 1 and its weight in row 2 (0.5 for fifty per cent), so the weights live in cells you can see and change once. Then an overall column and a letter column. In the formulas below the scores sit in C to H, the categories in J to L, the overall grade in M.

The formulas

Weighted grades in a Google Sheets gradebook template

Each category column works out one thing: points earned in that category divided by points possible for the work that has a score. SUMIF adds the earned points for the category named at the top of the column. SUMPRODUCT, which Google defines as the sum of the products of corresponding entries in equally sized ranges, adds the points possible, but only where a number has been entered.

The IFERROR with no second argument matters. Google documents that it returns a blank when the second argument is absent, so a category with nothing graded shows an empty cell instead of an error. The overall grade then multiplies each category by its weight and, crucially, divides by the weights that are actually in play:

Here is one fictional student three weeks into a term, weighted Tests 50 %, Homework 20 %, Projects 30 %, worked through both formulas.

The last two rows are the whole argument for the second formula. Without the division by 0.7, the empty projects category drags a C student to an F until the first project is marked, and the parent email arrives before you notice.

The same table shows the second decision. Adding points gives Tests 84.0 %, because the 100-point test outweighs the 50-point one. Averaging the two percentages (88 % and 76 %) would give 82.0 %, treating them as equal. Neither is wrong; they are different policies, and your syllabus should say which one you use. The formula above adds points. If every assignment in a category should count equally, give them all the same points possible in row 2.

Blank, zero, excused

The cell that moves a grade by a letter

Because the category formula only counts points possible where a number sits in the cell, the gradebook reads three kinds of entry three different ways:

• 0 means missing. It counts, at zero.
• Blank means not graded yet. It is left out of the category entirely.
• EX, or any text, means excused. Also left out, and it tells you why.

Take the student above and leave that missing homework blank instead of typing 0. Homework becomes 10 / 10, the overall grade becomes 88.6 %, and a C turns into a B. One empty cell did that, which is why the habit worth building is typing the zero the day the work is due, not at the end of the term.

Whether a missing piece of work should count as zero at all, or carry a floor, is your school’s policy rather than a spreadsheet question. The sheet only needs to make whichever rule you follow visible and consistent.

Letters

Turning percentages into letter grades

Do not nest five IFs. Put your scale on its own small tab, minimum percentage in column A sorted from lowest to highest, letter in column B: 0 F, 0.6 D, 0.7 C, 0.8 B, 0.9 A, or whatever your school publishes. Then look the grade up against it.

TRUE asks for an approximate match, and Google’s own help is blunt that the search column must be sorted ascending first or you may get a wrong value back. If you prefer XLOOKUP, =XLOOKUP(M4,Scale!$A$2:$A$6,Scale!$B$2:$B$6,,-1) does the same job: a match mode of -1 returns the exact match or the next smaller value. Adding a B+ later is one new row on the scale tab, not an edit to every student’s formula.

Keep it intact

Making the gradebook hard to break

• Flag who needs a conversation. Google’s own conditional formatting example is a teacher highlighting test scores below 80 %: select the overall column, Format, then Conditional formatting, “Less than”, 0.8. Pick your own threshold.
• Protect the formula columns from yourself. Data, then Protect sheets and ranges, with “Show a warning when editing this range” on the category and overall columns. A late-night paste over a formula is the commonest way a gradebook stops being right.
• Do not treat protection as privacy. Google says plainly that protected ranges should not be used as a security measure, because people can still copy and export a protected sheet. Never share the class gradebook with students or families; share their own results instead.
• Start each term on a fresh copy of the tab. Weights and scale come with it, last term stays readable, and nothing from September leaks into January’s average.

The same habits that keep an expense tracker in Google Sheets honest apply here: ranges that cover the whole class, and inputs kept apart from formulas.

Classroom

If you already use Google Classroom

Classroom has its own gradebook, and for many teachers it is enough. Its setup offers a points-based overall grade, “Weighted by category”, or no overall grade, and Google describes the weighted mode as the same arithmetic as above: a category average, multiplied by the category’s weight, added up. Work marked Excused is removed from the calculation.

Two details are why teachers still keep a sheet beside it. Google notes that grades are calculated for the life of the class, so starting fresh for a new term or semester means creating a new class. And Classroom lets you download grades for one assignment or for the whole class to Google Sheets or a CSV file, from the computer version. That export is a good way to feed a Google Sheets gradebook template without retyping scores, then add what Classroom does not hold: assessments done on paper, notes, and the term-by-term view.

Homeschool families can use the same layout with one change: subjects run down column A instead of students, and the category columns become the parts of each subject you weight.

In the room

Where the sheet stops and the planner starts

A spreadsheet is the right home for the maths and the wrong tool in the middle of a lesson. The quick marks, who answered the exit ticket, who needs a retake, tend to get written somewhere faster first and entered later. As our digital teacher planner guide argues, a planner should not try to be the official gradebook; it should be the place those marks land before they reach it.

Ours, the Digital Teacher Planner ($29), is a 142-page hyperlinked PDF with a gradebook page for each class with dated columns, beside lesson plans, a class schedule, seating charts, a parent contact log and conference notes, dated 2026 and 2027. It does not calculate anything, by design: the sheet does that. If you are still choosing a planner, our best teacher planner comparison sets it beside the paper options, and the rest of our teacher resources live together.

Paste the three formulas, decide the three rules, and test with one score in one category. Tell us in the comments how you handle missing work in your sheet, because the zero-or-blank question splits more staffrooms than any formula does.

FAQ

Common questions, answered briefly

People also ask

Other questions, briefly answered