Create Free Excel Monthly Attendance Sheet with Easy Formula

Walter Jul 15, 2026

Keeping track of monthly attendance can be a daunting task, especially for large teams or organizations. Excel, with its powerful formulas and tools, can simplify this process significantly. In this guide, we'll show you how to create a monthly attendance sheet using Excel, complete with formulas, for free.

Attendance Sheet Template in Excel
Attendance Sheet Template in Excel

Before we dive in, ensure you have Microsoft Excel installed on your computer. If you don't have it, you can use the free, web-based version of Excel, Microsoft 365, which offers the same features. Now, let's get started with a step-by-step guide.

Student Attendance Record Template For Excel
Student Attendance Record Template For Excel

Setting Up the Attendance Sheet

First, let's create a basic layout for our attendance sheet. Open a new workbook in Excel and name the first sheet "Monthly Attendance".

Monthly Attendance Templates in MS Excel
Monthly Attendance Templates in MS Excel

Next, label the headers of your columns. For a typical workspace, you might need columns for names, dates, present, and absences. Your headers should look something like this:

Name Date Present Absence
the information sheet is displayed in this screenshote screen shot from www ms officegeeek com
the information sheet is displayed in this screenshote screen shot from www ms officegeeek com

Adding Names

In the 'Name' column (A), enter the names of your team members. To avoid tedious typing, you can use the auto-fill feature: start typing a name, then hold down the 'Enter' key and drag down to create a list of names.

To make your sheet neat and organized, you can sort the names alphabetically. Select the 'Name' column, click on the 'Data' tab in the ribbon, then 'Sort A-Z' or 'Sort Z-A'.

2021 Employee Attendance Calendar Excel
2021 Employee Attendance Calendar Excel

Dates and Attendance

Now, in the 'Date' column (B), enter the dates for which you want to track attendance. You can use the auto-fill feature here too. For a month's worth of dates, you might start with the 'Date' cell set to the first day, then drag down to fill the rest.

Next, we'll use 'Yes/No' data validation for the 'Present' and 'Absence' columns to ensure only these two options are selected. Select the 'Present' column (C), click on 'Data' in the ribbon, then 'Data Validation'. Check 'Allow' and select 'List' from the 'Validation criteria' dropdown. Type 'Yes,No' in the 'Source' field and click 'OK'. Repeat this for the 'Absence' column (D), ensuring the 'Present' column is not selected.

Excel Monthly Attendance Sheet
Excel Monthly Attendance Sheet

Calculating Attendance with Formulas

Now that we've set up the basic structure of our attendance sheet, let's add some formulas to make it more useful.

Employee Attendance Calendar 1728
Employee Attendance Calendar 1728
🔥🔥 Fully Automated Attendance Sheet in Excel
🔥🔥 Fully Automated Attendance Sheet in Excel
Free Excel Leave Tracker Template (Updated for 2026)
Free Excel Leave Tracker Template (Updated for 2026)
Monthly Attendance Sheet Templates |
Monthly Attendance Sheet Templates |
Excel Monthly Attendance Sheet
Excel Monthly Attendance Sheet
Advance ATTENDANCE SHEET in Excel \
Advance ATTENDANCE SHEET in Excel \
Fully Automatic Attendance sheet  With Salary sheet in excel by learning Center in Urdu/hindi
Fully Automatic Attendance sheet With Salary sheet in excel by learning Center in Urdu/hindi
Attendance Sheets for Employees - Free Templates & Printable Formats
Attendance Sheets for Employees - Free Templates & Printable Formats
Printable Attendance Sheet Format in Excel - Time Clock MTS
Printable Attendance Sheet Format in Excel - Time Clock MTS

Counting Present Days

To calculate the total number of days each team member was present, we'll use the COUNTIFS function in Excel. In a new column, for instance, column E, enter the formula:

=COUNTIFS(A2:A15, E1, B2:B15, ">=" & $B$2, B2:B15, "<=" & $B$31) 

Replace A15 and B31 with the actual last cell in your 'Name' and 'Date' columns. This formula counts the number of days each team member was present in the current month and displays it in column E.

Calculating Attendance Percentage

Next, we'll calculate the attendance percentage for each team member. In the same row as the present days count, in column F, enter the formula:

=E1/(31-COUNTUNIQUE(B2:B31))*100 

This formula calculates the attendance percentage. It subtracts the total number of unique dates from 31 (assuming a full month) and divides the present days by this total, then multiplies by 100 to get the percentage.

Final Touches

Your monthly attendance sheet is now complete! You can sort and filter the data to analyze attendance patterns, identify high performers, and spot any frequent absentees. downstairs.

Remember to save your work regularly. To save your sheet, click 'File' in the ribbon, then 'Save As'. Choose a location and give your sheet a name, then click 'Save'.

With your new monthly attendance sheet, you're ready to track and analyze attendance effortlessly. This sheet will not only save you time but also provide valuable insights to help make informed decisions. Happy tracking!