How to Track Attendance in Excel (with Detailed Steps)

Track attendance in Excel is very common. But a perfect Excel attendance tracker will ease your work a lot. So, in this article, I am sharing a free Excel template to track attendance with you. You can download it from here. You can use and modify it easily as it is a basic template. Along with that, In this article, I will also show you how to track attendance in Excel with easy and clear steps.

Elements of an Attendance Tracker

Before making any templates in Excel, you have to know the things that will be included in the worksheet and the relations between them. So, you can make a draft plan for the template. To make an attendance tracker in Excel, you will need the following things:

Month
Holidays
Types of Activity: P= Present, PL = Planned Leave, A= Absent
Days of Month, Start & End date of Month
Participant Name & Id
Total Present, Planned Leave, Absence & Workdays
Percentage of Presence & Absence

You can add or remove any columns as you need. In this article, I will make a template with the mentioned elements

Here, I will describe how to track attendance in an Excel file by which you can track attendance. You can easily track the attendance of the participants in Excel if you follow the steps mentioned below. The steps are explained clearly with proper illustrations. So, go through the steps to track attendance in Excel.

Step 1: Making an ‘Information’ Worksheet in Excel

First, make a worksheet named “Information”. In this worksheet, add the lists of Months, Holidays, and the Type of activities in the institution. You can also add the information of participants’ names and IDs to link to the main worksheet.

💬 NOTES: I haven’t listed all the holidays in this workbook. You can add or remove holidays as per the calendar of your institution.

Step 2: Defining Name of the Month List

After inserting the necessary information, you have to define names for them. Defining the name will allow you to use the Data Validation tool to make a drop-down menu in the cells.

First, define a name for the list of the months.
To do this, select the cells of months.
Then, go to the Formula tab > Defined Name option.
After that, you will see a window named “New Name”. Here, give a suitable name for the list of cells.
Type “Month” in the Name and press OK.

Similarly, select the holiday cells and go to the Defined Name option.
Then, type “Holiday” as the name and press OK.

Lastly, select the Type cells and go to the Defined Name option.
Then, type “Type” as the name and press OK.

Step 3: Making a Template Structure to Track Attendance

Now, make columns and cells with the necessary things listed before. And insert the data of participants’ names and ids.

Step 4: Inserting Formula for Month, Start Date & End Date

We want to make a template for tracking attendance where you can shift from one month to another easily. And the data for the whole year will be on the same worksheet. For this, you have to make a drop-down option in the cell to select a month.

First, select the month cell.
Then, go to the Data tab and click on the Data Validation option

As an outcome, a window named “Data Validation” will appear.
Keep in the settings tab.
then, select the “List” option in the Allow menu.
And, type “=Month” in the Source option and press OK.

Now, if you go to the month cell in the worksheet, you will see a drop-down option to open.
Click on this to open and select a month.

Now, type this formula into the Start Date cell.

=DATEVALUE("1"&M1)

Formula Explanation :

DATEVALUE function transforms a date that is in Text format into a valid Excel date
Here, the M1 cell is the month cell giving value “January”
“1”& “January” denotes a date “1st January”

Then, type this formula in the End Date cell to get the last date of the month.

=EOMONTH(D3,0)

Step 5: Entering the Dates

Now, you will have to make columns for all dates of the month.

First, enter the first date of the month. For this use this formula to link the cell with the Start Date cell.

=D3

Then, you will make a column for the remaining dates. In the second cell type this formula to get the next date.

=IF(C6<$L$3,C6+1,"")

Formula Explanation:

C6<$L$3 : it denotes a condition that cell C6 (Previous Date before this cell) is less than L3 (End Date). You must use absolute reference because the cell of End Date will be the same for the next cells also.
C6 + 1 : It is a command when The “If” condition is true. It asks to add 1 with the previous cell.
“ ” : It denotes that when The “If” condition is False, keep the cell blank.

Then, copy the formula and paste it into the remaining cells in the row.

After that, get the name of the weekdays for the dates. For that, paste this formula into cell C7.

=TEXT(C6, "ddd")

Formula Explanation:

The TEXT function will convert the Date value of the C6 cell to text.
“ddd” denotes the format of the text which will give the name of the weekday in 3 strings.

💬 NOTES: Make the cells in Date Format. Without doing this, it may give unknown values.

Step 6: Inserting Formula to Identify Holidays

In the attendance tracker, you may want to have the dates identified which are holidays. You may find it complex but here I will explain them easily.

Put this formula into cell C5.

=IFERROR(IF(C6="",1,MATCH(C6,Holidays,0)),0)

Formula Explanation

