Attendance Register Template in Excel: Setup and Tips

How-to basics · Updated November 21, 2026

An attendance register template in Excel can track each person’s daily status and automatically total attendance for the month. Set up one row per person, one column per working date, and use consistent codes such as P for present, A for absent, and L for approved leave; Excel can count those codes and calculate an attendance rate.

A practical monthly register layout

For a simple monthly sheet, put the reporting month and organization details at the top, then create a header row with one person per row and a column for each date. A useful layout is:

Columns Purpose
A Employee or student ID
B Name
C:AG Daily status for dates 1 through 31
AH Present-day total
AI Absent-day total
AJ Leave-day total
AK Attendance rate

This example assumes the date headings are in row 4 and the first person’s record is in row 5. Add further people on the rows below. If the month has fewer than 31 days, leave the unused date columns blank or hide them for that month. Enter actual dates in the heading cells, rather than only typing day numbers, so the sheet can identify weekdays and can be understood when printed or shared.

Agree on what each code means before entering records. P, A, and L are only examples; an organization may need different codes for half days, work-related travel, or other categories. Put a small legend near the top of the sheet. Do not use a code such as A for both absence and annual leave: ambiguous codes make summaries unreliable.

How the totals and rate work

Excel’s COUNTIF function can count each code across a person’s daily cells. With that person’s dates in C5:AG5, put this formula in AH5 to count days marked P:

=COUNTIF(C5:AG5,"P")

If the row contains 18 P entries, the result is 18. With the same daily range, put this formula in AI5 to count days marked A:

=COUNTIF(C5:AG5,"A")

If there are two A entries, the result is 2. With the same daily range, put this formula in AJ5 to count days marked L:

=COUNTIF(C5:AG5,"L")

If there is one L entry, the result is 1. Copy the three formulas down the summary columns for the remaining people. The daily range should match the dates you intend to report; if your sheet uses fewer date columns, adjust the end of the range accordingly.

A common attendance-rate definition excludes approved leave from the denominator, so the rate is present days divided by present plus absent days. With the present total in AH5 and absence total in AI5, enter this formula in AK5:

=IFERROR(AH5/(AH5+AI5),0)

For 18 present days and 2 absent days, the result is 0.9, or 90% when the cell is formatted as a percentage. IFERROR displays zero when there are no present or absent entries yet, preventing a divide-by-zero error. Whether approved leave should be excluded is a policy choice, not an Excel rule. If your organization defines attendance differently, document the definition and adjust the calculation before using the figures.

To copy a formula down, select its cell and drag the fill handle, or copy and paste it into the cells below. Excel adjusts relative references for each row. Check a few copied rows to make sure the formula refers to that person’s daily cells, not the row above or below.

Enter dates and attendance consistently

Enter real date values across the date headings, then format them to display clearly, for example as 1, Mon 1, or 01-Mar. The displayed format does not change the underlying date. For a monthly sheet, confirm that the headings match the selected month and year; day 1 alone is not enough to distinguish one month from another.

Record only the categories the register is designed to capture. For instance, if weekends are not scheduled workdays, leave those cells blank rather than marking everyone absent. The example rate counts only P and A, so blank cells and L entries do not enter its denominator. This means a partly completed month can show a rate based only on entries already made. Label the report as a month-to-date figure if it is shared before all scheduled days have been recorded.

For clarity, use a single code per person per date. If half-day attendance matters, define a code such as HP and decide how it affects totals; the simple formulas above count only exact P, A, or L entries and will not count HP as P. More complex policies may require additional summary formulas or a separate column for hours. Make the rule visible in the legend and apply it consistently.

Set up a drop-down list in Excel

A drop-down reduces spelling differences such as Present, present, and Pres. In Excel for Microsoft 365 on Windows, select the daily-entry range, such as C5:AG34, then choose Data > Data Validation. Under Allow, choose List, and enter P,A,L in the Source box. Select OK. Users can then choose one of the listed codes in each selected cell. In some regional settings, the list separator may differ; if Excel does not accept the comma-separated source, use the separator expected by that installation.

To make the codes easier to scan, select the same entry range and use Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Create a rule for P, A, and L, choosing distinct, readable formats. Do not rely on color alone: keep the text code visible so the register remains understandable when printed in grayscale or viewed by someone who cannot distinguish the colors.

For a wide month, select cell C5 and choose View > Freeze Panes > Freeze Panes. This keeps the header rows above row 5 and the identifying columns to the left of column C visible while you scroll. On Excel for Mac, the core Data Validation and Conditional Formatting features are also available, though ribbon placement and labels can vary slightly by release. Excel for the web has related features, but some controls and workbook behaviors differ from the Windows desktop app.

Make the template easier to use and print

