Attendance Sheet Format in Excel With Formulas and Totals

Formulas & functions · Updated November 21, 2026

To create an attendance sheet in Excel, put employee or student names in column A, dates across row 4, and enter P, A, or L for present, absent, or leave. With names in A5:A20 and daily entries in B5:AF20, use =COUNTIF(B5:AF5,"P") to count a person’s present days, then fill the formula down for the other rows.

A practical monthly attendance sheet layout

A useful sheet makes it clear who is being tracked, which period it covers, and what each mark means. In a new Microsoft Excel for Microsoft 365 workbook on Windows, set up one worksheet for each month, or make one sheet that can be reused by changing the month and year. A simple layout is:

  • Cell A1: a heading such as “November Attendance.”
  • Cell B1: the year, and cell D1: the month number, such as 11 for November.
  • Cells A4:AF4: the calendar dates for the month, with A4 reserved for the name-column heading.
  • Cells A5:A20: names, one person per row.
  • Cells B5:AF20: the daily attendance marks.
  • Columns AG onward: summary totals and the attendance percentage.

Format the date headings to show the day number if space is limited, but keep them as actual Excel dates rather than typed text. That makes sorting and date-based calculations more reliable. Freeze the name and date headings with View > Freeze Panes if the roster is long or the month has many date columns. A clear, consistent layout also makes the sheet easier to print and review.

Choose attendance codes before entering data

Agree on a small set of codes before the sheet is used. For a basic tracker, P can mean present, A absent, and L approved leave. Explain the codes in a legend above or beside the table, and use them consistently. A mark such as “Half” or “Late” should not be added casually: decide whether it counts as present, absent, or a separate category, and make the summary formulas match that rule.

Blank cells should mean “not entered yet,” not “absent.” This distinction matters because a blank future date is normal, while a blank past date could be missing information. If weekends or holidays are not scheduled days, either leave them out of the attendance process or mark them with a separate code such as H. The simple formulas below count only P, A, and L, so a holiday code is not included in the attendance-rate denominator.

Formulas for present, absent, leave, and attendance rate

With daily marks in B5:AF5 for the person on row 5, put these formulas in the corresponding summary cells. Copy each formula down its column for the rest of the roster. In this example, AG is Present, AH is Absent, AI is Leave, and AJ is Attendance %.

  • With that row layout, enter in AG5: =COUNTIF(B5:AF5,"P"). It counts each P; if the row contains P, P, A, L, and blanks, the result is 2.
  • With that row layout, enter in AH5: =COUNTIF(B5:AF5,"A"). It counts absent marks; for P, P, A, L, and blanks, the result is 1.
  • With that row layout, enter in AI5: =COUNTIF(B5:AF5,"L"). It counts leave marks; for P, P, A, L, and blanks, the result is 1.
  • With marks in B5:AF5 and the three defined codes P, A, and L, enter in AJ5: =IFERROR(COUNTIF(B5:AF5,"P")/(COUNTIF(B5:AF5,"P")+COUNTIF(B5:AF5,"A")+COUNTIF(B5:AF5,"L")),0). In the same example, the result is 2 divided by 4, or 50%.

Format the percentage column as a percentage using Home > Number, then choose Percentage. The rate formula counts only entries in the three defined categories. It ignores blank cells, so it can show a provisional rate while the month is in progress. Before treating it as a final rate, make sure every scheduled day has been recorded. If you introduce a new counted status, update the denominator to include it; otherwise the displayed percentage may not represent your policy.

Make date headings for the selected month

Typing dates manually is straightforward, but a date formula can create the calendar row from the year and month cells. With the year in B1, month number in D1, and the first date cell in B4, enter this formula in B4 and copy it right through AF4: =IF(COLUMNS($B:B)<=DAY(EOMONTH(DATE($B$1,$D$1,1),0)),DATE($B$1,$D$1,COLUMNS($B:B)),""). For November 2026, the first date is November 1 and the last nonblank date is November 30; later cells in the 31-column span return blank. Format B4:AF4 with a date format such as d or ddd d so the headings are compact but recognizable.

This date-generation formula uses EOMONTH, DATE, DAY, COLUMNS, and IF, which are available in current desktop Excel. It also handles shorter months and leap years without requiring a different formula. If the worksheet is used for a work schedule rather than every calendar day, the formula still makes calendar dates; you can identify weekends visually or use only the columns for scheduled days. Do not count unscheduled days as absences simply because they have date headings.

Use data validation and formatting to reduce entry mistakes

A dropdown list helps keep entries consistent. Select the attendance-entry range, such as B5:AF20, then choose Data > Data Validation on Excel for Windows. In the Data Validation dialog, set Allow to List and enter P,A,L in the Source box. Keep the in-cell dropdown option enabled, then select OK. Excel for Mac also provides Data Validation on the Data tab, though dialog placement can differ slightly by version. If the list grows or needs longer labels, put the codes in a small reference range and use that range as the validation source instead.

