
You open the attendance sheet on Friday and see three blank cells. Did those learners miss class, or did someone forget to take attendance? A blank can mean either unless you give it a clear purpose. This Excel tracker counts recorded attendance and gives missing entries their own check.
You can use it for a classroom, an after-school club or a staff training session. The sample uses three sessions so you can verify every result before adding more dates.
Set up the attendance sheet
Put the following headings in A1:G1. For your own tracker, replace Session 1, Session 2 and Session 3 with actual dates so future readers can tell which meeting each column represents.
| Participant | Session 1 | Session 2 | Session 3 | Recorded | Attendance rate | Missing |
|---|---|---|---|---|---|---|
| Alex | P | A | P | |||
| Maya | P | P | ||||
| Sam | E | P | A |
Use P for present, A for absent and E for excused. Leave a cell blank when attendance has yet to be recorded. Insert the formulas below in row 2, then copy them down through row 4.
Count only the entries you recognise
In E2, count the three valid codes:
=COUNTIF(B2:D2,"P")+COUNTIF(B2:D2,"A")+COUNTIF(B2:D2,"E")
In F2, divide present sessions by the sessions marked present or absent:
=IF(E2-COUNTIF(B2:D2,"E")=0,"",COUNTIF(B2:D2,"P")/(E2-COUNTIF(B2:D2,"E")))
Format F2:F4 as percentages. This example excludes excused sessions from the attendance-rate denominator. If your organisation counts them differently, use its definition when building the report. An empty rate means there are no recorded present-or-absent sessions to calculate.
In G2, subtract the recorded entries from the number of session columns:
=COLUMNS(B2:D2)-E2
This catches both blanks and unrecognised codes. It also counts future sessions if you include them in the formula range. To report missing entries for sessions already held, limit all three formulas to the columns for those sessions.
Check the results before using real names
| Participant | Recorded | Attendance rate | Missing |
|---|---|---|---|
| Alex | 3 | 66.7% | 0 |
| Maya | 2 | 100.0% | 1 |
| Sam | 3 | 50.0% | 0 |
Maya’s 100% covers two recorded sessions. Enter A in her blank session cell. Her recorded count should become 3, her missing count should drop to 0, and her rate should become 66.7%. Undo the entry after the test.
Try a second test by replacing Alex’s first P with the word “Present.” The missing count should rise to 1. That test shows why consistent codes matter when someone copies data from a paper list or another spreadsheet.
Make the tracker easier to hand over
Put the code definitions directly above or beside the table, where a colleague can see them. Filter the Missing column for values greater than zero before sending a report. Resolve each entry against the original attendance record. If the record is still unavailable, keep the cell blank so the report shows the gap.
If you share the file with readers who only need totals, create a summary without participant names. For a tutorial screenshot, keep the fictional names shown here.
Formula reference — Microsoft Support guide to COUNTIF