It is Sunday evening and there are classes on Monday. You do not have a system, you are not buying one this week, and what you actually need is a school attendance spreadsheet that works first thing tomorrow. That is a reasonable decision — up to a certain size, the spreadsheet is the right tool.
Here is the whole template: the columns, the formulas and the conditional formatting, so you can build it yourself in about ten minutes without downloading anything. And then the part no free attendance template will tell you — the five specific moments where this sheet stops working, so you recognise them when they arrive instead of discovering them the week of an inspection.
What the sheet actually has to do
Before you draw a single column, decide what you are asking of it. An attendance register that survives a full year has to:
Let you take the roll in under a minute, without hunting for a student among a hundred rows.
Survive a student who starts in March and another who leaves in April, without corrupting anybody's percentage.
Give you each student's attendance percentage on demand, with no manual recalculation.
Tell you on its own who has dropped below your threshold, before somebody asks.
The fourth one is what separates a useful register from a graveyard of letters. Almost every downloadable template does the first three and none does the last, which is why they end up being opened once a term rather than every day.
The template: one sheet per group
If you would rather not build it, download the finished template: it is exactly what the rest of this section describes, with three example students so you can see the formulas working. Read on anyway. The day the sheet starts to fail — and the detail of when is further down — you will need to know why it is built the way it is.
One tab per class group, named the way your team names it — A2 · Mon-Wed 6pm, not Sheet3. Do not put every group on one tab with a "group" column: the first time you try to filter mid-lesson, you will hate it.
The fixed columns go before the dates
This is the one structural trick that matters, and almost no downloadable template applies it: put the summary columns to the left of the date block. If the percentage lives at the far right, every new class forces a column to move, and sooner or later somebody inserts a date in the wrong place and breaks a hundred rows of formulas.
Fixed columns A to H, headers in row 1:
A — ID: the student's identifier in your enrolment records. Boring and decisive — it is the only thing that lets you match this sheet against any other when two students share a name.
B — Student: surname and first name, always in the same order.
C — Start: the date the student starts in this group. A real date, not text.
D — End: the date they leave the group. Blank if they are still active.
E — Present: calculated.
F — Counted: calculated. The classes that count towards their percentage.
G — Attendance %: calculated, formatted as a percentage with no decimals.
H — Status: calculated. "OK" or "Below threshold".
Start and End look like bureaucracy and they are half the value of the sheet. Without them, a student who joined at class twelve carries eleven absences they never had, and their percentage is a lie.
The date block
From column I onwards, one column per class taught. Row 1 holds the real date of that class, formatted as a date rather than the text "12/03", because we are going to compare those dates against the start and end dates later and Excel will not compare text to dates. Students occupy rows 2 to 200; leave the spare rows empty, the formulas already account for them.
The four marks, and the decision nobody writes down
P — present.
L — late: they arrived, but after the start.
E — excused absence.
A — unexcused absence.
And now the decision hardly any school has written down anywhere: does a late arrival count as present, and does an excused absence come out of the denominator? There is no universal answer, only yours — and it has to live inside the workbook, on a tab called Rules, with the date it was agreed. The day somebody outside your school asks how you calculated a student's 82%, that tab is the difference between an answer and an awkward silence.
The template below assumes the most common convention: a late arrival counts as attendance, and an excused absence comes out of the calculation. The opposite convention is one formula away, below.
The formulas
Depending on your Excel's regional settings the argument separator is , or ;. These use commas; if Excel complains, swap them. They work unchanged in Google Sheets.
Present and counted classes
In E2, present plus late:
=COUNTIF($I2:$BZ2,"P")+COUNTIF($I2:$BZ2,"L")
In F2, the classes that count for that student:
=E2+COUNTIF($I2:$BZ2,"A")
That formula adds attendances and unexcused absences, and ignores both excused absences and empty cells. The empty cells are the classes the student was never meant to be at — they had not joined yet, or they had already left — which is why they never accumulate phantom absences.
If your convention is the opposite and an excused absence still counts against the student — which is usual where a visa or accreditation threshold is involved — change F2 to =COUNTA($I2:$BZ2).
The attendance percentage
In G2, formatted as a percentage:
=IF(F2=0,"",E2/F2)
The IF(F2=0,"") is not decoration. Without it, a newly enrolled student returns #DIV/0!, which then poisons every average you build on top.
Keep the threshold in a cell, not inside the formula
Create a Config tab, put 0.85 in B1, and name it: select the cell and type Threshold in the Name Box, to the left of the formula bar. Then, in H2:
=IF(G2="","",IF(G2<Threshold,"Below threshold","OK"))
That thirty-second detour is what lets you change one number in one place when management drops the minimum from 85% to 80%, instead of find-and-replacing across fourteen tabs. Select E2:H2 and fill down to row 200.
Conditional formatting: make the sheet do the watching
1. Colour the marks. Select I2:BZ200 and add four "equal to" rules: P green, L amber, E grey, A red. Now a whole class reads at a glance.
2. Flag the student below threshold. Select A2:H200, add a formula rule, and enter =AND($G2<>"",$G2<Threshold) with a soft red fill. Mind the dollar signs: $G2 locks the column and frees the row, which is what paints the whole row rather than a single cell.
3. Grey out the days that were never theirs. This is the rule almost nobody writes and the one that protects the percentage. Select I2:BZ200, formula rule:
=OR(I$1<$C2,AND($D2<>"",I$1>$D2))
Grey fill. Classes before the start date and after the end date go visually dark, and whoever is taking the roll knows not to type there. It is the domestic equivalent of a date-bounded enrolment: it does not prevent the error, it makes it visible.
The finishing touches
Data validation. On
I2:BZ200, Data → Data Validation → List, and enterP,L,E,A. That is the end of the lowercase "p" and the stray "x" that no formula counts.Freeze panes. Put the cursor on
I2and use View → Freeze Panes. Names and percentages stay put while you scroll across the whole year.The daily rate row. In row 201,
=COUNTIF(I$2:I$200,"P")+COUNTIF(I$2:I$200,"L"); in row 202,=I201+COUNTIF(I$2:I$200,"A"); in row 203,=IF(I202=0,"",I201/I202)formatted as a percentage, and drag all three across. A column with a wildly odd rate almost never means half the group was away — it means nobody took the roll that day, and now you see it on Tuesday instead of in June.A
Summarytab with one row per group:=AVERAGE('A2 · Mon-Wed 6pm'!$G$2:$G$200)for the group average, and=COUNTIF('A2 · Mon-Wed 6pm'!$H$2:$H$200,"Below threshold")for students at risk. Enjoy it while it lasts: every new group is a hand-written row with a tab name buried inside a formula, and the day somebody renames that tab the summary breaks silently.
The five moments this sheet stops working
Everything above works, which is why it is written down. These are the five points, roughly in the order they arrive, where it stops.
1. A mid-term level change
An A2 student outperforms expectations and moves up to B1 on the Thursday. Their attendance is now split across two tabs and neither holds their real percentage. You can end them on one and start them on the other — that is what columns C and D are for — but the year-to-date figure now exists nowhere, and has to be added by hand every time somebody asks for it. For one student that is a few minutes. For fifteen a term it is a recurring job nobody has been given.
2. The second campus
A second site means a second workbook, because nobody shares one file across two buildings and two teams. From that point on, "what is our attendance this month?" means opening two files, checking they use the same threshold and the same convention on excused absences, and adding them up. Consolidation is the first real cost of growth, and more things multiply when you open a second campus than anyone plans for.
3. Rolling intake
If students start every Monday rather than at one fixed start of year — the model of almost every language academy — the date block stops making sense as a grid. Every student has their own window, every student has sat a different number of classes, and comparing two percentages stops being a comparison of like with like. The sheet keeps calculating; it is the number that stops meaning the same thing.
4. An inspection that asks for a year of records
An inspector, an auditor, or whoever oversees your students' visa compliance is not asking for the percentage. They are asking for the record: who marked what, on which day, and when it was changed. An Excel workbook holds none of that. Worse, it holds the current state, so a correction made in May to a class in November is indistinguishable from what was written on the day. The sheet is not lying — it simply cannot demonstrate that it is telling the truth.
5. Two people editing at once
The evening teacher wants to mark their class while the office is building the monthly report. One of them opens read-only, saves a copy, and attendance_march_v2_anna.xlsx is born. At that instant you stop having a register and start having two versions and an argument. Google Sheets solves simultaneous editing, and only that: the other four problems are untouched.
Why we know exactly where it breaks
Because KMPUS started there. Not as a product designed for an imagined school, but inside the school where we worked, solving a problem we watched happen every day.
Teachers took the roll on paper, in class. Then somebody in the office spent at least two hours every single day retyping those paper sheets into Excel. And that workbook had to be rebuilt daily, so that the next morning every student appeared in the right room. Because many students changed level mid-week, the daily rebuild was extraordinarily error-prone. The errors happened anyway.
Our first attempt automated the typing, because we assumed the two hours were the typing. We were wrong. What actually cost the time was rebuilding the rooms every night. Correcting that mistake produced the second build: a room management system that had to reconcile four date-bounded relationships — which teacher, from and until when, which level they taught, and which students were enrolled in that level between which dates. That is where KMPUS stopped being a script and became a product.
Which is why the first of the five items on that list is the mid-term level change, and not the absence of pretty charts. It is the one that cost us.
What this looks like with a proper system
No pitch, just the mechanics. In an academic management system the roll is taken in class, on the teacher's device, and there is no second typing: the record the teacher creates is the record the office reads. An enrolment in a level carries a start date and an end date, so a mid-week level change is ordinary data rather than a copy-and-paste between tabs. The percentage is calculated over the classes that were actually the student's, however many groups they passed through. And every mark is stored with who entered it and when — which is precisely what an inspection asks for.
The rest is written up separately: the case for automated attendance, everything else that falls short when you try to stop running your school on spreadsheets, and the sequence for migrating to a school management system without breaking the term.
FAQ
How do I calculate attendance percentage in Excel?
Count the attendances with COUNTIF and divide by the classes that count for that student: =COUNTIF($I2:$BZ2,"P")+COUNTIF($I2:$BZ2,"L") on top, and that same result plus COUNTIF($I2:$BZ2,"A") underneath. Format the result cell as a percentage rather than multiplying by 100, so the value stays a number you can compare against a threshold.
Excel or Google Sheets for an attendance register?
Google Sheets if more than one person will write in it, because it solves simultaneous editing and gives you version history. Every formula here works in both. What Sheets does not solve is the structure: level changes, multi-campus consolidation and rolling intake are data-model problems, not file-location problems.
Will a spreadsheet survive an accreditation inspection?
It answers "how much", not "how do you know". If you are asked for a full year with an audit trail of who entered each mark and when, a workbook does not have one, because it only preserves the file's current state. While your obligation is to report percentages, the sheet holds. The moment it is to evidence the record itself, it does not.
When is it time to stop using a spreadsheet?
When two of the five points above are true at the same time. One is manageable with discipline; two turn maintaining the file into somebody's job. The most reliable signal is not student numbers — it is the first afternoon somebody spends reconciling two versions of the same register.
Start with the sheet
Build it this week. It is ten minutes of work and it will give you a better attendance register than most schools your size have. Then revisit the list of five every term and mark off how many have happened to you. The day you mark the second one, you know what comes next.
When that day arrives, you can see how attendance behaves inside a full system on the KMPUS free trial — no card, and with your own data.
