Free Attendance Sheet in Excel: Daily, Monthly & Yearly
You can get a free attendance sheet in Excel for daily, monthly, or yearly tracking by downloading a template from Microsoft’s official template gallery or by building your own with basic formulas. This article explains the differences between the three timeframes, shows you how to create a simple version yourself, and lists common pitfalls to avoid.
What Each Attendance Sheet Tracks
An attendance sheet records who was present, absent, late, or on leave for a given period. The timeframe changes the level of detail:
- Daily sheet: One row per person, one column per check-in/check-out or per class period. Best for shift work or hourly tracking.
- Monthly sheet: One row per person, one column per day of the month (1–31). Cells are marked with codes like P (present), A (absent), L (late). This is the most common format for payroll and school records.
- Yearly sheet: Usually a summary that rolls up monthly totals (e.g., total days present, absent, or late per employee per month). It is used for annual reviews, leave balances, or compliance reporting.
Where to Find Free Templates
Microsoft offers a built-in template gallery in Excel (File → New, then search “attendance�). These templates are free and work in both Windows and Mac versions. Popular options include:
- “Attendance tracker� – a monthly sheet with conditional formatting.
- “Employee attendance tracker� – includes annual summary.
- “Student attendance sheet� – for classrooms with 30+ rows.
You can also find free templates on sites like Vertex42 or Smartsheet, but always check the license if you plan to use them commercially. Downloading from Microsoft’s own gallery is the safest way to avoid broken formulas or hidden fees.
How to Build Your Own in Excel
If you prefer a custom sheet, here’s a simple way to make a monthly tracker:
- Open a new workbook. In row 1, put the month name in A1 and the year in B1.
- In row 3, put “Name� in A3, then put the numbers 1, 2, 3… up to 31 in B3 through AF3 (adjust for months with fewer days).
- In row 4, put the day abbreviations (Mon, Tue, etc.) under each number using a formula like
=TEXT(DATE($B$1,$A$1,B3),"ddd")– this automatically updates if you change the month/year. - Starting in row 5, list employee or student names in column A.
- For each day, enter a code: P (present), A (absent), L (late), H (holiday), or V (vacation).
- At the end of the row, add a total column:
=COUNTIF(B5:AF5,"P")for present days, and similarly for other codes. - Use conditional formatting to color-code cells (e.g., green for P, red for A) – select the data range, then Home → Conditional Formatting → New Rule.
For a daily sheet, replace the day columns with time columns (e.g., “In� and “Out�) and subtract to get hours worked. For a yearly sheet, create 12 monthly tabs and a summary tab that references each month’s totals using formulas like =SUM(Jan!G5,Feb!G5,...).
Key Formulas You’ll Need
These are the most useful formulas for any attendance sheet:
=COUNTIF(range,"P")– counts how many times “P� appears in a range.=COUNTA(range)– counts non-empty cells (total days recorded).=NETWORKDAYS(start_date,end_date)– counts weekdays between two dates, excluding weekends (and optionally holidays).=IF(weekday_formula="Sat","",IF(...))– to automatically mark weekends as non-working days.=TODAY()– returns the current date, useful for daily sheets that highlight today’s column.
For a daily sheet that calculates hours, use =IF(AND(ISNUMBER(C5),ISNUMBER(D5)),(D5-C5)*24,0) where C5 is clock-in and D5 is clock-out (formatted as times). This gives decimal hours, which you can sum at the bottom.
Common Mistakes and How to Avoid Them
Even with a good template, people often run into these issues:
- Mixing date formats: Excel may interpret “01/02/2025� as January 2 or February 1 depending on your region. Use the ISO format (2025-01-02) or set the cell format explicitly.
- Not freezing panes: When scrolling to the right, you lose sight of names. Use View → Freeze Panes → Freeze First Column.
- Using “X� for present: If you use “X� for present and “A� for absent, the COUNTIF formulas get confused. Stick to one-letter codes and be consistent.
- Forgetting to update weekends: If you copy a monthly template, weekends are often left blank. Use conditional formatting to gray them out, or use the NETWORKDAYS formula to skip them in totals.
- Overwriting formulas: When you type a code over a cell that contains a formula (like a total column), you lose the calculation. Keep totals in separate columns and lock them with cell protection (Review → Protect Sheet).
How to Choose the Right Option
Consider your primary need:
- If you run a small business with hourly employees, a daily sheet with in/out times is best for payroll.
- If you manage a classroom or a team of salaried employees, a monthly sheet with P/A/L codes is simpler and sufficient.
- If you need to track annual leave balances or produce year-end reports, a yearly summary is essential – you can build it from monthly sheets, or use a template that automatically aggregates.
For most users, a monthly sheet with a yearly summary tab is the sweet spot. You get day-level detail without the clutter of a daily sheet, and the yearly tab gives you the big picture.
Frequently Asked Questions
Can I use an attendance sheet for free without signing up?
Yes. Microsoft’s template gallery is free and doesn’t require a Microsoft 365 subscription – you can download the template and open it in the free Excel web app or the desktop version. Some third-party sites may ask for an email, but you can avoid those by using the built-in gallery.
How do I make a dropdown list for attendance codes?
Select the cells where you want the codes, go to Data → Data Validation → Allow: List, and enter your codes separated by commas (e.g., P,A,L,H,V). This prevents typos and makes data entry faster.
Can I automatically count present days per month?
Yes. Use =COUNTIF(range,"P") where range is the row of day cells for that person. For a yearly summary, reference each month’s total cell, e.g., =Jan!G5+Feb!G5+....
What’s the difference between an attendance sheet and a timesheet?
An attendance sheet records presence (present/absent/late) by day, while a timesheet records the exact hours worked each day, often with start and end times. For hourly employees, you need a timesheet; for salaried or classroom settings, attendance is enough.
How do I protect the formulas so nobody breaks them?
Select the cells with formulas, right-click → Format Cells → Protection → check “Locked�. Then go to Review → Protect Sheet and set a password (optional). This prevents accidental edits to totals while still allowing data entry in the code cells.
Can I use these templates in Google Sheets?
Yes. You can upload an Excel attendance template to Google Sheets, and most formulas (COUNTIF, IF, etc.) work the same. The main difference is that some formatting may shift slightly, but the functionality remains.
Final Thoughts
A free attendance sheet in Excel is a practical, low-cost solution for tracking presence across daily, monthly, or yearly periods. Start with a monthly sheet for most needs, add a yearly summary if you need long-term trends, and use a daily sheet only when you must track exact hours. With the formulas above, you can customize any template to fit your exact workflow.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.