Daily Attendance Register Template Excel: Setup and Tips

How-to basics · Updated November 22, 2026

A daily attendance register in Excel is a worksheet that lists people down the rows, dates across the columns, and a short status code in each day’s cell. In Microsoft Excel for Microsoft 365 on Windows, you can build a practical monthly template with a few headings, dropdown codes, and formulas for totals and attendance rates; the setup below gives you a reusable structure.

What a daily attendance register records

A register provides a dated record of whether each person attended, was absent, arrived late, or had another recognized status. It can be used by a small business, club, training group, or school, but the right categories depend on the organization’s attendance rules. Excel records what you enter; it does not decide whether an absence is excused or determine how a policy should treat lateness.

For a simple monthly sheet, use one row per person and one column per calendar date. This makes it easy to scan a person’s record across the month or compare attendance on a particular date. A useful layout is:

  • Cell A1: Person or employee name.
  • Cells B1:AF1: The dates of the month, one date per column. There are 31 possible date columns; leave columns beyond the month unused or hide them.
  • Rows 2 onward: One person per row, with attendance codes in the date columns.
  • Columns AG onward: Optional totals, such as present, absent, late, and attendance rate.
  • A note near the top or on a separate legend sheet: The meaning of every code.

Use actual Excel dates in the heading cells, not text that merely looks like a date. Actual dates can be sorted, formatted, and used reliably in formulas. Select the date headings and choose a date format from Home > Number Format. A display such as “1” or “Mon 1” can save space while the underlying cell still contains a full date.

Set up the monthly template

Start with a new workbook and add a clear title, such as “Attendance Register — April 2026,” above the table. Put the month, year, group or department, and the person responsible for maintaining the sheet near the title if those details help identify the record. Enter the first day of the month in B1, then fill the remaining headings with the dates in order. Check that the final date matches the month; not every month has 31 days.

Enter names in column A, one name per row. If you need to distinguish people with similar names, use a separate ID column and keep names in the next column. Avoid combining several people in one cell: individual rows make totals and filters more dependable. Keep a spare row or add new rows inside the table when the group changes.

For a reusable workbook, save a clean copy with the headings, formatting, and formulas but no month’s attendance data. Create a working copy for each month. A separate worksheet for each month can work for a small group, but a separate workbook or a single long, structured attendance table may be easier to maintain when records span many years.

Choose attendance codes that match the rules

Keep codes short and define them consistently. A basic set is P for present, A for absent, and L for late. You might also need an approved absence code, a half-day code, or a code for a non-working day, but do not add categories unless someone will use and interpret them correctly. A blank cell should mean “not recorded” rather than silently standing for absence; otherwise, a missing entry can be mistaken for a confirmed status.

Decide how each category affects the totals before building formulas. For example, if late counts as attended, your present total might count both P and L. If late is tracked separately, report it in its own column and explain whether it is included in the attendance-rate numerator. Likewise, a holiday or scheduled day off should usually remain outside the denominator rather than being counted as an absence. These are policy choices, not Excel defaults.

Add totals and an attendance-rate formula

Put summary headings in AG1, AH1, AI1, and AJ1, such as Present, Absent, Late, and Rate. Formulas in row 2 can then be copied down for each person. With attendance entries in B2:AF2, enter this formula in AG2 to count P codes:

=COUNTIF(B2:AF2,"P")

For example, if the row contains 18 P codes, 2 A codes, and 1 L code, the formula returns 18. With the same B2:AF2 layout, enter this formula in AH2 to count A codes:

=COUNTIF(B2:AF2,"A")

That example returns 2. With the same layout, enter this formula in AI2 to count late codes:

=COUNTIF(B2:AF2,"L")

That example returns 1. To calculate a rate where P is attended and A and L are included in the number of recorded attendance days, put the following in AJ2. The formula leaves the cell blank if there are no P, A, or L entries:

=IF(COUNTIF(B2:AF2,"P")+COUNTIF(B2:AF2,"A")+COUNTIF(B2:AF2,"L")=0,"",COUNTIF(B2:AF2,"P")/(COUNTIF(B2:AF2,"P")+COUNTIF(B2:AF2,"A")+COUNTIF(B2:AF2,"L")))

For the example of 18 present, 2 absent, and 1 late, that calculation is 18 divided by 21, or about 85.7%. It treats late as a recorded day but not as present. If your rules count lateness as attendance, use a different numerator; make sure the displayed rate is consistent with the policy. Format the rate column as Percentage using Home > Number Format, then copy the formulas down. Check a few rows by hand before relying on the totals.

Use dropdowns and formatting to reduce entry errors

A dropdown list helps prevent variations such as “Present,” “present,” and “P” from being counted differently. Select the attendance entry range, for example B2:AF31, then choose Data > Data Validation. In the Data Validation dialog, set Allow to List and enter the approved codes in Source, separated by commas, such as P,A,L. Confirm that the in-cell dropdown option is enabled, then select OK. The precise dialog wording can vary slightly by Excel build. Add any additional valid status codes to the list and to the written legend.

