What formula counts attendance percentage in Excel?

Use =COUNTIF(range,"P")/COUNTA(range) and format the cell as percentage. For example, =COUNTIF(C2:AG2,"P")/COUNTIF(C2:AG2,"<>") gives the percentage of present days out of all marked days for that person, ignoring blank future dates.

How do I add a dropdown for P, A, L, LV in Excel?

Select the attendance grid cells, go to Data > Data Validation, choose List, and enter P,A,L,LV in the Source box. Every cell in the grid will then show a dropdown so users cannot type invalid codes.

How do I auto-color cells based on attendance code?

Select the grid, open Home > Conditional Formatting > New Rule > Format only cells that contain. Set Cell Value equal to "P" with green fill, "A" with red, "L" with amber, "LV" with blue. Repeat one rule per code.

How do I highlight weekends automatically in the attendance grid?

Use a formula-based conditional formatting rule: =WEEKDAY(C$1,2)>5 with a grey fill, applied to the date row and grid. Saturdays and Sundays then turn grey wherever the date row updates.

Is this tracker suitable for teachers and team leads?

Yes. The same structure works for class registers, workshop cohorts, and small team rosters. Teachers usually add a homework or assessment column; team leads usually add a leave-balance column. The roster, grid, and summary layout stays the same.

Excel

How to Create an Attendance Tracker in Excel for Teams, Schools, or Training

By Sagnik Bhattacharya 24 Feb 2026 · Updated 28 Apr 2026 11 min read

Coding Liquids blog cover featuring Sagnik Bhattacharya for creating an attendance tracker in Excel, with roster and attendance-grid visuals.

This is the tutorial I wish I had when I built my first class register. Most attendance templates online break the moment a name changes, the month rolls over, or you try to count anything. We will build one that does not — using only formulas, data validation, and conditional formatting. No macros, no add-ins. The whole thing fits in one workbook with three sheets.

Coming Soon

Complete Excel Guide with AI Integration

Master formulas, pivot tables, data analysis, and charts — with AI integration.

Learn more

It works for a teacher with 30 students, a workshop trainer with 12 participants, or a team lead tracking 8 people across a sprint. The structure is identical; only the codes change.

If you can type a formula and right-click a sheet tab, you have every prerequisite. The whole build takes about 25 minutes the first time and roughly ten minutes for every subsequent month. I tested everything on Microsoft 365 (desktop and web) — Excel 2019 and 2021 will also work for this tutorial.

What we are building

Three sheets, in this order:

Roster — the list of people. One row per person. This is the only place names live.
April — the attendance grid for the current month. Names down the left, dates across the top, codes inside.
Summary — the auto-calculated view. Present count, absent count, attendance %, and a "watchlist" for anyone below 75%.

The grid sheet is for typing. The summary sheet is for reading. Never mix the two — that is the single biggest mistake in attendance trackers.

Step 1 — Build the Roster sheet

Open a new workbook. Rename Sheet1 to Roster. In row 1, set these headers:

A	B	C	D
ID	Full Name	Group	Email
S001	Aarav Mehta	10A	aarav@example.com
S002	Priya Iyer	10A	priya@example.com
S003	Rohan Das	10B	rohan@example.com

The ID column matters. Names get edited (typos, marriage, transliteration). IDs do not. Every formula in the rest of the workbook will key off column A. If you skip this step, renaming a student in March will silently break February's totals.

Select A1:D1 and press Ctrl + T to convert to a Table. Name it tblRoster (Table Design tab → Table Name). Tables auto-extend when you add rows, which is why the rest of the workbook will keep working when you onboard a new student in week 3.

Step 2 — Build the attendance grid

Add a new sheet and rename it to the month — April. We will set up a 30-day grid.

In A1 type ID, in B1 type Name. In C1 type the first day of the month: 01-Apr-2026. In D1 type =C1+1, then drag right until you reach the end of the month (column AF for a 30-day month).

Format C1:AF1 as dd only — Right-click → Format Cells → Custom → type dd. The header now shows just the day number (1, 2, 3...) but the underlying value is still a real date, which is what makes the weekday formulas work later.

In row 2 onwards, pull the names from the roster:

A2:  =Roster!A2
B2:  =Roster!B2

Drag both down to row 31 (or however many students you have). Now if a name changes on the Roster sheet, every monthly grid updates automatically.

Step 3 — Add the attendance codes with a dropdown

Decide your codes before you start. Keep them to 4 or fewer. The most common set:

Code	Meaning	Counts toward attendance %
P	Present	Yes
A	Absent (unexcused)	No
L	Late (more than 10 min)	Yes (half-credit if you want)
LV	Approved leave / sick note	Excluded from denominator

Now lock the grid so users cannot type anything else. Select C2:AF31 (the entire entry zone). Go to Data → Data Validation. Choose:

Allow: List
Source: P,A,L,LV
Check In-cell dropdown
On the Error Alert tab, set Style to Stop with a message like "Use P, A, L, or LV only."

Now every cell shows a dropdown arrow and rejects typos. If a parent or supply teacher fills in attendance later, they cannot accidentally type "Pp" or "PRESENT" and break your COUNTIFs.

Step 4 — Conditional formatting (the colours)

This is the part that makes the sheet glanceable. A green grid with three red cells immediately tells you who needs a follow-up. Without colour, you are reading letters.

Select C2:AF31 → Home → Conditional Formatting → New Rule → Format only cells that contain. Add four rules, one at a time:

