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.

**Table of Contents**hide

## 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

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

**💬**

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

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

**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

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

**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`

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

**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!.**

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

### 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.**

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

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

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

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

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

- 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**

**Students Tracking Their Own Progress Template****Excel Invoice Tracker (Format and Usage)****How to Create a Daily Task Sheet in Excel (3 Useful Methods)****How to Keep Track of Customer Orders in Excel (With Easy Steps)****Keep Track of Clients in Excel (Download Free Template)****How to Keep Track of Invoices and Payments in Excel (3 Ideal Examples)**