An attendance sheet that someone rebuilds by hand every month also hides the one thing a manager needs: who keeps missing work. In this tutorial you build an employee attendance tracker in Excel 2026 step by step: a month drop down, a calendar that builds itself, grey weekends and holidays, colour coded attendance codes, an attendance percentage and an AT RISK flag. No macros. The example is Brightwater Logistics in Columbus, Ohio, with 15 employees.
⬇️ Join Free on Skool & Download the Practice File
Free membership · takes 1 minute · the file is in Classroom → Free Downloads from YouTube on Next Gen Templates. To master formulas like these step by step, join the Excel + AI Mastery: 100 Essential Formulas and Real-World Projects course.
This is the 2026 remake of my earlier tutorial, How to make an attractive attendance tracker.
Frequently Asked Questions
How do I make an attendance tracker in Excel?
Build a month drop down, a calendar header that rebuilds itself, a code drop down (P, A, L, S, H), COUNTIF formulas that count days by code, and an attendance percentage with an OK or AT RISK status.
How do I stop a 30-day month from showing a 31st day?
Wrap the next-day formula in an EOMONTH check: =IF(E12="","",IF(E12+1>EOMONTH($C$10,0),"",E12+1)). Once the date passes the last day of the month the cell stays blank.
How do I grey out weekends and holidays automatically?
Use one conditional formatting formula on the calendar: =AND(E$12<>"",OR(WEEKDAY(E$12,2)>5,COUNTIF(Holidays,E$12)>0)). It greys Saturdays, Sundays and every date on the Holidays sheet.
Why does NETWORKDAYS need the Holidays argument?
Without it NETWORKDAYS counts public holidays as working days. In November 2026 it says 21 instead of 19 because Thanksgiving and the day after are not removed.
How is the attendance percentage calculated?
Present days plus half of the half days, divided by the working days minus paid leave: =IF(SUM(AK14:AO14)=0,"",(AK14+0.5*AO14)/($E$9-AM14)). Approved leave comes out of the denominator.
Why do the COUNTIF references have dollar signs in different places?
$E14:$AI14 locks the columns and AK$13 locks the code row, so one formula can be filled both right and down to count every code for every employee.
Is the practice file free?
Yes. The zip has the Practice and Solution workbooks for a fictional logistics company. All names and numbers are example data for learning.
Download the Free Practice Files
⬇️ Join Free on Skool & Download the Practice File
Free membership · takes 1 minute · the file is in Classroom → Free Downloads from YouTube
Practice + Solution workbooks · attendance tracker · .zip · Excel 365 / 2021 / 2019
By PK, An Excel Expert. The business and numbers are fictional example data for learning.
Conclusion
An attendance tracker needs only a few ideas: a drop down for the month, a calendar that stops at EOMONTH, one conditional formatting rule, and COUNTIF with mixed references. Download the files, build along and compare with the Solution file if you get stuck.