MATCH(C6,Holidays,0) : The MATCH function will search the value of C6 in the Holiday list.
IF(C6=””,1,MATCH(C6,Holidays,0) : IF Function denotes that if the value of cell C6 is blank then insert 1 else search that in the Holiday list.
IFERROR(IF(C6=””,1,MATCH(C6,Holidays,0)),0) : It denotes that when the IF condition can’t give any values then it will give an error value and the IFERROR function works to give the value 0 instead of Error!.

Finally, copy and paste the values to the remaining cells of the row.
Now, the template will be like the screenshot below.

Step 7: Setting up Drop-down Menu for the Attendance Cells in Excel

Now, you will set up a drop-down menu for the attendance cells. So, when you want to input the attendance data, you can’t insert any other values except the Type list.

For this, select all the attendance cells.
And, go to Data tab > Data Validation.

Then in the Data Validation window, keep remaining in the Settings tab.
Now, select List from the Allow options.
And, write =Type in the Source box.
Finally, press OK.

Now, go to any cells to insert attendance. You will find drop-down options to open.
Then, you can select any to insert. And without these options, you can’t insert any other values.

Step 8: Highlighting Holiday Columns

It is necessary to highlight the holiday columns so you can easily identify them. You can manually format them with colors. Alternatively, you can automate them by using the Holiday list and Conditional Formatting.

At first, select all the cells of the attendance column.
And, go to the Home tab > Conditional Formatting > New Rule options.

Now, a window will appear named “New Formatting Rule” and select the option “Use a formula to determine which cells to format” in the Rule Type.
Then, paste this formula into the Rule Description box:

=OR(C$7= "SUN")

Now, go to the Format option. Select Red color as the Fill
As a result, this will make the cells of the column red in which the 7th-row value is “Sun”. That means you want to make the Sunday columns red.

Now, you will see the Sunday columns are in red.

💬 NOTES:: You can add any more days as weekends to mark as Red. Follow the same way to add again.

Now, insert one more conditional formatting to identify the office holidays from the list. Follow the same way and paste this formula into the box:

=COUNTIF(Holidays,C$6)

Then, go to the Format option and select the Green color to fill the box.
And, press OK.
Then, press Apply in the Conditional Formatting window to apply the formats.

As a result, you will see the occasional holidays from the list are in green color and Sundays are in red.

Now, select the month February to check whether the formatting is working perfectly or not.

Step 9: Inserting Data in Attendance Cells

Now, insert data in the attendance cells to calculate the summary columns. To insert data, you can write from the keyboard or use the drop-down suggestions.

Step 10: Inserting Formulas to Calculate the Total Attendance

Now, to calculate the total presence of the month or the week, insert this formula into the cell:

=COUNTIFS(C8:J8, "P",$C$7:$J$7,"<>Sun",$C$5:$J$5,0)

Formula Explanation

Using the COUNTIFS function, you will count the cells if they follow 3 conditions.
C8:J8, “P”: If the cell contains “P”
$C$7:$J$7,”<>Sun”: If the cell doesn’t contain “Sun”
$C$5:$J$5,0: If the cells are of value 0, it means it is not a holiday.

Then, copy the formula and paste it to the other cells of the column or use the Fill Handle icon to drag the formula.

Now, to calculate the total Planned Leave for the month or the week, insert this formula into the cell:

=COUNTIFS(C8:J8, "PL",$C$7:$J$7,"<>Sun",$C$5:$J$5,0)

Then, copy the formula and paste it to the other cells of the column or use the Fill Handle icon to drag the formula.

Similarly, to calculate the total Unplanned Absence (A) of the month or the week, insert this formula into the cell:

=COUNTIFS(C8:J8, "A",$C$7:$J$7,"<>Sun",$C$5:$J$5,0)

After that, to calculate the total Work Days of the month or the week, insert this formula into the cell:

=COUNTIFS($C$7:$J$7,"<>Sun",$C$5:$J$5,0)

Now, to calculate the Present Percentage, first, make the cells of the Percentage format.
Then, use this formula into the cell:

=K8/N8

As a result, it will divide the value of Total Presence by the value of Total Work-Days.

Then, to calculate Absent Percentage, first, make the cells of Percentage format.
And, use this formula into the cell:

=(L8+M8)/N8

It will divide the value of Total Planned & Unplanned Absesnse by the value of Total Work-Days.

Finally, your monthly attendance report is complete. You can track each participant’s attendance data easily.

Things to Remember

In this article, I have shown the attendance tracker for one week, you can easily convert it for a month by adding days.
If the applicant list is large then you may face problems seeing the column header while scrolling. For this, you can freeze the panes. For this, Go to View Tab > Freeze Panes menu and select the Freeze Panes option here.

In the Holiday list, you can add or remove dates as per your institution calender. After editing, do the Define Name step again.
This workbook will contain data for the full year. So, you have to simply copy the data of any month and “paste value only” to another sheet to create a different worksheet for a different month. Then clean the attendance cells to track attendance for the next month.

Download Free Template

You can download the Excel free template to Track Attendance from the following button.

Monthly Attendance Tracker.xlsx

Conclusion

In this article, I have tried to show you how to track attendance in Excel. You can download the free templates and modify them for your use. Also, you can create an Excel file to track attendance following the steps. I hope you found this article helpful. If you have any questions or feedback, please let me know in the comment section.

<< Go Back to Attendance | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!