How to combine student grades and attendance records
By the Analistable team · Updated · 6 min read
Use the student ID as the key across the class roster, the gradebook and the attendance export. Join them into one row per student with average grade, attendance % and missing assignments, then filter for students below an attendance threshold or with several missing pieces of work — usually the earliest warning signs.
Part of our guide: How to join spreadsheets on a common column
Build the student summary
Attendance % =COUNTIFS(Attendance[Student ID], [@ID], Attendance[Mark], "Present") / COUNTIFS(Attendance[Student ID], [@ID])
Average =AVERAGEIFS(Grades[Score], Grades[Student ID], [@ID])
Submitted =COUNTIFS(Grades[Student ID], [@ID])
Missing =COUNTA(Assignments[Name]) - [@Submitted]| Student | Attendance % | Average | Missing | Flag |
|---|---|---|---|---|
| S-1001 | 97% | 72 | 0 | |
| S-1002 | 84% | 58 | 2 | Check in |
| S-1003 | 71% | 41 | 4 | At risk |
Which assignments are missing
=TEXTJOIN(", ", TRUE, FILTER(Assignments[Name], COUNTIFS(Grades[Student ID], [@ID], Grades[Assignment], Assignments[Name]) = 0, ""))This lists the names of the assignments with no grade for the student — useful in parent emails.
Across several classes
Teachers often keep one sheet per class. Stack them with a Class column (see combining sheets into one) so a student's attendance and grades across subjects appear together.
Student data is sensitive. Keep it within your school's systems or tools that process it locally, and follow your data protection policy.
Does attendance track results?
With one row per student, a quick check is the correlation between attendance and average grade:
=CORREL(Summary[Attendance %], Summary[Average])A value close to 1 means students who attend more score higher; close to 0 means no clear link. It doesn't prove cause, but a strong relationship supports intervening on attendance first.
In Google Sheets
The same COUNTIFS, AVERAGEIFS, FILTER and TEXTJOIN formulas work in Google Sheets. If attendance comes from a separate spreadsheet, bring it in with IMPORTRANGE onto its own tab and point the formulas at that tab.
Common data problems
- Students who joined mid-term have fewer attendance records — calculate the percentage from their own possible sessions, not the class total.
- Excused absences should be a separate mark so they don't count against attendance.
- Assignments not yet due shouldn't count as missing: filter the assignment list by due date ≤ today.
Step by step
- Export attendance from your register or management information system with student ID, date or session, and mark.
- Export grades from the gradebook with student ID, assignment and score.
- Make a Students table with one row per student ID, plus name and class.
- Add the attendance, average, submitted and missing formulas above.
- Add a Flag column with your thresholds and sort by it.
Flag =IF(OR([@[Attendance %]] < 0.75, [@Average] < 45, [@Missing] >= 4), "At risk",
IF(OR([@[Attendance %]] < 0.9, [@Missing] >= 2), "Check in", ""))The thresholds are examples; use your school's own attendance and attainment criteria. All of these formulas work in Excel 2019, Excel 365 and Google Sheets, except the FILTER-based missing-assignment list, which needs Excel 365, Excel 2021 or Sheets. Mark codes vary between systems (P, /, Present), so count the codes your register actually uses.
Second example: scores out of different totals
Averaging raw scores only works when every assignment is marked out of the same total. If one test is out of 40 and another out of 100, convert to percentages first by joining an assignment table with the maximum mark:
Max mark =XLOOKUP([@Assignment], Assignments[Name], Assignments[Max mark])
Percent =[@Score] / [@[Max mark]]| Assignment | Score | Max mark | Percent |
|---|---|---|---|
| Algebra test | 26 | 40 | 65% |
| Geometry project | 51 | 100 | 51% |
| Homework set 3 | 9 | 15 | 60% |
The average of the percentages is (65 + 51 + 60) / 3 ≈ 58.7%, while the raw average of the scores (26 + 51 + 9) / 3 ≈ 28.7 means nothing. Use AVERAGEIFS on the Percent column, or a weighted average if assignments carry different weights.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| #DIV/0! in Attendance % | No attendance records for the student | Wrap in IFERROR and check the student ID |
| Student missing from one export | ID formatted differently (leading zeros, prefix) | Store IDs as text in the same format |
| Average looks too low | Ungraded work recorded as 0 | Leave ungraded cells blank so AVERAGEIFS ignores them |
| Attendance over 100% | Duplicate attendance rows after combining exports | Remove duplicates on student, date and session |
To check the result, sum the Submitted column and compare it with the number of grade rows, and spot-check two students against the register. Refresh each half-term by replacing the exports; the Students table and formulas stay the same.
Doing it in SQL or Power Query
For a whole year group, a query builds the summary in one step. This runs in DuckDB and SQLite:
SELECT s.id,
a.present * 1.0 / a.sessions AS attendance,
g.avg_score, g.submitted
FROM students s
LEFT JOIN (SELECT student_id, SUM(mark = 'Present') AS present, COUNT(*) AS sessions
FROM attendance GROUP BY student_id) a ON a.student_id = s.id
LEFT JOIN (SELECT student_id, AVG(score) AS avg_score, COUNT(*) AS submitted
FROM grades GROUP BY student_id) g ON g.student_id = s.id;Aggregating attendance and grades separately before joining matters: joining the raw tables first would multiply every attendance row by every grade row and give wrong averages and counts. In Power Query, the same idea is Group By on each table, then Merge Queries into the student list.
Common mistakes
- Using the whole class's session count as every student's denominator.
- Treating a blank grade as zero, which drags the average down for work not yet marked.
- Sharing a summary with names and flags beyond the staff who need it.
- Comparing averages between classes that sat different assessments.
Repeating it each term
- Save the workbook as a template with empty Attendance and Grades tables.
- Paste each half-term's exports into the tables; the summary updates.
- Copy the summary's values to a dated tab, so you can compare a student's attendance and average between half-terms.
- Review flagged students with pastoral staff and note the action taken next to each flag.
Comparing two dated summaries shows whether interventions worked: a student whose attendance rose from 71% to 88% after a meeting is a different conversation from one whose attendance fell further. The stacking step is the same as in pulling data from multiple sheets into one.
Frequently asked questions
- How do I combine attendance and grades in Excel?
- Use the student ID to bring attendance percentage (COUNTIFS) and average grade (AVERAGEIFS) into one row per student.
- How do I find missing assignments?
- Compare the list of assignments with the grades recorded for each student; FILTER with COUNTIFS = 0 lists the missing ones.
- Can I do this in Google Sheets?
- Yes, the same COUNTIFS, AVERAGEIFS, FILTER and TEXTJOIN formulas work in Google Sheets.
- How do I calculate attendance percentage?
- Count sessions marked present for the student and divide by all sessions recorded for them.
- How do I flag students at risk automatically?
- Combine thresholds in one formula, for example =IF(OR(Attendance<0.8, Missing>=3), "At risk", ""), and apply conditional formatting to the column.
- How do I average grades marked out of different totals?
- Convert each score to a percentage of its maximum mark first, then average the percentages or use a weighted average.
- Why does my average look wrong when assignments have different maximum marks?
- Raw scores out of different totals can't be averaged directly. Convert each to a percentage of its maximum mark first.
- Should excused absences count against attendance?
- Usually not. Record them with their own mark and leave them out of the present and possible session counts, following your school's attendance policy.