Conditional formatting can make the grid easier to scan. Select B5:AF20 and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Add a rule for P, A, and L, choosing distinct, readable fills. Do not rely on color alone: keep the letters visible, since color may be hard to distinguish for some readers or in a printed copy. Use a contrasting font and avoid a palette where similar shades make statuses unclear.

Define workdays and interpret the percentage correctly

An attendance percentage is only meaningful when its denominator matches the organization’s attendance rules. The example calculates attended days divided by recorded P, A, and L marks. It treats an approved leave day as part of the denominator but not as a present day. That is one possible policy, not a universal definition. Some organizations report presence against scheduled workdays and treat approved leave separately; others exclude approved leave from the rate. Choose the interpretation first, label the column accordingly, and adjust the formula to match.

For example, if a person has 18 present marks, one absence, and one leave mark, the sample formula returns 18/20, or 90%. If the local policy excludes leave, the corresponding rate would instead be 18/19, about 94.7%. These figures are not interchangeable, even though both could be called an attendance percentage. Put a short note near the summary explaining what is included. If partial days count, decide whether to use a separate code, a fraction, or a different tracking method, and document that decision rather than silently treating a half-day as a full day.

Common formula and setup problems

  • The total is zero: Check that the entries exactly match the formula’s code. A value such as “Present” will not match a formula counting P. A dropdown list helps prevent spelling and spacing differences.
  • The percentage looks too high: Check for missing past entries. Blank cells are intentionally excluded by the example percentage formula, so missing attendance records do not lower the rate.
  • The percentage is displayed as 0.9: The calculation may be correct. Format the cell as Percentage to display 90% instead.
  • Dates continue into the next month: If dates were entered by dragging a series, a 31-day month can extend into the following month. Use the date formula above or stop the series at the month’s final date.
  • A copied formula refers to the wrong row: The sample summary formulas use relative row references, so copying down should change row 5 to row 6 and so on. If you move the summary block, check that the daily range still covers the intended date columns.
  • A new code is not included: COUNTIF counts only the exact code named in its criteria. Update both category totals and the attendance-rate calculation when your code policy changes.

When a result seems wrong, test a row with a few known entries and calculate it by hand. For example, two P marks, one A, one L, and one blank should produce totals of 2, 1, and 1, with a sample attendance rate of 50%. A small test helps distinguish a formula problem from a policy or data-entry problem.

Excel template, paper register, or attendance software?

An Excel sheet suits a small roster, a straightforward monthly process, or a team that needs to adapt its own categories and reports. It can be printed, filtered, and extended with summaries, but the workbook owner must maintain the formulas, code list, and access controls. A paper register avoids spreadsheet setup and may be practical where users do not have dependable computer access, but totals and corrections must be handled manually. Dedicated attendance software may support workflows such as approvals, shift schedules, or audit records, but whether those features are necessary depends on the organization and its requirements.

A template can save setup time, but inspect it before using it: check the date range, formulas, hidden rows, and code definitions. A polished appearance does not guarantee that its percentage calculation matches your policy. Do not assume a workbook is an official timekeeping or payroll record unless the organization has approved it for that use. For a small tracker, a clearly labeled sheet with validated entries and tested totals is often easier to maintain than a complicated design with features nobody uses.

Protect and share attendance information carefully

Attendance information can identify people and reveal absences or leave. Store the workbook in an organization-approved location, share it only with people who need access, and avoid sending unnecessary copies as email attachments. Use a clear file name and period, and keep a controlled original if multiple people contribute. Excel worksheet protection can help prevent accidental edits to formulas, but it is not a substitute for restricting access to the file or its storage location.

Before printing or sharing a copy, check whether it includes more personal information than the audience needs. Keep a backup of the workbook according to your normal records process, and confirm that the intended users can open it. These are practical handling considerations, not a substitute for any workplace policy or legal requirement that applies to your organization.

Frequently Asked Questions

How do I calculate total attendance in Excel?
With one person’s daily status entries in B5:AF5 and P as the present code, use =COUNTIF(B5:AF5,"P") to count present days. Copy the formula down for the other people.

How do I calculate an attendance percentage in Excel?
Use present marks divided by the total of the attendance statuses your policy counts. The article’s example includes P, A, and L in the denominator, so blanks and other codes are excluded.

Can I use Y and N instead of P and A?
Yes. Replace the codes in the data-validation list and in the formulas. For example, the present-count formula must use Y if Y is your chosen present mark. Keep the legend and formula criteria aligned.

How do I make a dropdown for attendance status?
Select the entry cells, choose Data > Data Validation, set Allow to List, and provide the allowed codes as the source. On Excel for Mac, use Data Validation from the Data tab; the dialog may look different from Windows.

Should weekends count in an attendance sheet?
Only if they are scheduled attendance days under your rules. The sample formulas count entered P, A, and L codes, so leave unscheduled days blank or distinguish them with a code that is excluded from the rate. Make the schedule and denominator clear to readers.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.