Free Excel template · Excel for teachers

Free Excel class attendance template

Q: How do I count P and A marks in Excel?

Use COUNTIF on the student's row: =COUNTIF(B2:K2,"P") counts present days and =COUNTIF(B2:K2,"A") counts absences. To count two codes at once, add two COUNTIFs together.

For teachers who take attendance on paper or in a school system that makes it hard to see patterns. Type one letter per student per day (P present, T tardy, A absent, E excused); the template counts each code, works out every student's attendance rate, flags anyone below the line you choose and shows how many were in class each day.

cellwise-teacher-attendance-template.xlsxExcel workbook · 5.0 KB · works in Excel, Google Sheets, Numbers and LibreOffice · no signup

Download free template

Preview and try it

This is the template's sheet, calculated by the same engine as the Cellwise app. Change any number to see the formulas react before you download.

	A	B	C	D	E	F	G	H	I	J	K	L
1	Student	Nov 2, 2026	Nov 3, 2026	Nov 4, 2026	Nov 5, 2026	Nov 6, 2026	Nov 9, 2026	46336	46337	46338	46339	Present
2	Maya	P	P	P	P	P	P	P	P	P	P	10
3	Leo	P	T	P	P	A	P	P	T	P	P	7
4	Priya	P	P	P	P	P	P	P	P	P	E	9
5	Omar	A	P	P	A	P	T	A	P	P	A	5
6	Jonah	P	P	A	P	P	P	A	P	T	P	7
7	Sofia	P	P	P	T	P	P	P	P	P	P	9
8	Eli	P	P	P	P	E	E	P	P	P	P	8
9	Ana						P	P	P	P	P	5
10
11	In class (P or T)	6	7	6	6	5	7	6	8	8	6
12	Out (A or E)	1	0	1	1	2	1	2	0	0	2
13
14	Class	Period 3 Biology
15	Flag students below	0.9
16	Class attendance rate	0.867
17	Students flagged	3
18
19	Codes: P present, T tardy, A absent, E excused absence. Leave a cell empty before a student joined the class.

How the formulas work

School days fill in by themselves

G1=F1+3

Type the first Monday in B1. Each date after it adds one day (=B1+1), and the Monday after a Friday adds three (=F1+3), so weekends never appear. Change B1 to the start of the next two weeks and every header updates. For a holiday, type the next school date over the formula.

Count each code per student

L2=COUNTIF(B2:K2,"P")

COUNTIF counts the cells in the row that hold exactly P. The Tardy, Absent and Excused columns do the same for T, A and E. COUNTIF ignores upper and lower case, so p counts too, but a stray space ("P ") does not, so type codes without spaces or use Data Validation (Data > Data Validation > List: P,T,A,E) to limit the choices.

Attendance rate out of the days actually marked

Q2=IF(P2=0,"",ROUND((L2+M2)/P2,3))

Present plus tardy, divided by the number of days marked for that student (COUNTA in column P). Dividing by days marked rather than 10 means a student who joined mid-way, like Ana, is not penalized for days before she enrolled: leave those cells empty. Excused absences count as missed days here, as they do in most chronic-absence reports; if your school counts them as present, add O2 to the top of the fraction.

Flag students who need a check-in

R2=IF(Q2="","",IF(Q2<$B$15,"Check in",""))

Any rate below the threshold in B15 (90% in the sample) shows Check in. The $ signs keep every row pointing at the same threshold cell, so you can change it once. In the sample, Omar (60%), Jonah (80%) and Eli (80%) are flagged.

Daily headcount for the office or a sub

B11=COUNTIF(B2:B9,"P")+COUNTIF(B2:B9,"T")

Under each date, row 11 counts who was in class (P or T) and row 12 who was out. Two COUNTIFs added together is the simplest way to count either of two codes, and it works in every version of Excel.

Make it yours

Add students by inserting a row inside the list (right-click row 9 > Insert), then copy the formulas in L to R down into it. The class totals in B16 widen automatically when you insert inside their range.
Keep one sheet per two-week block: right-click the tab > Move or Copy > Create a copy, clear B2:K9 and change the date in B1.
Color the codes so patterns jump out: select B2:K9, Home > Conditional Formatting > Highlight Cells Rules > Equal To, type A and pick red; repeat for T in yellow.
Attendance records can be sensitive. Store the file where your school allows student data, and use first names or student IDs if you share it.

FAQ

How do I calculate attendance percentage in Excel?

Divide the days present by the days marked: =(present+tardy)/days. In this template that is =(L2+M2)/P2. Format the cell as a percentage to show 90% instead of 0.9.

How do I count P and A marks in Excel?

Use COUNTIF on the student's row: =COUNTIF(B2:K2,"P") counts present days and =COUNTIF(B2:K2,"A") counts absences. To count two codes at once, add two COUNTIFs together.

Does this attendance template work in Google Sheets?

Yes. Upload the .xlsx to Google Drive and open it with Google Sheets. COUNTIF, COUNTA, ROUND and IF all work the same way there.

Functions used

Related guides

More free templates

Updated November 2, 2026