Use a descriptive worksheet name such as Nov 2026 and add a clear title that identifies the team or class. Keep IDs and names in separate columns, because an ID can distinguish people who share a name. Use consistent date formats, readable font sizes, and a legend that explains every code. Avoid merged cells inside the data grid; merged cells can interfere with selecting, sorting, and copying records.

Before printing, check the print preview and set a sensible print area. A 31-day grid may be too wide for portrait orientation, so landscape orientation and fit-to-page-width scaling can help. In Excel for Microsoft 365 on Windows, use Page Layout options such as Orientation and Scale to Fit, then review File > Print. Excessive scaling can make text too small, so a two-page-wide printout may be more usable than forcing every column onto one page.

Protect the structure if many people will enter data. One approach is to leave the daily cells unlocked, protect the worksheet, and keep formulas locked so they are not overwritten accidentally. Worksheet protection helps prevent routine edits; it is not a substitute for secure access controls or a guarantee that sensitive data cannot be disclosed. Test the protected sheet using a copy before distributing it.

Choose a template that fits the job

A monthly grid is easy to understand and works well when a supervisor needs to see one person’s pattern across dates. It is also convenient for a modest group and a clearly defined month. Its drawback is that it becomes very wide, and summarizing information across many months may require separate sheets or additional formulas.

A transaction-style register stores one attendance event per row, with fields such as date, ID, name, status, and optional notes. This form is usually more practical when records need filtering, sorting, or combining over a long period. The trade-off is that it is less like a wall calendar and may need a PivotTable or summary formulas to show a month-by-month view.

A paper register may suit a location where people sign in by hand or computer access is limited, but manual totals can take time and handwriting may be difficult to interpret. Dedicated attendance software can offer features such as user accounts, reporting, and integrations, but availability and cost depend on the product and organization. Excel’s main advantages are familiarity and flexibility; it does not automatically verify that a recorded entry is true or provide a full attendance-management system.

Common errors and how to prevent them

  • Inconsistent entries: Use data validation and a visible legend. A formula counting P will not count Present, and a trailing space can make a text entry different from the intended code.
  • Wrong date range: Check the month, year, and number of scheduled days. Do not count a weekend or holiday as an absence unless it is a scheduled workday under the relevant policy.
  • Misleading rates: State whether approved leave is excluded and whether the rate is month-to-date or final. A rate is meaningful only when its numerator and denominator are understood.
  • Overwritten formulas: Separate daily entry cells from summary cells, and consider protecting formula cells after checking that users can still edit the intended fields.
  • Duplicate or misspelled identities: Keep a stable ID alongside the name and reconcile the list with the authoritative roster before sharing totals.
  • Using the sheet as proof by itself: A spreadsheet is a record of entered information, not independent confirmation. Keep any required approval or supporting record according to the organization’s procedures.

Privacy, sharing, and record keeping

Attendance information can be sensitive, especially when notes reveal health, family, or other personal circumstances. Collect only details needed for the register. Instead of writing private explanations into a broadly shared daily grid, follow the organization’s process for documenting and restricting access to sensitive information.

Store the workbook in an approved location and share it only with people who need access. A password on a worksheet or workbook can reduce accidental changes or casual access, but it should not be treated as a complete security system. Follow applicable organizational retention and privacy rules rather than assuming the template sets them. Keep a clear file name and reporting period, and avoid circulating multiple conflicting copies by agreeing on one authoritative version.

Frequently Asked Questions

Where can I get an attendance register template in Excel?
Excel may offer templates through its template gallery, and organizations often provide their own approved forms. You can also create the monthly layout described above in a blank workbook. Check that any downloaded template uses a suitable attendance definition and does not contain unnecessary macros or fields.

How do I calculate attendance percentage in Excel?
First define what counts as an eligible attendance day. If present days are in AH5 and absences are in AI5, and approved leave is excluded, use =IFERROR(AH5/(AH5+AI5),0) and format the result as a percentage. Change the definition if your rules include leave or other statuses.

Can Excel count present and absent days automatically?
Yes. If daily entries for a person are in C5:AG5, =COUNTIF(C5:AG5,"P") counts P entries and =COUNTIF(C5:AG5,"A") counts A entries. Use the same agreed codes throughout the register and copy the formulas down for other people.

Should weekends and holidays be marked absent?
Usually, they should not be treated as absences when attendance was not expected, but the correct treatment depends on the organization’s schedule and policy. Leave non-working dates blank in a basic register, or clearly distinguish them with a code that your summaries are designed to handle.

Can I use one Excel sheet for a whole year?
You can, but a full-year daily grid becomes very wide and harder to review or print. Separate monthly worksheets are easier to navigate for many small registers. For larger or long-running records, a one-entry-per-row table can be easier to filter and summarize across dates.

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.