An attendance tracker in Google Sheets works well for small teams, classrooms, or event series where you want a simple record without dedicated HR software. This guide shows you how to build one that tracks daily attendance, calculates totals automatically, and flags patterns worth knowing about.
The Basic Structure
Names in column A, dates across the top as column headers. Each cell in the grid holds an attendance code: P (Present), A (Absent), L (Late), or leave it blank for non-working days. Keep the date headers as actual date values — formatted to show just the day — so formulas can reference them.
Use data validation on the attendance cells to enforce your codes. A dropdown with P, A, L keeps entries consistent.
Calculating Totals per Person
Add summary columns to the right of your date grid:
Days Present:
=COUNTIF(B2:AF2, "P")
Days Absent:
=COUNTIF(B2:AF2, "A")
Days Late:
=COUNTIF(B2:AF2, "L")
Attendance Rate:
=COUNTIF(B2:AF2, "P") / (COUNTIF(B2:AF2, "P") + COUNTIF(B2:AF2, "A") + COUNTIF(B2:AF2, "L"))
Format the attendance rate as a percentage. Apply conditional formatting to flag anyone below 80% attendance in red.
Daily Summary Row
Add a summary row at the bottom showing how many people were present each day:
=COUNTIF(B2:B30, "P")
Copy across all date columns. This shows daily attendance headcount — useful for spotting days where many people are out simultaneously.
The Easy Way: Using SheetXAI in Google Sheets
Example 1: You have a list of employees or students and want to build the tracker.
"I have 25 employee names on Sheet 1. Build an attendance tracker for May 2026 with P/A/L codes, automatic totals per person showing days present, absent, late, and attendance rate, and flag anyone below 90% attendance."
SheetXAI builds the full grid, adds the summary formulas, and applies the conditional formatting.
Example 2: Your attendance data comes from a time tracking or HR system.
"Pull attendance records for May from our HR system and build a summary showing each employee's present days, absent days, and attendance rate, with anyone below 90% flagged."
SheetXAI connects to your system and builds the attendance summary.
Try SheetXAI free and see what it builds for you.
Published May 2026. See also: How to Build a Headcount Tracker in Excel, How to Track Student Grades in Google Sheets, and Google Sheets AI Guide.
