Student Grade Tracker

About this template

When to use it

A term’s marks kept as raw rows with a summary page in front. Three sheets — Dashboard, Input data, Usage guide — and typing happens only on Input data. The intro line in B3 describes the template rather than your class, so replace or delete it first.

What the dashboard holds

Everything on the Dashboard is live except one block. Key metrics in B7:E7 — rows entered, score total, score average, score max — read column E of Input data. Class summary in B12:F14 groups the same column by the Class label. The Trend over time block (B18:C21) is typed in by hand and never moves on its own.

Filling order

1. Rows 2–21 of Input data take No., Student, Class, Assessment type, Score, Attendance, Notes (A–G). Six sample rows ship and the formula ranges run to row 21, so there is room for twenty.
2. Column E takes plain numbers out of 100 (note 6). Adding a unit label makes the cell text, and it drops out of the average. Decimals are fine — the sample carries 78.5 and 64.5.
3. The class labels on the Dashboard are Class 1, Class 2, Class 3 (B12:B14). The formulas use those cells as their criterion, so retyping a label re-targets them — but it has to match column C character for character.
4. Replace the four Trend over time rows (March diagnostic, Term 1 midterm, Term 1 final, Term 2 midterm: 74 · 79 · 83 · 86) with real averages.

How the formulas calculate

• =COUNT(‘Input data’!$E$2:$E$21) counts numeric scores only.
• Sum, average and maximum run over the same range; average and maximum are wrapped in IFERROR and return 0 while the sheet is empty.
• The class block is =COUNTIF(‘Input data’!$C$2:$C$21,$B12), =SUMIF(…) and =IFERROR(AVERAGEIF(‘Input data’!$C$2:$C$21,$B12,’Input data’!$E$2:$E$21),0), with a share of =IFERROR(C12/COUNT(‘Input data’!$E$2:$E$21),0).
• Under the trend table, =SUM(C18:C21) and =AVERAGE(C18:C21) — 322 and 80.5 as shipped.
• The ranges stop at row 21, so a 21st student is not counted. Copy the last row down and widen $E$21 and $C$21.
• Nothing splits the scores by assessment type, even though column D captures it. Add it yourself: =IFERROR(AVERAGEIF(‘Input data’!$D$2:$D$21,”Term 1 midterm”,’Input data’!$E$2:$E$21),0).

Know this first

Note 5 on the Usage guide asks for the assessment type to be “one of midterm, final or performance task”, but the shipped rows all say “Term 1 midterm”. Pick one wording and use it everywhere, because any grouping formula you add matches on the exact string.

Examples

• Row 2 — 1 · Kim Seo-yeon · Class 1 · Term 1 midterm · 92 · Present.
• The six shipped scores total 488, so Score average reads 81.3 and Score max reads 97.
• Class 1 holds two of those six rows, so its share cell shows 33.3%.

Common mistakes

• Typing “Absent” or a letter grade into the score column, which removes it from COUNT and AVERAGE; leave it blank and explain in Notes.
• Overwriting B7:E7 or the class block and losing the formulas.
• Adding a fourth class to Input data without adding a row to the Class summary — it counts in the shares but appears nowhere.
• Circulating names alongside marks. Replace names with numbers, or share class-level summaries only.