Softwiro
Free Excel template

Weighted Grade Calculator for Excel (Gradebook with Categories)

Most weighted-grade questions come down to the same three things: how much each category is worth, which scores get dropped, and what you need on the final. This workbook is built around exactly those. Set weights for Homework, Quizzes, Tests, Projects, Participation and a Final exam (the sheet warns you if they do not total 100%), choose how many of the lowest scores to drop in each category, decide how missing work is treated, and type scores. It gives every student a weighted grade, letter, rank and status, a class dashboard with charts, a page for one student, and a calculator for the score needed on the final. The sample class has 15 students; clear it and enter your own. A student can also use the calculator on its own by typing a current grade, the final's weight and the grade they want.

Excel file (.xlsx) · 77 KB · 10 tabs · works in Excel and Google Sheets · no sign-up

The blank copy has the same formulas, charts and lists without the example data; categories and settings are ready.

Try the sheet here, on the example

The sheet is open below with sample numbers. Change any white cell and watch the results update, or press the blank-copy button to type your own data from scratch. Nothing you type leaves your browser.

Example from the sheet

Example from the “Grades” tab, with the numbers and formulas that are in the file.
StudentHomeworkQuizzesTestsProjectsParticipationWeighted gradeFinal gradeLetterRank
Emma Johnson96.0%95.0%90.0%93.3%95.0%93.3%93.3%A5
Liam Carter78.0%75.0%73.0%74.2%80.0%75.4%75.4%C9
Olivia Bennett100.0%100.0%97.0%97.1%100.0%98.5%98.5%A2
Noah Williams56.0%61.7%58.0%62.5%65.0%60.0%61.0%D13
Ava Thompson92.0%86.7%84.0%84.2%90.0%86.8%86.8%B7
Ethan Brooks30.0%45.0%47.0%45.0%42.3%42.3%F15
Sophia Martinez98.0%95.0%92.0%91.3%100.0%94.5%94.5%A4

Example from the “Grades” tab, with the numbers and formulas that are in the file.

What is inside

  • Setup: Missing-work policy (zero, a floor percentage, or left out), extra-credit cap, passing mark, the six categories with weights and drop-lowest N, and an editable letter scale with a check that weights total 100.
  • Roster: Up to 40 students with ID, name, section and extra-credit points.
  • Scores: 25 assignment slots grouped by category (Homework 8, Quizzes 6, Tests 3, Projects 3, Participation 4, Final exam 1). Type points, M for missing, X for excused; empty means not graded yet.
  • Percents: A working sheet that turns every score into a percentage after the missing-work policy. Nothing to type.
  • Grades: Category percentages after dropping the lowest scores, the weighted grade, extra credit, final grade, letter, rank, missing items, status and the grade before the final exam.
  • Dashboard: Class average, highest and lowest, pass rate, students at risk, grade spread, category averages and the hardest assignment, with 3 charts.
  • Student view: Pick a student ID to see each category's weight, percentage and contribution, and how every assignment counted.
  • Final calculator: The score needed on the final for a target grade and for each letter cut-off, for a student in the book, plus a box where you type your own numbers.

The file opens on a start tab (“Start here”) that explains how to use it.

What makes it different

This is not a plain gradebook with a total. The weights are the point: every category has its own weight and its own drop-lowest rule, and the missing-work policy is a setting you can change in one cell instead of a formula you have to rewrite. Because the final-exam calculator reads the same data, a teacher can tell a student exactly what is needed, and a student can use the manual box without any of the gradebook.

How do you calculate a weighted grade in Excel?

Work out a percentage for each category, multiply it by the category's weight, add the results and divide by the total weight of the categories that have scores. In this sheet the category percentage is the average of the assignment percentages, so a 10-point quiz counts the same as a 50-point test inside a category.

Take Ethan Brooks in the sample. His Homework, Quizzes, Tests and Participation have scores, Projects and the Final exam do not. Only the weights of the categories with scores are used, so the missing categories do not pull his grade down: his weighted grade is 42.3% and he is flagged At risk.

How do you drop the lowest score in a category?

Convert each score to a percentage, find the smallest one or two, leave them out of the average, and divide by the number of scores that remain. Setup has a Drop lowest N column (0, 1 or 2) for each category. In the sample, Homework and Quizzes each drop 1.

The sheet never drops the only score in a category, and excused or ungraded work is not counted in the first place. Check your syllabus for your own rule on dropping scores.

What do I need on the final to get an A?

The formula is: needed = (target - current x (1 - weight)) / weight, with the final's weight as a fraction. With a current grade of 88%, a final worth 30% and a target of 90%, you need 94.7% on the final.

