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.


Download Free Template

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


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

How to Track Attendance in Excel


Steps to Track Attendance in Excel

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: Make 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.

List of holidays

💬 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: Define 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.

Define Name

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

How to Track Attendance in Excel

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

Define Name


Step 3: Make 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.

How to Track Attendance in Excel

Read More: QR Code Attendance Tracking with Excel (with Easy Steps)


Step 4: Insert 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

Data Validation in Excel

  • 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. 

Data Validation in Excel

  • 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.

How to Track Attendance in Excel

  • 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)

How to Track Attendance in Excel

Read More: How to Make Daily Attendance Sheet in Excel (2 Effective Ways)


Step 5: Enter 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

How to Track Attendance in Excel

  • 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.

How to Track Attendance in Excel

  • 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.

How to Track Attendance in Excel

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

Read More: Attendance Sheet in Excel with Formula for Half Day (3 Examples)


Step 6: Insert 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!. 

How to Track Attendance in Excel

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

How to Track Attendance in Excel


Step 7: Set 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.

Set up Drop-down Menu for the Attendance cells

  • 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.

Set up Drop-down Menu for the Attendance cells

  • 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.

Set up Drop-down Menu for the Attendance cells


Step 8: Highlight 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.

Set up Drop-down Menu for the Attendance cells

  • 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.

Highlight Holiday Columns

  • 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.

Highlight Holiday Columns

  • 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.

Highlight Holiday Columns

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

Highlight Holiday Columns

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

Highlight Holiday Columns


Step 9: Insert 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.

Insert Data in Attendance Cells

Read More: Attendance and Overtime Calculation Sheet in Excel


Step 10: Insert 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.

Insert Formulas to Calculate the Summary Columns

  • 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.

Insert Formulas to Calculate the Summary Columns

  • 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)

Insert Formulas to Calculate the Summary Columns

  • 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)

Insert Formulas to Calculate the Summary Columns

  • 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.

How to Track Attendance in Excel

How to Track Attendance in Excel

Read More: Tracking Student Progress Excel Template (Free Download)


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.

Freeze Panes in Excel

  • 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.

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. You can visit our website ExcelDemy to learn more Excel-related content. If you have any questions or feedback, please let me know in the comment section.


Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo