Attendance tracker formulas in Excel

Set up the grid

One row per person, one column per day, a single letter in each cell. Codes beat ticks or colors because formulas can count them. Keep the codes short and consistent; a drop-down list makes sure nobody types "Pres" or "x".

1. Select the attendance cells (B2:F7 here).
2. Go to Data > Data Validation, set Allow to List and type P,A,L,E in Source. Click OK.
3. Freeze names and dates in place: click B2, then View > Freeze Panes > Freeze Panes.
4. Optional: Home > Conditional Formatting > Highlight Cells Rules > Equal To, type A and choose a red fill, so absences stand out.

Count days present, late and absent

COUNTIF counts the cells in a row that match a code. It is not case-sensitive, so p and P both count. Liam has three P days and one L.

Whether late counts as present is a policy decision. If it does, add the two counts. Most schools count late as attended but report it separately, so keep a Late column too: =COUNTIF(B3:F3,"L").

Attendance percentage

Divide days attended by days that count. Two details make this fair. First, use COUNTA on the row, not a fixed 5, so days not yet recorded and days before someone joined do not lower their rate. Owen joined on Wednesday: dividing by 5 would give him 60%, but he attended 2 of 3 days. Second, subtract excused days from the bottom of the fraction, so a documented medical appointment does not count against anyone.

Noah has 2 present, 1 late and 2 absences out of 5 recorded days.

Flag anyone below a threshold

Wrap the percentage in IF to get a status column. 90% is a common threshold for chronic absence in US schools; workplaces often use a number of absences instead, like =IF(COUNTIF(B5:F5,"A")>=3,"Review","OK").

If the formula is long, put the percentage in its own column (say H) and write =IF(H5<0.9,"Follow up","OK") next to it. Shorter formulas are easier to check.

Daily headcount and total absences

Point COUNTIF down a column instead of across a row to get the headcount for one day. On Monday four students are marked P, Noah is absent and Owen's blank (he had not joined yet) is ignored. Adding the L count keeps late arrivals in the headcount.

COUNTIF also works on the whole grid: =COUNTIF(B2:F7,"A") returns 4 absences for the week, handy for a weekly summary line.

How many school or working days in the month

To show a rate against the whole month, you need the number of possible days. NETWORKDAYS counts Monday to Friday between two dates and skips any holidays you list. H2:H4 holds Veterans Day and the Thanksgiving break.

Use one tab per month (right-click the tab > Move or Copy > Create a copy), and a summary tab that adds each person's monthly counts.