To make a daily attendance sheet in Excel, put employee or student names in column A, dates across row 1, and a short attendance code in each date cell. With names in A2:A31 and daily entries in B2:AF2, enter =COUNTIF(B2:AF2,"P") in AG2 to count that person’s present days; copy it down for the rest of the list.
Choose a layout and consistent attendance codes
A simple monthly sheet uses one row per person and one column per scheduled day. Put a clear title and month above the table, names in column A, and actual dates in row 1. If you have 30 scheduled dates, for example, the date columns can run from B through AE; if the sheet has 31 date columns, they can run from B through AF. Use only the columns that match the period and schedule.
Decide on codes before entering attendance. A practical set is P for present, A for absent, L for late, and E for an excused absence. Explain the codes in a small key near the table. If late arrivals count as attended for your reporting rules, define that explicitly rather than assuming the spreadsheet knows what “attendance” means. You might count P and L together for a participation measure, while keeping them separate for a punctuality report.
Keep the data area consistent: use a code in every scheduled-day cell once its status is known, and leave a cell blank when it has not yet been recorded. Do not use an ambiguous mix of marks such as “yes,” “here,” check marks, and P. A formula looking for P will not treat those other entries as present. Also avoid merging cells inside the data grid, because merged cells make sorting, copying formulas, and selecting records harder.
Count attendance with basic formulas
For a monthly row with dates in B1:AF1 and one person’s codes in B2:AF2, the formula below counts cells marked P. Put it in AG2, then fill it down for other people whose daily entries occupy the corresponding row.
=COUNTIF(B2:AF2,"P")
If the row contains 18 P entries, the result is 18. COUNTIF is a standard Excel function; the text criterion is enclosed in straight double quotes. The formula does not count L as present, so alter the reporting approach if your policy treats late arrivals as attendance rather than using this number as a total of all attended days.
To total present people for a particular date, use the column for that date. With names in A2:A31 and that day’s codes in B2:B31, put this in B32 and copy it across beneath the other date columns:
=COUNTIF(B2:B31,"P")
If 21 of the 30 people listed have P in column B, B32 returns 21. A row of daily totals can help a manager or teacher spot unexpectedly low turnout, but it is not a substitute for checking individual records. If the list includes people who were not scheduled that day, the total needs to reflect the actual roster rather than every name on the sheet.
Calculate an attendance rate without counting blanks
A common mistake is to divide present days by all date columns, even when some cells are blank because the month is in progress or records are missing. That makes the rate misleading. One approach is to use only recognized status codes as the denominator. With the same B2:AF2 row layout, and P, A, L, and E as the complete set of recorded statuses, put this formula in AH2:
=IFERROR(COUNTIF(B2:AF2,"P")/(COUNTIF(B2:AF2,"P")+COUNTIF(B2:AF2,"A")+COUNTIF(B2:AF2,"L")+COUNTIF(B2:AF2,"E")),0)
For a row containing 18 P, 2 A, 1 L, and 1 E, the formula returns 18/22, or about 81.82%. It ignores blank cells, and IFERROR returns zero when no status has been entered. Format the result as a percentage using Home > Number, then choose Percentage. This rate counts only P as present; if L should also count as attendance, the numerator must be changed to include the late count.
Agree on how holidays, weekends, leave, and unscheduled days should be handled. The clearest monthly design often includes only scheduled dates as columns. If a holiday column is included, a holiday code must be excluded from both numerator and denominator; otherwise the rate will treat it as a recorded attendance day. Apply the same policy to every person and reporting period so comparisons are meaningful.
Build the sheet in Excel step by step
- Create a new workbook and enter the organization, team or class, and reporting month above the data table. Use a separate month sheet or a separate workbook for each period if that makes review and archiving easier.
- Enter names in A2:A31 and scheduled dates in B1 onward. Store dates as real Excel dates, not text that merely looks like a date. You can format the cells to show a compact day number while preserving the underlying date value.
- Enter the agreed status codes in the grid. Add count and rate columns to the right, then enter the appropriate formulas in the first person’s row.
- Copy the formulas down for all listed people. Check a few rows manually against the visible codes to confirm the totals make sense.
- Add a key, identify who maintains the sheet, and note when entries should be completed. Save the file with a clear month and department or class name.
On Excel for Microsoft 365 for Windows, select B2 before choosing View > Freeze Panes > Freeze Panes to keep the top row and first column visible while scrolling. The equivalent Freeze Panes command is also available in Excel for Mac, although ribbon placement can differ by version. Freeze the panes after selecting the cell below and to the right of the rows and columns you want to keep visible.
Use validation and formatting to reduce entry errors
A drop-down list makes it less likely that someone will type “Present” in one cell and P in another. Select the attendance grid, then choose Data > Data Validation. In the Data Validation dialog, set Allow to List and enter the permitted codes as the source, such as P,A,L,E. The list source can depend on regional settings; if Excel rejects comma-separated items, use a small range of cells containing the codes as the source instead. Test the drop-down before distributing the workbook.
Conditional formatting can make codes easier to scan. Select the data grid and use Home > Conditional Formatting > Highlight Cells Rules > Text that Contains to assign distinct fills to entries such as A or L. Choose readable colors and retain the letters: color alone is not accessible to every reader and can be hard to distinguish in print. Avoid applying a rule to entire worksheet columns when a compact range will do, since large ranges can make a workbook harder to manage.
Protect formulas from accidental edits if several people will enter data. One approach is to unlock only the entry cells, leave formula cells locked, and then use Review > Protect Sheet. Protection helps prevent routine mistakes, but it is not strong encryption and should not be treated as a security barrier for sensitive information. Keep a backup copy before changing protection settings, and make sure authorized users know how to maintain the file.
Review, print, and preserve monthly records
Before relying on a monthly total, inspect for misspelled codes, missing entries, duplicate names, and people who joined or left during the period. A blank can mean “not recorded,” not “absent.” Do not automatically convert every blank to A: doing so can create false absence records, especially for future dates or days when a person was not scheduled. Resolve incomplete records with the person responsible for attendance.
For a paper copy, use File > Print to review the preview before printing. A wide monthly grid may fit better in landscape orientation, and scaling to one page wide can make text too small if there are many people or dates. Consider printing a separate summary or splitting the roster across pages rather than making every cell unreadable. Repeat header rows when the print setup and sheet size require multiple pages.
Keep a dated copy of each completed period and use a predictable file-naming convention, such as Attendance-Team-2026-11.xlsx. Store the workbook in an approved location with access limited to people who need it. Attendance records may include personal information; collect only what is needed, avoid placing sensitive explanations in a broadly shared sheet, and follow the organization’s retention and privacy requirements.
When Excel is suitable—and when it is not
Excel works well for a small team, class, club, or temporary tracking need when a designated person can maintain the workbook and the reporting rules are straightforward. It is flexible, easy to adapt, and can provide totals without specialized software. A single shared workbook can also be convenient, but concurrent editing, inconsistent changes, accidental overwrites, and version confusion become more likely as the number of editors grows.
For larger organizations, rotating shifts, multiple locations, formal approval workflows, or connections to payroll and student information systems, dedicated attendance software may be more appropriate. Such systems can provide permissions, audit history, notifications, and reporting features that a basic spreadsheet does not automatically provide. They may have subscription or setup costs, so compare those with the time and risk involved in maintaining spreadsheets. Excel formulas calculate what the sheet tells them to calculate; they do not establish policy, verify that an entry is true, or replace required review.
Frequently Asked Questions
How do I count present days in Excel?
With a person’s daily status codes in B2:AF2 and P used for present, enter =COUNTIF(B2:AF2,"P") in the total cell. It counts only exact P entries in that range.
How do I count absences for each person?
With the same row layout and A as the absence code, use =COUNTIF(B2:AF2,"A") in the person’s absence-total cell. Copy it down for other rows, and confirm that the code matches the one used in the sheet.
How can I make an attendance drop-down in Excel?
Select the cells for daily entries, choose Data > Data Validation, set Allow to List, and provide the allowed codes as the source. Check the list in a few cells and ensure all codes used by the formulas are included.
Should blank attendance cells count as absences?
Not by default. A blank may mean the day has not happened yet, the record is incomplete, or the person was not scheduled. Use a clear policy and fill an absence code only after the status is confirmed.
Can Excel calculate attendance percentages?
Yes. Divide the number of statuses that count as present by the number of recorded scheduled-day statuses, then format the result as a percentage. Define how lateness, excused absences, holidays, and unscheduled days are treated before comparing rates.
Related
- Convert Percentage to Text in Excel: Methods and Tips
- Convert Text to Numbers in Excel: Methods That Work
- Convert Excel to ZIP: How to Compress and Share a Workbook
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MID and FIND Functions: How to Extract Text in ExcelWith the text INV-2026-047 in A2, use =MID(A2,FIND("-",A2)+1,4) to extract the year; the result is 2026. MID returns…
- Excel Table Filter Button Greyed Out? Fixes That WorkIf Excel’s filter button is greyed out, first press Esc to leave cell-edit mode, ungroup any selected worksheets,…
- Excel UNIQUE Function: Return Values Without DuplicatesYes. In Microsoft 365 for Windows, enter =UNIQUE(A2:A100) to return the distinct values in A2:A100, with repeated entries…
- Excell Battery Canada: How to Find the Right BatteryIf you are searching for an Excell battery in Canada, first check whether the name on the product…
- Business Mileage Log Excel Template: Setup and FormulasA business mileage log Excel template can be a simple table that records each trip’s date, odometer readings,…
- Rank in Excel Without Duplicates: Formula and TipsYes. To give every numeric value its own rank, including tied values, add a tie-break count to Excel’s…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.