Conditional formatting can make a busy register easier to read, but color should support the code rather than replace it. Select B2:AF31 and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Create separate rules for P, A, and L and choose distinguishable fills. Verify that the rules cover the full attendance range. A printed register or a copy viewed without color should still be understandable from its codes and legend.

To keep headings visible while scrolling, click B2 and choose View > Freeze Panes > Freeze Panes. This freezes the top row and first column because the selected cell is below the row to freeze and to the right of the column to freeze. On Excel for Mac, the Freeze Panes command is also on the View tab, though ribbon layout can vary. On Windows, Ctrl+S saves the workbook; on Mac, use Command+S.

Handle weekends, holidays, and partial months

A 31-column layout is convenient, but it does not mean every column represents a scheduled workday. You can leave weekend and holiday cells blank, mark them with a separate non-working code, or use a separate calendar note. Choose one approach and document it. If non-working days have codes, exclude them from any rate denominator; the example formula above already ignores codes other than P, A, and L.

For a month with fewer than 31 days, unused date columns should not contain attendance codes. You can leave them blank and visually shade them, or hide the columns after the last day. When using a template for a partial month, start and end the dates at the actual reporting period. If a person joins or leaves during the month, record only the days they were expected to attend, or add an agreed status that the summary formulas handle correctly. Do not treat days before a person joined as absences.

Check the register and fix common problems

Formulas are only as reliable as the entries and the chosen rules. Common issues include inconsistent codes, accidental spaces, overwritten formulas, and dates stored as text. A dropdown reduces some typing mistakes, but users may still paste values over it. If a count seems wrong, inspect the cells in that person’s row, confirm that each code matches the list, and check that the formula range reaches the final date column.

  • If a total is too low, look for alternate spellings or extra spaces, such as “P ” instead of “P.”
  • If a rate is unexpectedly high or low, confirm which statuses belong in its numerator and denominator.
  • If a date sorts incorrectly, check that it is a real date value and not text.
  • If a formula shows as text, check whether the cell is formatted as Text; set it to General and re-enter the formula.
  • If copied formulas show the same result on every row, inspect the references and confirm the formula was copied into the intended rows.

Protecting formula cells can reduce accidental edits, but worksheet protection is not a substitute for a backup or access controls. If you use Review > Protect Sheet, test that staff can still enter attendance in the intended cells. Keep a dated copy of the original template, and periodically reconcile the sheet with the source records your organization relies on.

Excel register versus paper and attendance software

Excel is a practical choice when a group is small, the rules are straightforward, and a designated person can maintain the file. It is flexible: you can add fields, adjust codes, filter a table, and print a monthly view. It does require careful entry and file management. A workbook with several editors can develop conflicting copies, and a spreadsheet does not automatically verify who entered a status or when it was changed.

A paper register can be convenient where people sign in themselves or where computers are not available, but totals must be calculated manually or transferred later. Dedicated attendance software may provide user accounts, audit histories, notifications, or reports, depending on the product and plan. Those features may be unnecessary for a small group, and software prices and capabilities vary. Compare the actual requirements, data-handling practices, and costs rather than assuming that one format is always better.

Privacy and responsible recordkeeping

Attendance can be sensitive personal information, especially when a register includes reasons for absence or health-related details. Collect only the fields needed for the stated purpose. Where possible, record a neutral code in the shared register and keep supporting explanations in an appropriately restricted system, following the organization’s own policies and applicable requirements.

Store the workbook in an approved location, limit access to people who need it, and avoid sending unprotected copies through channels that are not suitable for the information. Use clear file names and a retention process so old records are not kept indefinitely by accident. Excel sheet protection can discourage edits, but it should not be treated as strong encryption or as a guarantee that confidential data is secure.

Frequently Asked Questions

How do I make a daily attendance register in Excel?
Put names in rows, dates in columns, and use a defined code such as P, A, or L in each applicable cell. Add a legend, optional dropdowns, and summary formulas, then check that the date range and attendance rules are correct.

Can Excel calculate attendance percentage automatically?
Yes. A formula can divide the number of qualifying attended days by the number of recorded scheduled days. Decide first how to treat late arrivals, holidays, and approved absences, because the formula cannot infer those rules.

Should I leave weekends blank in an attendance sheet?
If the group is not scheduled to attend on weekends, leave those cells blank or identify them as non-working days. Do not count them as absences. Make sure the rate formula excludes any non-working-day code.

How can I stop people entering the wrong attendance codes?
Use Data > Data Validation to create a list of allowed codes, show a legend, and check entries periodically. Dropdowns help standardize normal input, but pasted data can still introduce invalid values.

Can I use one Excel register for the whole year?
Yes, but a wide sheet with hundreds of date columns can be difficult to navigate and audit. Separate monthly sheets are often easier for a simple register; a structured table with one row per person-date record can be more suitable for larger datasets and reporting.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.