How to Track Attendance in Excel (with Detailed Steps)

 

Elements of an Attendance Tracker

To make an attendance tracker in Excel, you will need the following:

  • 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. We will make a template with the mentioned elements.

How to Track Attendance in Excel


Step 1 – Making an ‘Information’ Worksheet in Excel

In this worksheet, add the lists of Months, Holidays, and the Type of activities that will be used to track attendance (present/absent or reason). You can also add the employee names and IDs to link to the main worksheet.

List of holidays

NOTES: We haven’t listed all the holidays in this workbook. You can add or remove holidays as per your company.

Step 2 – Defining the Name of the Month List

  • Select the cells of months.
  • Go to the Formula tab and click on the Defined Name option.
  • You will see a window named “New Name”. Insert a suitable name for the list of cells. We chose “Month” for the Name.
  • Press OK.

Define Name

  • Select the holiday cells and go to the Defined Name option.
  • Type “Holiday” as the name and press OK.

How to Track Attendance in Excel

  • Select the Type cells and go to the Defined Name option.
  • Put “Type” as the name and press OK.

Define Name


Step 3 – Making a Template Structure to Track Attendance

Make columns and cells with the necessary things listed before. Insert the data of employee or participant names and IDs.

How to Track Attendance in Excel


Step 4 – Inserting Formula for Month, Start Date, and End Date

  • Select the month cell.
  • Go to the Data tab and click on the Data Validation option

Data Validation in Excel

  • A window named “Data Validation” will appear.
  • Select the “List” option in the Allow menu.
  • Type “=Month” in the Source option and press OK. 

Data Validation in Excel

  • If you go to the month cell in the worksheet, you will see a drop-down.
  • Click on it and select a month.

How to Track Attendance in Excel

  • Use this formula in 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”
  • Insert this formula in the End Date cell to get the last date of the month.
=EOMONTH(D3,0)

How to Track Attendance in Excel


Step 5 – Entering the Dates

  • Enter the first date of the month. Use formula to link the cell with the Start Date cell:
=D3

How to Track Attendance in Excel

  • Make a column for the remaining dates.
  • In the second cell, use this formula to get the next date.
=IF(C6<$L$3,C6+1,"")

Formula Explanation

  • C6<$L$3 : 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 :  Adds 1 with the previous cell.
  • “” : If the “If” returns False, keep the cell blank.

How to Track Attendance in Excel

  • Drag the formula through the row.
  • 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.

Step 6 – Inserting Formula to Identify Holidays

  • 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

  • Drag the formula to the remaining cells of the row.
  • The template will be like the screenshot below.

How to Track Attendance in Excel


Step 7 – Setting up a Drop-down Menu for the Attendance Cells

  • Select all the attendance cells.
  • Go to the Data tab and pick Data Validation.

Set up Drop-down Menu for the Attendance cells

  • In the Data Validation window, remain in the Settings tab.
  • Select List from the Allow options.
  • Put =Type in the Source box.
  • Press OK.

Set up Drop-down Menu for the Attendance cells

  • Go to any cells to insert attendance. You will find drop-downs next to the cell.
  • Select a value to insert. You can’t insert any other values.

Set up Drop-down Menu for the Attendance cells


Step 8 – Highlighting Holiday Columns

  • Select all the cells of the attendance column.
  • Go to the Home tab, then to Conditional Formatting, and pick the New Rule option.

Set up Drop-down Menu for the Attendance cells

  • A window will appear named “New Formatting Rule”. Select the option “Use a formula to determine which cells to format” in the Rule Type. 
  • Paste this formula into the Rule Description box:
=OR(C$7= "SUN")
  • Go to the Format option. Select a Red color as the Fill.
  • This will make the cells of the column red in which the 7th-row value is “Sun”. It will make Sunday columns red.

Highlight Holiday Columns

  • 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

  • Insert one more conditional formatting to identify the office holidays from the list. Use this formula into the box:
=COUNTIF(Holidays,C$6)
  • Go to the Format option and select a Green color to fill the box.
  • Press OK.
  • Press Apply in the Conditional Formatting window to apply the formats.

Highlight Holiday Columns

  • You will see the occasional holidays from the list are in green and Sundays are in red.

Highlight Holiday Columns

  • Select the month February to check whether the formatting is working.

Highlight Holiday Columns


Step 9 – Inserting Data in Attendance Cells

  • Insert data in the attendance cells to calculate the summary columns. You can write from the keyboard or use the drop-downs.

Insert Data in Attendance Cells


Step 10 – Inserting Formulas to Calculate the Total Attendance

  • To calculate the total presence of the month or the week, insert this formula into the cell K8:
=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.
  • 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

  • To calculate the total Planned Leave for the month or the week, insert this formula into the first cell:
=COUNTIFS(C8:J8, "PL",$C$7:$J$7,"<>Sun",$C$5:$J$5,0)
  • 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

  • To calculate the total Unplanned Absence (A) of the month or the week, insert this formula into the first cell:
=COUNTIFS(C8:J8, "A",$C$7:$J$7,"<>Sun",$C$5:$J$5,0)

Insert Formulas to Calculate the Summary Columns

  • To calculate the total Work Days of the month or the week, insert this formula into the first cell:
=COUNTIFS($C$7:$J$7,"<>Sun",$C$5:$J$5,0)

Insert Formulas to Calculate the Summary Columns

  • To calculate the Present Percentage, put the cells in the Percentage format.
  • Use this formula into the cell:
=K8/N8
  • This will divide the value of Total Presence by the Total Workdays.

  • To calculate Absent Percentage, put the cells in the Percentage format.
  • Use this formula into the cell:
=(L8+M8)/N8
  • This will divide the value of Total Planned & Unplanned Absesnse by the value of Total Workdays.

How to Track Attendance in Excel

  • Your monthly attendance report is complete. You can track each employee’s attendance data easily.

How to Track Attendance in Excel


Things to Remember

  • If the list is large, then you may face problems seeing the column header while scrolling. You can freeze the panes. in the Freeze Panes menu by selecting the Freeze Panes option here.

Freeze Panes in Excel

  • In the Holiday list, you can add or remove dates. After editing, do the Define Name step again.
  • This workbook will contain data for the full year. Simply copy the data of any month and use “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 the Free Template

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


<< Go Back to Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Osman Goni Ridwan
Osman Goni Ridwan

OSMAN GONI RIDWAN is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, OSMAN has transitioned into the role of a content developer. Specializing in creating technical content exclusively centered around Excel... Read Full Bio

1 Comment
  1. Your steps are very concise. I am getting to work right away and I will give you feed back as soon as possible. Thank you!!!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo