Boost Excel: Create Employee Attendance Sheet with Formulas

Walter Jul 15, 2026

Tracking employee attendance is a crucial aspect of human resource management. It helps monitor productivity, analyze work patterns, and ensure compliance with labor laws. While there are numerous software solutions available, creating an employee attendance sheet using Microsoft Excel is simple, cost-effective, and efficient. This article guides you through creating an interactive and automated employee attendance sheet using Excel's built-in formulas.

Employee Attendance Tracker | Free Excel Attendance Spread | Updated for 2026 | Leave Board
Employee Attendance Tracker | Free Excel Attendance Spread | Updated for 2026 | Leave Board

Before we delve into the steps, ensure you have a basic understanding of Excel's interface, including cells, rows, and columns. Also, familiarize yourself with basic numerical and text formulas, such as SUM, AVERAGE, and IF. Now, let's create an employee attendance sheet that automatically calculates attendance percentages and indicates employee status.

Attendance Sheet Template in Excel
Attendance Sheet Template in Excel

Setting Up the Attendance Sheet

Begin by opening a new or existing Excel workbook. In the first sheet, name it 'Attendance' for convenience. In the first row (A1 to H1), enter the following headers: 'Employee Name', 'Days Present', 'Days Absent', 'Total Days', 'Percentage Present', and 'Employee Status'.

Easy Attendance Sheet Tutorial
Easy Attendance Sheet Tutorial

Freeze the top row as headers by selecting any cell below the headers (e.g., A2), clicking the 'View' tab, and then 'Freeze Panes'. This ensures the headers remain visible while scrolling through the data.

Entering Employee Names and Days

How to Find Working Hours and Calculate Totals in Attendance Sheets – A Step-by-Step Guide
How to Find Working Hours and Calculate Totals in Attendance Sheets – A Step-by-Step Guide

Starting from A2, enter the names of your employees in the 'Employee Name' column (A). The days present and absent for each employee will be entered in columns B and C, respectively. In this example, we'll consider a month of 30 working days.

Enter 30 in cell B2 and C2. Copy and paste these values into the cells below for other employees. You can adjust the total days based on the specific month or period you're tracking.

Calculating Total Days and Attendance Percentage

Fully Automatic Advance Attendance sheet in Excel
Fully Automatic Advance Attendance sheet in Excel

In cell D2, enter the following formula to calculate the total days: `=B2+C2`. Then, drag this formula down for other employees. This calculates the total number of days for each employee, considering both present and absent days.

Next, calculate the attendance percentage. In cell E2, enter the following formula: `=(B2/D2)*100`. This calculates the percentage of days present compared to the total days. Drag this formula down for other employees to automatically calculate their attendance percentages.

Categorizing Employee Status

Employee Attendance Sheet Template
Employee Attendance Sheet Template

Now, let's use the IF function to categorize employee attendance as 'Present', 'Absent', or 'Excused'. Enter the following formula in cell F2: `=IF(B2=D2, "Present", IF(C2>0, "Absent", "Excused"))`. This formula checks if the days present (B2) equal the total days (D2). If true, it categorizes the employee as 'Present'. If not, it checks if the days absent (C2) are greater than zero. If true, it categorizes the employee as 'Absent'. If none of the above conditions are met, it categorizes the employee as 'Excused'.

Drag this formula down for other employees to categorize their attendance status automatically.

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
How To Make attendance sheet for beginners excel
How To Make attendance sheet for beginners excel
2021 Employee Attendance Calendar Excel
2021 Employee Attendance Calendar Excel
“Weekly Attendance Tracker – HR Excel Dashboard Template”
“Weekly Attendance Tracker – HR Excel Dashboard Template”
Attendance Tracker in Excel
Attendance Tracker in Excel
Attendance Sheet of Employees  | How to Use Countif Function in Excel | Calculate Total Number 🤯
Attendance Sheet of Employees | How to Use Countif Function in Excel | Calculate Total Number 🤯
“Weekly Attendance Tracker – HR Excel Dashboard Template”
“Weekly Attendance Tracker – HR Excel Dashboard Template”
Automate Attendance In Excel
Automate Attendance In Excel
Employee Absence Schedule Template for Excel | Download
Employee Absence Schedule Template for Excel | Download

Setting Conditions for Absenteeism

To set up a threshold for absenteeism, you can modify the previously mentioned formula. For example, to categorize an employee as 'Absent' if they have more than 5 days absent in a month, enter the following formula in cell F2: `=IF(B2=D2, "Present", IF(AND(C2>5, C2<=D2), "Frequent Absentee", IF(C2>0, "Absent", "Excused")))`. This formula introduces an 'OR' condition to check if the days absent (C2) are greater than 5 and less than or equal to the total days (D2). If true, it categorizes the employee as a 'Frequent Absentee'.

Adjust the threshold value (currently set at 5) based on your organization's policies. Drag this formula down for other employees to identify frequent absentees automatically.

Monitoring and Analyzing Attendance Data

Sort, filter, andConditionally format your data to gain insights into employee attendance. Use the 'Sort & Filter' button in the 'Home' tab to sort data based on employee names, total days, or attendance percentages. This helps identify employees with perfect or problematic attendance.

Conditionally format cells containing attendance percentages to visualize data at a glance. Go to the 'Home' tab, click 'Conditional Formatting', then 'Highlight Cells Rules'. Choose the 'Between' option to highlight percentages based on your organization's acceptable attendance range.

Regularly update your attendance sheet to monitor employee performance and make informed decisions about leave management, training, and other HR-related activities. This automated and interactive Excel sheet ensures accurate and up-to-date attendance tracking, making it an invaluable tool for any organization.