An attendance tracker in Excel is a three-sheet workbook: a Roster sheet keyed by ID, a monthly grid with a P, A, L, LV dropdown, and a Summary sheet that counts each code and flags anyone below 75%. It uses only formulas, data validation and conditional formatting, with no macros and no add-ins.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowIt 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 does an Excel attendance tracker need?
An Excel attendance tracker needs three sheets: a Roster for names and IDs, a monthly grid for typing codes, and a Summary for reading totals and percentages.
Build them 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.
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 | |
| 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.
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.
How do you add attendance codes with a dropdown?
Attendance codes go in as a Data Validation list on the grid cells, so each cell offers a dropdown and rejects anything typed that is not on the list.
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.
How do you colour attendance cells automatically?
Conditional formatting colours each attendance code by value, so a glance at the grid shows who was present, absent, late or on leave.
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.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowWhich formulas calculate attendance percentage?
COUNTIF counts each code per person, and the percentage divides Present plus Late by Present, Absent and Late, leaving approved leave out of the denominator.
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.
How do you flag people below 75% attendance?
A conditional formatting rule on the Summary percentage column turns anyone under 75% red, giving you a watchlist the moment the workbook opens.
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.75and0.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.
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.
If you want to build trackers like this with an instructor, try The Complete Excel Course with AI Integration on Coding Liquids.
How do you reuse the tracker for next month?
Copy the April sheet with Move or Copy, rename it and change the date in C1, and the formulas, codes and colours carry over to the new 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 |
Can one tracker work for teachers, team leads and trainers?
Yes, one tracker works for teachers, team leads and trainers, because the three-sheet skeleton stays 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 is an Excel attendance grid the wrong tool?
An Excel attendance grid stops being the right tool beyond about 80 people, or when what you really need is a calendar or a project tracker.
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.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowRelated tutorials on this site
- How to Create a Dropdown List in Excel — deeper dive on the dropdown technique used above.
- Conditional Formatting Tips That Actually Save Time — more patterns for colouring the grid.
- How to Create a Calendar in Excel That Updates Automatically Every Month
- How to Build a Project Tracker in Excel That Stays Tidy
- Excel + AI for HR Teams: Hiring Trackers, Attrition Analysis, and Reporting
- How to Protect an Excel Workbook or Sheet From Editing
- The Complete Excel Guide 2026 — hub for all Excel tutorials.
