An attendance sheet records who was present, absent or on leave on each day, and calculates totals and percentages for you. Teachers, managers and small business owners use it for classes, shifts and projects. In Excel you can build one in under an hour: a row of dates across the top, a list of names down the side, a code in each cell such as P for present and A for absent, and COUNTIF formulas that total the codes. This guide shows each step with a small example that you can check by hand. It also covers drop-downs, weekend shading, attendance percentage, a summary of the whole class, and tips for reusing the sheet every month.
Updated November 2026
In this guide
- Plan the layout
- Step 1: set the month and the dates
- Step 2: add the names and drop-downs
- Step 3: shade weekends automatically
- Step 4: count the codes with COUNTIF
- Step 5: attendance percentage
- Step 6: highlight low attendance
- Step 7: a daily summary row
- Step 8: a class or team summary
- Count working days automatically
- Protect the sheet and share it
- Practice problems
- Common mistakes
Plan the layout
Decide the period first. A monthly sheet works best, with one worksheet per month, or one sheet per class. Put the names in column A, starting from row 5, with the headings in row 4. Put the dates in row 4 from column B to column AF, which is room for up to 31 days. Leave rows 1 to 3 for the title, the month and the legend of codes. The right-hand columns after the dates hold the totals. A simple legend helps everyone use the same codes: P for present, A for absent, L for leave, and H for holiday.
Step 1: set the month and the dates
In cell B1 type the first day of the month as a real date, such as 1 November 2026. Format it as mmmm yyyy so it shows November 2026. In B4 type =B1, and in C4 type =B4+1, then fill across to AF4. To avoid showing dates that belong to the next month, change the formulas from column AE, for example with =IF(AD4+1<=EOMONTH($B$1,0),AD4+1,""). Format the date row as d, so that it shows only the day number. In row 3, show the weekday with =B4 formatted as ddd, which displays Sun, Mon, Tue and so on. November 2026 has 30 days, so the last day is shown in column AE.
Step 2: add the names and drop-downs
Type the student or employee names in A5 downward. Select the attendance cells, for example B5:AF30, go to Data, Data Validation, choose List, and enter P,A,L,H. Each cell now has a drop-down with the four codes, which prevents typing mistakes and keeps the totals correct. Our drop-down list guide shows how to use a range as the source. If you need to enter many days quickly, you can type the letter, and Excel will accept it because it matches the list.
Step 3: shade weekends automatically
Select the date and attendance cells, and add a conditional formatting rule with the formula =WEEKDAY(B$4,2)>5. The second argument 2 makes Monday equal 1, so Saturday is 6 and Sunday is 7, and the rule shades both. Choose a light grey fill. The dollar sign before the row number keeps the reference on the date row when the rule applies to the cells below. For holidays, add a list of dates on another sheet, and use a second rule with COUNTIF, such as =COUNTIF(Holidays!$A$1:$A$20,B$4)>0. Shading saves you from marking weekends by hand.
Step 4: count the codes with COUNTIF
Add total columns after the last date. In AG5 use =COUNTIF(B5:AF5,"P") for days present. In AH5 use =COUNTIF(B5:AF5,"A") for days absent. In AI5 use =COUNTIF(B5:AF5,"L") for leave. Fill these formulas down. As an example, if a student has 18 P, 2 A and 1 L in a month with 21 working days, the totals show 18, 2 and 1. Our COUNTIFS guide explains more complex counting. Use the same pattern for every code in your legend.
Step 5: attendance percentage
The attendance percentage is the days present divided by the days that should have been attended. In AJ5 use =AG5/(AG5+AH5+AI5), which divides present days by present, absent and leave days. For the example of 18 present, 2 absent and 1 leave, the figure is 18 divided by 21, which is 85.7 percent. If leave should not count against the person, divide by present plus absent only, which gives 18 divided by 20, or 90 percent. Decide the rule with your organization, and write it in the sheet. To avoid a divide-by-zero error before any data is entered, wrap it with IFERROR: =IFERROR(AG5/(AG5+AH5+AI5),0).
Step 6: highlight low attendance
Select the percentage column and add a conditional formatting rule that shades values below 0.75 in red, or below 0.9 in orange. A teacher or manager can then see at once who needs a conversation. Use a simple formula, such as =$AJ5<0.75, applied to the name column to shade the whole row. Choose colors that are readable when printed in black and white, and add a note with the threshold so that the rule is clear to anyone who opens the file.
Step 7: a daily summary row
At the bottom of the sheet, add a row for each code to count how many people had that code on each day. For example, in B32 use =COUNTIF(B5:B30,"P") and fill across to see the number present each day. In B33 use =COUNTIF(B5:B30,"A") for absent. A row with the percentage present for each day is =B32/COUNTA($A$5:$A$30). This helps to spot days with many absences, such as the day before a holiday. Our SUBTOTAL guide is useful if you filter the list by department and want totals for the visible people.
Step 8: a class or team summary
On a Summary sheet, collect the percentage for each person with a link to each month, such as =November!AJ5. Use AVERAGE to get the class average. You can sort by attendance percentage with the steps in our sorting guide, and show a chart of the average by month. Keep the same layout in every monthly sheet, so the links always point to the right cells. To create a new month, copy the last sheet, change the date in B1, and clear the codes with Home, Find and Select, Go to Special, Constants, then press Delete. The formulas and the formatting stay.
Count working days automatically
The number of days that should be attended is the number of working days in the month. Use =NETWORKDAYS(B1,EOMONTH(B1,0),Holidays!A1:A20), which counts weekdays and skips the holidays on your list. For November 2026, 1 November is a Sunday, so the weekdays run 2 to 6, 9 to 13, 16 to 20, 23 to 27 and 30 November. That is 4 full weeks of 5 days plus Monday 30 November, which gives 21 working days when the holiday list is empty. If one holiday falls on a weekday, the count drops to 20. Show the result in a cell and divide present days by it to get the attendance percentage for people who were not marked absent.
Protect the sheet and share it
Lock the formula cells and the date row, and leave the attendance cells unlocked, then protect the sheet with Review, Protect Sheet. This prevents accidental changes to the formulas. If several teachers use the same file, save it in a shared folder and agree who updates which sheet. Freeze the panes so that the names and the dates stay visible, as explained in our freeze panes guide. For privacy, store the file securely, since it includes personal data, and share only the summary with people who do not need the details.
Practice problems
Try these. Problem 1: a student has 17 present days, 3 absent days and no leave. The attendance is 17 divided by 20, which is 85 percent. Problem 2: a month has 22 working days, and an employee was present on 20. The attendance is 90.9 percent. Problem 3: if 24 of 30 students are present on one day, the daily attendance is 80 percent. Problem 4: if the target is 90 percent and the month has 20 working days, the minimum number of present days is 18. Problem 5: build the sheet for the month of your choice and check the weekend shading against a calendar.
Common mistakes
The most common mistake is to type the codes in different ways, such as p, P and Present, so COUNTIF misses some. COUNTIF is not case sensitive, but spelling variants matter, so use a drop-down. Another is to enter dates as text, so the weekday formulas fail. A third is to count weekends as absences. A fourth is to overwrite formulas when copying a sheet. A fifth is to forget the rule for leave in the percentage. Test the sheet with a person who has a full month of P, a person with all A, and a person with mixed codes.
Frequently asked questions
How do I make an attendance sheet in Excel?
List the names and dates, use drop-down codes such as P and A, and count them with COUNTIF.
How do I calculate attendance percentage?
Divide the days present by the days that should have been attended.
How do I shade weekends?
Use a conditional formatting rule with =WEEKDAY(date,2)>5.
How do I count working days in a month?
Use NETWORKDAYS with the first and last day of the month and a holiday list.
Can I reuse the sheet each month?
Yes. Copy the sheet, change the start date and clear the codes.


