How to create Attendance Tracker in Excel

 

Introduction

If you are a teacher in a school, college or university or even working in an office, every place there will be a one common thing and that is to put record of your students or employees. So it’s imperative to keep an Attendance Sheet. The paper and pen work can get messy and sometimes full of confusion, also may be sometimes disorganized and that’s where Microsoft Excel comes. With this software you can create a simple yet functional Attendance Sheet to keep track of your students or employees.

So why buy an expensive Attendance Software, if you can create Attendance sheet in excel? Yes! It’s absolutely right that you can create your own Attendance sheet or Attendance tracker in Excel in a very easy way. Even with the change of the month, it also changes your Sunday highlighters and the total of Present, Absent and Leave. Here we will learn how to do so:

Step 1: Open a blank Excel sheet

Step 2: Create Employee/Student name list

Step 3: under the Date Column, type date on first column

Step 4: Right click on that date and open the ‘Format cell’ to change the formation of date. Click on Custom on the left side under the Category.

Step 5: Type ‘dd’ on Type box. The date as you want to see on the list will preview on the ‘Sample’. If you type only dd it will show you only date without month and year.

Step 6: As you click on OK button the list will see as below:

Step 7: Click on that cell and drag upto 31^st. It will put the date automatically.

Step 8: Below the date cell, put the formula for day: =text(C3, “ddd”) : Three times ddd means type first three letters of the day as “Sun” in spite of “Sunday”. If we type ddd four times as dddd, then full name of the day will come. Hold the mouse key and drag it.

The day will come automatically as shown above.

Step 9: Now its time to fill absent, present or holiday of the employee/student.

Step 10: Select whole the data upto 10 names and upto 31^st day.

Step 11: For this go to “DATA” menu→Data Validation→ Data Validation.

Step 12: Then select “List” in the ‘Allow’ drop down box except ‘Any Value’.

Step 13: In the “Source” text, type ‘P’ for Present, ‘A’ for Absent and ‘H’ for Holiday with

commas. You can also type full words as Present, Absent and Holiday.

Step 14: When you click any cell the drop down menu with P,A and H comes.

Step 15: You will add Present, Absent or Holiday with the help of drop down menu.

Step 16: Now, its time for highlighting Sunday. For highlighting Sunday, first select the

cell/cells to which you want to apply Sunday.

Step 17:  Then go to ‘Home’ menu → Conditional Formatting→ New Rules.

Step 18: In the ‘New Formatting Rule’ box select the last option i.e. ‘Use a formula to determine which

 cells to format’.

Step 19: Now put your cursor in the Edit text box and select first cell. (Be sure that first cell should be

 selected)

Step 20: As you select one cell as selected in above pic i.e. 'C4' in the edit box it seems

=$C$4. First $ sign is for row and second $ sing is for column. As we want to fix

only row not column so we have to erase first $ sign.

Step 21: Now we erase first $ sing and complete the formula with writing “Sun”. As

=C$4=”Sun” to display Sunday in your attendance sheet. It should be noted that you have to write only

 first three letters of Sunday because you have mentioned ‘Sun’ not ‘Sunday’. Then click on Format:

Step 22: In the “Format cell” box go to fill tab and select the colour through which you want to show

 the Sunday will display, as here I selected Red colour.

Step 23: The “Sunday” will show in red colour.

Step 24: You can also give special effect on word “Sun” to highlight. For example I applied here font

 “Bold” and colour “White”.

The result will be as under:

Step 25: Next time when you create new attendance sheet with new month. The different Sunday date

 will automatically highlighted, if you highlight it with formatting.

Step 26: Now, its time to calculate Present, Absent and Holiday.

Step 27: For this go to the next cell after the 31^st. Write there Present, Absent and Holiday.

Step 28: Fill the row manually (with the help of drop down list) with P, A or H and draw boarder to see

it clearly.

Step 29: First calculate the total presence. For this the formula is: =countif(range, criteria)

Step 30: You can place same formula for Absent and Holiday. Only difference is that you have to put A

 for 'Absent' and H for 'Holiday'. The total record is:

Conclusion…

 

And that is it. We have our Excel Attendance Sheet in Excel is ready. You can modify this as per your requirement. Use it for salary calculation, incentive calculation or anything else. This tool will work. 

You can make changes to calculate holidays and weekends separately in each sheet. Then subtract them from the total present days, to calculate total working days. You can also include L for Leave in the drop down to mark leave of employees/Students.

So yeah guys, this is how you can create an excel attendance record for your startup. It is cheap and highly flexible. I hope this tutorial helps you in creating your own excel attendance workbook.

You can also see the Video regarding How to create Attendance Record in Excel in Hindi:

You can also see the Video regarding How to create Attendance Record in Excel in English:

If you have any questions let me know in the comments section below.

Next Topic:

XLOOKUP Function