You are a teacher who grades students in several ways: homework, quizzes, projects and a final exam, each with its own weight in the full mark. What you want to accomplish is to know each student's percentage with a fair calculation of those weights, and to spot early who is close to failing so you can step in in time. The teacher's weighted gradebook does this from one table where you type the scores. The sheet and its page are in Arabic.
The problem it solves
When every score carries the same weight, adding is enough. But when the final is worth 40 of 100 and each category has a different maximum, adding becomes a formula you repeat for every student, and mistakes creep in: a forgotten weight, a wrong maximum, or a changed weight that forces you to redo everything. The sheet does it all from weights you type once in Settings.
How it works, step by step
- In Settings write the subject and class, then the four categories with their weights and maximums.
- Write the pass percentage and the warning margin, and adjust the grade bands. The bands shown are examples only, not an official rule.
- In Gradebook write each student's number, name and category scores. Leave a cell empty if it is not graded yet.
- The sheet works out the weighted total, percentage, grade, status and rank.
- Open At risk and Statistics for the full picture.
A category's contribution = score / maximum x weight, and the contributions are added. The percentage = weighted total / the weights of the categories graded.
What each sheet does
- Settings: categories, weights and maximums, the weight total (with a warning if it is not 100), the pass percentage and the grade bands.
- Gradebook: the student log with grey calculated columns. Status is "at risk of failing", "borderline" or "safe", and a note flags a missing score or one above the maximum.
- At risk: students below the pass percentage or close to it, lowest first.
- Statistics: class average, highest and lowest percentage, each category's average, the weakest category and the grade distribution.
A walk-through with the sample sheet
These names and numbers come from the built-in example and are not real. Weights are 20, 20, 20 and 40; maximums 20, 30, 20 and 60; pass 50% and margin 5%:
- The first student scored 18, 27, 17 and 52: 18 + 18 + 17 + 34.67 = 87.67, which is 87.7% and "excellent" under the example bands.
- One student has no final score yet, so the percentage uses the graded categories, worth 60: 50 / 60 = 83.3%, with the note "incomplete scores".
- There are 14 students and 13 have a percentage. Three are at risk (35.3%, 42.0% and 45.0%), one is borderline (52.7%) and nine are safe.
- The class average is about 64.0% and the highest 98.7%. The weakest category is the final (about 59.7% of its maximum).
- One project score of 22 out of 20 shows the note "score out of range".
Why this approach
- Your own weights: change one and every result updates.
- Early warning: the at-risk list is ready without sorting.
- Missing scores are flagged: an incomplete student is worked out from what exists, with a note.
- Works in Excel 2016 and later, Google Sheets and LibreOffice; free, no sign-up.
How it differs from the grades sheet
The student grades sheet adds components with fixed maximum marks and prints a report card per student. This gradebook uses relative weights you edit, works out the percentage from what has been graded, and flags students by risk of failing.
Using the online version
The page opens on this example. Edit the white cells and the grey ones update. You can add a record with a form, import your own .xlsx, export to .xlsx, or download the Excel file. Your data stays in your browser. Open the template page or the template list.
Who it suits
A teacher following a class with several assessment types, each with a weight. To track homework and behaviour see the homework and behaviour tracker.
Common mistakes, and when a sheet stops being enough
Typical mistakes: a score above the maximum, weights that do not add to 100, or treating the example grade bands as an official rule. The sheet covers one class and one editor at a time. If several teachers must enter marks at once, a custom program can be built for you.
FAQ
How is the weighted total worked out? Each score / its maximum x its weight, then added.
Can I change the weights? Yes, in Settings.
Are the grade bands official? No, they are examples you edit.