Confident Cells

Attendance percentage in Excel, and persistent absence

20 November 2026 · 2 min read

Attendance is the sessions attended divided by the sessions possible: =C2/B2, shown with the % format. A pupil who attended 153 of 170 sessions has 90% attendance, which counts as persistently absent (PA), because PA means missing 10% or more.

Attendance percentage

There are two sessions a day, morning and afternoon. Maya could have attended 170 sessions and attended 141.

  1. 141 ÷ 170 = 0.829…
  2. Shown as a percentage, 82.9%.
=C2/B2

Click the % button (or press Ctrl+Shift+5). There's no need to multiply by 100: see percentages in Excel.

Flagging persistent absence

A pupil is persistently absent at 90% attendance or below, and severely absent at 50% or below. IF shows a flag:

=IF(D2<=0.9,"PA","")
ABCDE
1PupilPossibleAttendedAttendanceFlag
2Maya17014182.9%PA
3Leah17015390%PA
4Omar17016697.6%
5Zara1708047.1%PA
Leah is on exactly 90%, so she is flagged too. Use <=, not <.

For more than two labels, such as Severe, PA and Good, use a band table and a lookup: the attendance bands lesson shows how.

The share of a group who are PA

Count the pupils at 90% or below with COUNTIF, then divide by the number of pupils:

=COUNTIF(D2:D31,"<=0.9")/COUNTA(A2:A31)

Common mistakes

Using < instead of <=

A pupil on exactly 90% is persistently absent. D2<0.9 misses them.

Typing 90 instead of 0.9

The % format shows 0.9 as 90%, but the value underneath is 0.9. D2<=90 flags everyone. Use 0.9 or 90%.

Dividing the wrong way round

=B2/C2 gives over 100%. It's attended divided by possible.

Try it now

Your turn

In D2, work out each pupil's attendance: the sessions they attended as a share of the sessions possible. Column D is set to show percentages. Your formula will be copied down to D7.

ABCD
1PupilPossibleAttendedAttendance
2Maya170141
3Leah170153
4Omar170166
5Zara17080
6Ivy164160
7Noah158130