Make an Attendance Tracker in Excel

An attendance tracker in Excel is a grid: people down the side, dates across the top and a short code in each cell. COUNTIF turns the codes into totals and rates, and conditional formatting makes problems jump out. This guide builds a one-week tracker for a team of five that you can extend to a month or a school term. It works the same for staff shifts, classes and training sessions.

How the attendance tracker is laid out#

The tracker has one row per person and one column per day, followed by summary columns.

The codes are P (present), A (absent) and L (late). Put a small key above or beside the grid so anyone filling it in knows what each code means.

Step 1: Add dates across the top#

Real dates in the header let you sort, filter and count by date later. Type the first date in B1, for example 3/3/2025. Then in C1, type:

=WORKDAY(B1,1)

Fill it right to F1 with Ctrl+R (Mac: ⌘R). WORKDAY gives the next working day, so the headers skip Saturdays and Sundays. To show the day name as well, select the headers, press Ctrl+1 (Mac: ⌘1), choose Custom and type the format ddd m/d.

Step 2: Add a drop-down list for the codes#

A drop-down list stops typing mistakes such as "p ", "Pres" or "0". COUNTIF only counts what matches exactly (apart from upper and lower case).

1. Select the grid, B2:F6.
2. Click Data > Data Validation (on Windows it's in the Data Tools group).
3. Under Allow, choose List.
4. In Source, type P,A,L and click OK.

Each cell now has a small arrow with the three codes. Our guide to creating a drop-down list in Excel covers lists that read from cells instead.

Step 3: Count present, absent and late with COUNTIF#

COUNTIF counts the cells in a range that match a condition: =COUNTIF(range, criteria). In G2, count Ana's present days:

=COUNTIF(B2:F2,"P")

In H2 and I2, use the same range with "A" and "L". Then select G2:I6 and press Ctrl+D (Mac: ⌘D) to fill every row. Text criteria always go in double quotes. How to use COUNTIF has more criteria examples, such as counting everything that is not P with "<>P".

A quick check for each row: Present + Absent + Late should equal the number of days filled in. If it doesn't, someone typed a code that isn't on the list.

Step 4: Work out the attendance rate#

Divide the days attended by the days recorded. If late still counts as attended, add the P and L counts. In J2:

=(G2+I2)/COUNTA(B2:F2)

COUNTA counts the filled cells in the row, so blank days that haven't happened yet don't pull the rate down. Format column J as a percentage with Ctrl+Shift+% (Mac: Control+Shift+%). Ben shows 80%: four of five days attended.

If you only want days marked P, use =COUNTIF(B2:F2,"P")/COUNTA(B2:F2). That gives Ben 60%, and it is the formula in the exercise on this page.

If you add a holiday code such as H, take those days out of the bottom of the fraction: =(G2+I2)/(COUNTA(B2:F2)-COUNTIF(B2:F2,"H")).

Step 5: Count each day's attendance#

The same COUNTIF works down a column. In B8, count how many people were present on Monday:

=COUNTIF(B2:B6,"P")

Fill it right to F8. The week reads 4, 3, 4, 5, 3. Tuesday and Friday are the days to look at. This is often the number a manager asks for first.

Step 6: Color absences and low attendance#

Conditional formatting colors cells by rule, so the colors update as codes change.

Turn every A red:

1. Select B2:F6.
2. Choose Home > Conditional Formatting > Highlight Cells Rules > Equal To.
3. Type A, pick Light Red Fill with Dark Red Text and click OK.

Flag the whole row for anyone under 90%:

1. Select A2:J6.
2. Choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
3. Enter =$J2<0.9 and pick a yellow fill.

The $ before J keeps every cell in the row looking at the rate in column J. Without it, each column would check a different cell. The conditional formatting guide explains formula rules step by step.

How to extend the tracker to a month#

• More days: fill the date header right for the whole month, then widen the COUNTIF ranges, for example B2:W2.
• More people: insert rows inside the grid, above the last person, so the ranges grow by themselves.
• One sheet per month: copy the sheet, change the first date and clear the codes. The formulas stay.
• Freeze the names and dates: click B2 and choose View > Freeze Panes so they stay in view as you scroll.

Counting codes like this is also good practice for tests: it is one of the practice tasks to do before an Excel test. Next, try an inventory tracker to practise SUMIF and lookups, or an invoice with formulas.

Practise it#

Do the exercise on this page to work out a present rate with COUNTIF and COUNTA. Then practise COUNTIF with text, numbers and cell criteria and practise conditional formatting rules in a live spreadsheet. The COUNTIF function guide is there when you need the full reference.