On the Final calculator sheet you can type those three numbers, or pick a student and read the score needed for your target and for every letter cut-off. If the answer is above 100% the target cannot be reached on the final alone. In the sample, Ethan Brooks would need 281% for a 90, so the sheet says Out of reach.

How to use it

  1. On Setup, set the missing-work policy, passing mark and extra-credit cap, then give each category a weight (they should total 100%) and how many of its lowest scores to drop. Edit the letter cut-offs if your school differs.
  2. On Roster, list your students (ID, name, section) and any extra-credit points they earned.
  3. On Scores, name each assignment and enter its points possible in the block for its category. Then type each student's points. Type M for missing work, X for excused, and leave a cell empty if the work is not graded yet.
  4. Open Grades for category percentages, the weighted grade, letter, rank and status. Open Dashboard for class average, grade spread and charts.
  5. On Student view, pick a student ID to see how every category and assignment feeds the grade.
  6. On Final calculator, pick a student (or type a grade by hand) and a target to see the score needed on the final exam.

Before you start:

  • Capacity: 40 students, 25 assignment slots (Homework 8, Quizzes 6, Tests 3, Projects 3, Participation 4, Final exam 1) and 6 categories.
  • Inside a category every assignment counts equally as a percentage (a 10-point quiz counts the same as a 50-point test), then the lowest N percentages are dropped, then categories are combined with your weights.
  • Missing work (M) follows the policy on Setup: counted as zero, counted at a floor percentage, or left out. Excused work (X) and empty cells are always left out.
  • A category with no scores yet does not pull the weighted grade down: the weights of the categories that have scores are re-balanced to 100%.
  • Extra credit is added to the final grade as percentage points, limited by the cap on Setup. A score above the points possible also works as extra credit.
  • Currency is not needed here. Give every student a different ID: Student view and the calculator look the student up by it.

Excel file (.xlsx) · 77 KB · 10 tabs · works in Excel and Google Sheets · no sign-up

The blank copy has the same formulas, charts and lists without the example data; categories and settings are ready.

Still running this on Excel?

This sheet holds the numbers of one class, typed in by one teacher. In the School Performance Management Platform, each student's grades, attendance and teacher notes come together, and reports reach parents.

In this sheetIn School Performance Management Platform
Every teacher has a sheet, and the administration collects the files.One dashboard for students, teachers and classes, with exam results tracking and a comparison of performance across terms.
A student's decline shows up in the end-of-term result.AI analyzes the student's grades, attendance and teacher notes, warns early about a possible decline, and suggests a development plan that the teacher decides on.
The parent learns about the problem late.Regular reports to parents on the student's level, teacher notes and the attendance record, plus direct contact with the school through the app.
Attendance lives in a notebook or a sheet per class.Attendance, absence and lateness are recorded for each student and turned into attendance reports and rates that the administration follows for each class.

See School Performance Management Platform · Ask for a demo

Frequently asked questions

Is this weighted gradebook free?

Yes. It is free to download, change and share. It is an ordinary Excel file that also opens in Google Sheets and LibreOffice.

What if my weights do not total 100%?

Setup and the Dashboard show a red warning when the total is not 100. The grades still calculate, because they divide by the weights in use, but you should fix the weights.

How does it treat missing work?

Type M for a missing assignment. The policy on Setup decides: Zero counts it as 0%, Floor counts it at a percentage you set, Excluded leaves it out. X (excused) and empty cells are always left out.

Can I give extra credit?

Yes, in two ways. A score above the points possible counts as more than 100% for that assignment, and extra-credit points on the Roster are added to the final grade as percentage points, up to a cap you set.

How many students and assignments does it hold?

40 students, 25 assignment slots and 6 categories per file. Use a new copy for each class or term. For an unweighted class book see the gradebook template.

Is the template free?

Yes, with no sign-up and no email address. Use it, edit it and share it as you like.

Does it work in Google Sheets and on mobile?

The sheet uses formulas supported by Excel 2016 and later, Google Sheets and LibreOffice, so you can upload it to Google Drive and open it with Google Sheets on a computer or a phone.

What is the difference between the white and the gray cells?

You type in the white cells; the gray cells are calculated automatically. Don't type in a gray cell, or you will erase its formula.

Can I use the sheet from an AI assistant?

Yes. Add our MCP server (URL https://softwiro.com/api/mcp, no key needed) to your assistant and ask it to do the calculation in this template. The numbers come from the sheet's own formulas, and we do not store your data. The steps and a ready-made prompt are in this article: https://softwiro.com/blog/mcp-excel-templates-ai-agent-guide.html

All the templates in this group: Excel templates for teachers: gradebook, lesson planning and follow-up

More free templates for: Teachers

More free templates

All templates · Free tools