Rule	Cell value	Fill	Font
Present	equal to `"P"`	Light green (#D4EDDA)	Dark green
Absent	equal to `"A"`	Light red (#F8D7DA)	Dark red, bold
Late	equal to `"L"`	Light amber (#FFF3CD)	Brown
Leave	equal to `"LV"`	Light blue (#D1ECF1)	Navy

Then add a fifth rule for weekends so they self-grey-out wherever the calendar lands. Select the same range, New Rule → Use a formula to determine which cells to format:

=WEEKDAY(C$1,2)>5

Set fill to a soft grey (#E9ECEF). The dollar sign is essential — C$1 locks the row but lets the column slide, so every cell in the grid checks the date row above it. When the month rolls over and the weekends shift, the grey shifts with them.

Apply the same weekend rule to the date header row C1:AF1 too, so the date numbers also grey out.

Step 5 — The summary formulas

Add a third sheet called Summary. Headers in row 1:

A	B	C	D	E	F	G
ID	Name	Present	Absent	Late	Leave	Attendance %

Pull names from the roster the same way as before:

A2:  =Roster!A2
B2:  =Roster!B2

Now the four counters. In C2 through F2:

C2:  =COUNTIF(April!C2:AF2, "P")
D2:  =COUNTIF(April!C2:AF2, "A")
E2:  =COUNTIF(April!C2:AF2, "L")
F2:  =COUNTIF(April!C2:AF2, "LV")

And the attendance percentage in G2. This is the formula most templates get wrong:

=IFERROR((C2+E2)/(C2+D2+E2), 0)

Read it carefully. The numerator is Present + Late — late students still showed up. The denominator is Present + Absent + Late, which deliberately excludes approved leave. Excluding LV is what makes the percentage fair: a student with one sick note and 19 days present scores 100%, not 95%. Format G2 as percentage with 1 decimal.

Drag A2:G2 down to cover all rows. If you want to be stricter on lateness, change the numerator to C2+E2*0.5 for half-credit.

Daily totals (optional but useful for teachers)

Back on the April sheet, in row 33 (below your last student), add daily totals:

C33:  =COUNTIF(C2:C31, "P")    // total present that day
C34:  =COUNTIF(C2:C31, "A")    // total absent that day
C35:  =COUNTA(C2:C31)-COUNTBLANK(C2:C31)  // total marked

Drag right across the month. This row instantly shows you which day had the worst attendance — usually the Monday after a long weekend, in my experience.

Step 6 — The 75% watchlist (the bit that earns its keep)

Schools, training providers, and many compliance regimes require a minimum attendance — often 75%. You want this flagged automatically, not noticed in week 11.

On the Summary sheet, select G2:G31 (the percentage column). New conditional formatting rule, Format only cells that contain:

Cell value less than 0.75 — fill red, bold font.
Cell value between 0.75 and 0.85 — fill amber.
Cell value greater than or equal to 0.85 — fill green.

Now opening the workbook on a Friday gives you an instant red-list for the parent calls or one-to-ones you need next week. No filtering, no sorting, just look.

Step 7 — Freeze panes and make it usable

On the April sheet, click cell C2 (the first data cell), then View → Freeze Panes → Freeze Panes. Now scrolling right keeps names visible, scrolling down keeps dates visible. Without this, day 28 is unusable.

Set the column width of C:AF to about 4 (Format → Column Width → 4). The grid should look square, not stretched — that is what makes 30 days fit on one screen.

Step 8 — Reuse for next month

Right-click the April tab → Move or Copy → check Create a copy. Rename the new sheet May. Change C1 to 01-May-2026. Everything else — the formulas, the codes, the conditional formatting, the weekend greying — recalculates automatically.

Then on the Summary sheet, either replace April! with May! in the formulas, or build a year-to-date version that sums across both. For most teachers, one summary per month is cleaner.

The full formula reference

Where	Formula	What it does
April!A2	`=Roster!A2`	Pulls student ID from roster
April!D1	`=C1+1`	Next day in the month
Summary!C2	`=COUNTIF(April!C2:AF2,"P")`	Total present days
Summary!G2	`=IFERROR((C2+E2)/(C2+D2+E2),0)`	Attendance %, excluding leave
Conditional format	`=WEEKDAY(C$1,2)>5`	Greys out weekend columns
April!C33	`=COUNTIF(C2:C31,"P")`	Daily present total

Variations: teachers vs team leads vs trainers

The skeleton is the same. What changes is the column meanings and one or two summary metrics.

Use case	Codes	Extra summary column
School class register	P, A, L, LV	Days below 75% (compliance)
Workshop / cohort	P, A, LV	Sessions completed of total
Team / sprint	WFO, WFH, LV, OFF	Office days vs remote days
Volunteer rota	Y, N, MAYBE	Reliability % over rolling 8 weeks

For a team lead, swap the codes in the Data Validation source from P,A,L,LV to WFO,WFH,LV,OFF and update the conditional formatting rules to match. The formulas stay identical — they just count whichever code you ask them to.

Common mistakes I have seen ruin these trackers

Typing names directly into the grid instead of pulling from Roster. First name change in week 4, the COUNTIF starts splitting one student across two rows.
Mixing notes into the cells ("P (came at 10:15)"). Use a comment or a separate notes column. Cell content has to be exactly P, A, L, or LV — otherwise COUNTIF returns 0.
Hard-coding dates instead of =C1+1. The minute the month has 28 or 31 days, the weekend rule breaks.
Putting absent in the numerator like (C2-D2)/30. This double-penalises and produces percentages above 100% or below 0% on edge cases. Stick to the formula above.
No data validation. Three months in, you discover someone has been typing "p" lowercase. Data validation stops this on day one.

When this is not the right tool

If you have more than ~80 people, you need a real database (or at least Excel + a structured HR workflow). If you need a calendar view rather than a grid, see the auto-updating calendar tutorial. If you find yourself adding task columns and deadlines, you actually want a project tracker, not an attendance sheet.