Student grades roll-up template
By the Analistable team · Updated · 3 min read
The workbook has a Students roster, a Marks sheet where every assessment score is one row, and a Summary that totals score and maximum per student with SUMIFS, works out a percentage and flags missing assessments. Paste your roster and marks in, and the Summary updates.
Part of our guide: How to consolidate data in Excel
What the template does
Teachers usually keep one sheet per test or assignment. That is fine for marking, but a report needs one line per student with everything added up. This template replaces the per-assessment sheets with one long Marks table (student, assessment, score, maximum) and rolls it up with formulas, so adding a new assessment is just more rows, not a new column or a new sheet.
It is a small, practical case of the approach in the consolidating data in Excel guide: stack the detail, then summarise it.
Sheet-by-sheet guide
| Sheet | Columns | You fill in? |
|---|---|---|
| Instructions | Step, What to do | No |
| Students | Student ID, Name, Class | Yes |
| Marks | Student ID, Assessment, Score, Max score | Yes |
| Summary | Student ID, Name, Total score, Total max, Percent, Assessments, Status | No (formulas) |
How to fill it in
- Replace the sample roster on Students with your class list. Keep the Student ID unique.
- On Marks, add one row per student per assessment. If you export marks from a gradebook with one column per test, unpivot it first (see unpivoting wide reports).
- Extend the Summary formulas down if you have more students than the sample, and change the
3in the Status formula to the number of assessments you expect.
How the formulas work
Total score and total possible marks are conditional sums over the Marks sheet:
=SUMIFS(Marks!$C$2:$C$200,Marks!$A$2:$A$200,A2)
=SUMIFS(Marks!$D$2:$D$200,Marks!$A$2:$A$200,A2)Because the maximum is summed only over assessments the student actually has, a missing piece of work does not drag the percentage down; it is flagged instead:
=IFERROR(ROUND(C2/D2*100,1),0)
=IF(F2<3,"Missing "&(3-F2),"Complete")The same SUMIFS pattern works across separate sheets too, described in SUMIFS across multiple sheets.
Worked example
The sample has three assessments (Test 1 out of 50, Essay out of 30, Final out of 100). Ella has no Final mark yet:
| Student | Total score | Total max | Percent | Assessments | Status |
|---|---|---|---|---|---|
| Ava Patel | 147 | 180 | 81.7 | 3 | Complete |
| Ben Okafor | 117 | 180 | 65 | 3 | Complete |
| Chloe Li | 164 | 180 | 91.1 | 3 | Complete |
| Dan Novak | 95 | 180 | 52.8 | 3 | Complete |
| Ella Reyes | 62 | 80 | 77.5 | 2 | Missing 1 |
If you want weighted grades (for example Final = 60%), add a Weight column to Marks and sum Score/Max × Weight instead of raw marks.
Limits and when to move on
- Student IDs must match exactly between Students and Marks; a stray space makes SUMIFS return 0.
- The template rolls up one class or year group. For several teachers' workbooks, stack their Marks sheets first, or match grades and attendance across files.
- If you want to ask questions such as which students dropped more than 10 points between tests across many files, load them into Analistable and ask in plain English; files stay in your browser.
Frequently asked questions
- Does this work in Google Sheets?
- Yes. Upload the .xlsx to Google Drive and open it with Google Sheets; SUMIFS, COUNTIFS, ROUND and IFERROR behave the same.
- Why is a missing mark not counted as zero?
- Because a missing mark is often work not yet handed in or marked. The Status column flags it so you can decide; to count it as zero, add a Marks row with score 0.
- Can I add more assessments?
- Yes. Add rows to Marks with the new assessment name and maximum, then update the expected count in the Status formula.