Soumik Dutta
Leave tracker is a widely used task for the human resource department to track every people for their vacation and working days. Almost every organizations use its employee leave tracker. In this article, we will demonstrate how to create a leave tracker in Excel. If you are also interested to know how to make it on your own, download our practice workbook and follow us.
Download Template
Download this free template while you are reading this article.
What Is Leave Tracker?
Leave is a database where we store the history of an employee’s vacation list. All detailed information about his receiving vacation is listed there. It is that database where we can easily see an employee’s performance and sincerity. Almost the HR manager of every company and the owner of small companies handle this type of leave tracker for their organization.
Step-by-Step Procedure to Create Leave Tracker in Excel
To demonstrate the procedure, we are considering a dataset of 5 employees of a company. We will create a leave tracker for them. The final outcome will be like the image shown below.
Step 1: Create Summary Layout
Here, we are going to create the summary layout for our leave tracker database.
- First of all, launch Microsoft Excel on your device.
- Now, rename the sheet name as Summary from the Sheet Name Bar.
- Select cell F1.
- Then, in the Insert tab, select the drop-down arrow of Illustrations > Picture > This Device.
- A dialog box called Insert Picture will appear.
- Then, select your company logo and click Insert.
- In our file, we are inserting our website logo to show the process and for your convenience.
- Now, select the range of cells B4:I4 and select the Merge & Center option from the Alignment group.
- Then, write down the title. We set the file title as Employee Leave Tracker. Keep your desired formatting to the cell.
- In cell B6 write the title Year and cell C6 keep empty for the current year. We keep 2022 as we want to create the tracker for this year.
- After that, in the range of cells K8:L14, specify the leave type and a short form for them. We keep 6 different types of leave and their identical short forms.
- For creating the final summary table, select cell B8:I8.
- Then, select Merge & Center option, and write down the table title. We keep our table title as Year Summary.
- Now, in cell B9, entitled the column name as Employee Name and set the range of cells B10:B14 for 5 employees.
- After that, in the range of cells C9:H9, denote the leave short forms.
- Next, set 2 Total entities, one is column-wise from which we will know an employee’s total leave, and another is row-wise which illustrates the total leave quantity for any specific type of leave.
- Thus, we can say our summary layout is completed.
Thus, we can say that we have completed the first task to create a leave tracker in Excel.
Step 2: Build Tracker List for Each Month
In this step, we will generate the leave tacker data list for every individual month. We are going to build it for January. For the rest of the months, the process will be similar.
- At first, create a new sheet and rename it as Jan.
- In the Home tab, select the Format option from the Cells group and click on Column Width.
- As a result, a small dialog box called Column Width will appear.
- As we will see too many columns in this sheet, set the column width ~2.50 and click OK.
- Then, select cell AF1, and insert your company logo similarly as we show in step-1.
- After that, in cell B4, write down the following formula:
="January"&Summary!C6
- Press Enter to store the data.
- As the month of January has 31 days, and we need a column for the employees’ names, so select 32 columns that are from B6:AG6. Then select the Merge & Center option from the Alignment group.
- In the marge cell, write down the following formula and press the Enter key.
=B4
- Now, entitled cell B7 as Days and cell B8 as Employee Name.
- Modify the cell format for the range of cells B9:B13 and keep them to input the employee’s name.
- Now, we use the DATE function to get the dates. In cell C8, write down the following formula:
=DATE(Summary!$C$6,1,1)
- After that, in cell D8 write down the following formula and drag the Fill Handle icon to copy the formula until date 28 appears.
=C8+1
- In the last 3 cells AE8:AG8, write the formula shown below. This formula will help us to define all the dates according to the month. In this formula, we use the IF and MONTH functions.
=IF(MONTH($AD8+1)>MONTH($C$8),"",$AD8+1)
🔍 Breakdown of the Formula
We are breaking down our formula for cell AE8.
👉 MONTH($AD8+1): This function returns 1.
👉 MONTH($C$8): This function returns 1.
👉 IF(MONTH($AD8+1)>MONTH($C$8),””,$AD8+1): This function returns the date.
- Then, in cell C7, write down the formula to get the corresponding weekday’s name in short form. The IF, INDEX, and WEEKDAY functions will help us to get the result.
=IF(C8="","",INDEX({"Su";"M";"Tu";"W";"Th";"F";"Sa"},WEEKDAY(C8,1)))🔍 Breakdown of the Formula
We are breaking down our formula for cell C9.
👉 WEEKDAY(C8,1): This function returns 7.
👉 INDEX({“Su”;”M”;”Tu”;”W”;”Th”;”F”;”Sa”},WEEKDAY(C8,1)): This function returns Sa.
👉 IF(C8=””,””,INDEX({“Su”;”M”;”Tu”;”W”;”Th”;”F”;”Sa”},WEEKDAY(C8,1))): This function returns the day name Sa.
- Next, drag the Fill Handle icon to copy the formula up to cell AG7.
- To ensure that only our defined leave short forms enter our tracker, we will add a data validation drop-down arrow.
- For adding the drop-down arrow, select cell C9.
- Now, in the Data tab, select the drop-down arrow of the Data Validation option from the Data Tools group.
- Then, select the Data Validation option.
- A small dialog box called Data Validation will appear on your device.
- In the Setting tab, select the drop-down arrow below the Allow title and choose the List option.
- After that, write down the cell references $AH$8:$AM$8 in the box below Source or simply select them with your mouse.
- Finally, click OK.
- You will see a drop-down arrow will add which contains all the short forms.
- Now, drag the Fill Handle icon in the range of cells C9:AG13 to copy the drop-down arrow in all the cells.
- Marked the weekends with a different color so that you can easily find them.
- After that, select the range of cells AH6:AM6 and choose the Merge & Center option.
- Entitled the merged cell as Total.
- Now, we use the COUNTIF function in the following formula in cell AH9, write down the following formula:
=COUNTIF($C9:$AG9,AH$8)+0.5*COUNTIF($C9:$AG9,AH$8&"H")+0.5*COUNTIF($C9:$AG9,"H"&AH$8)
🔍 Breakdown of the Formula
We are breaking down our formula for cell AH9.
👉 COUNTIF($C9:$AG9,AH$8): This function returns 1.
👉 COUNTIF($C9:$AG9,AH$8&”H”): This function returns 0.
👉 COUNTIF($C9:$AG9,”H”&AH$8): This function returns 0.
👉 COUNTIF($C9:$AG9,AH$8)+0.5*COUNTIF($C9:$AG9,AH$8&”H”)+0.5*COUNTIF($C9:$AG9, “H”&AH$8): This function returns the day name 1.
- Drag the Fill Handle icon with your mouse to keep the formula in the range of cells AH9:AM13.
- In the range of cells AH8:AM8, write down the formula to show the leave type short forms.
=Summary!C9
- In cell AH7, use the SUM function to sum the total number column-wise. To sum, use the following formula in cell AH7:
=SUM(AH9:AH13)
- Then, drag the Fill Handle icon to copy the formula up to cell AM7.
- Finally, our leave tracker for the month of January will be ready to use.
- Similarly, create the monthly leave tracker for the rest of the months of the year.
Thus, we can say that we have completed the second task to create a leave tracker in Excel.
Read More: Employee Monthly Leave Record Format in Excel (with Free Template)
Similar Readings
- How to Calculate Half Day Leave in Excel (2 Effective Methods)
- How to Create Maternity Leave Calculator in Excel
- Calculate Annual Leave in Excel (with Detailed Steps)
- How to Calculate Leave Balance in Excel (with Detailed Steps)
- How to Calculate Accrued Vacation Time in Excel (with Easy Steps)
Step 3: Generate Final Leave Tracker
Now, we will complete our Year Summary table in the Summary sheet to get the final report. We will insert a formula in the range of cells C10:H14 to extract the data from the individual month tracker. To get the final result, IFERROR, INDEX, MATCH, and SUM functions will help us.
- In the beginning, insert the formula into cell C9.
=IFERROR(INDEX(Jan!AH$9:AH$13,MATCH($B10,Jan!$B$9:$B$13,0)),0)+IFERROR(INDEX(Feb!AH$7:AH$11,MATCH($B10,Feb!$B$7:$B$11,0)),0)+IFERROR(INDEX(Mar!AH$7:AH$11,MATCH($B10Mar!$B$7:$B$11,0)),0)+IFERROR(INDEX(Apr!AH$7:AH$11,MATCH($B10,Apr!$B$7:$B$11,0)),0)+IFERROR(INDEX(May!AH$7:AH$11,MATCH($B10,May!$B$7:$B$11,0)),0)+IFERROR(INDEX(Jun!AH$7:AH$11,MATCH($B10,Jun!$B$7:$B$11,0)),0)+IFERROR(INDEX(Jul!AH$7:AH$11,MATCH($B10,Jul!$B$7:$B$11,0)),0)+IFERROR(INDEX(Aug!AH$7:AH$11,MATCH($B10,Aug!$B$7:$B$11,0)),0)+IFERROR(INDEX(Sep!AH$7:AH$11,MATCH($B10,Sep!$B$7:$B$11,0)),0)+IFERROR(INDEX(Oct!AH$7:AH$11,MATCH($B10,Oct!$B$7:$B$11,0)),0)+IFERROR(INDEX(Nov!AH$7:AH$11,MATCH($B10,Nov!$B$7:$B$11,0)),0)+IFERROR(INDEX(Dec!AH$7:AH$11,MATCH($B10,Dec!$B$7:$B$11,0)),0)🔍 Breakdown of the Formula
We are breaking down our formula for the month of January only. In our formula, we did that for every month and added those.
👉 MATCH($B10,Jan!$B$9:$B$13,0): This function returns 2.
👉 INDEX(Jan!AH$9:AH$13,MATCH($B10,Jan!$B$9:$B$13,0)): This function returns 0.
👉 IFERROR(INDEX(Jan!AH$9:AH$13,MATCH($B10,Jan!$B$9:$B$13,0)),0): This function returns 1.
- Then, copy the formula up to cell H14 by dragging the Fill Handle icon.
- Now, in cell I10, use the SUM function to sum the range of cells C10:H10.
=SUM(C10:H10)
- Double-click on the Fill Handle icon to copy the formula up to cell I14.
- Again, in cell C16, write the following formula, to sum up, a specific type of leave.
=SUM(C10:C14)
- Finally, copy the formula up to cell I16 using the Fill Handle icon.
- Thus, we can say our leave tracker is completed and ready to use.
At last, we can say that we have completed the final task to create a leave tracker in Excel.
Read More: Employee Leave Record Format in Excel (Create with Detailed Steps)
Step 4: Verify Leave Tracker with Data
Now, we input some leave data in our months and check our formula as well as the tracker’s accuracy.
- At first, we input our employees’ names in the range of cells B10:B14.
- Then, input some data for January in the sheet Jan.
- Similarly, input some value for the months of February in sheets Feb.
- Now, if you check the Year Summary table, you will find our formula is extracting the value from the month sheet and showing us individual employees and individual leave types.
Finally, we can say that our leave teacher file worked successfully, and we can track the leave data as well as we are able to create the leave tracker.
Read More: How to Create a Recruitment Tracker in Excel (Download Free Template)
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and you will be able to create a leave tracker in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.
Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!
Related Articles
- How to Keep Track of Clients in Excel (Download Free Template)
- How to Track Attendance in Excel (with Detailed Steps)
- Create Fully Functional To Do List in Excel (4 Handy Methods)
- How to Keep Track of Invoices and Payments in Excel (3 Ideal Examples)
- Make a Sales Tracker in Excel (Download Free Template)
- How to Track Multiple Projects in Excel (Download Free Template)
Hi! Am Babalola Olona. I want to say a very big thank you to ExcelDemy for this great article as this will go a long way to help build up a lot of learners.
While I am happy that am able to learn a lot from this piece I however have challenges comprehending Step 3 “Generate Final Leave Tracker”, I really cant understand the formula used “IFERROR, INDEX, MATCH & SUM”.
I can’t also understand the rational behind the use of 0.5, “H” & in the COUNTIF function. i will be eternally grateful this FUNCTIONS can be further broken down for a better understanding.
Thank you.
Thanks a lot BABALOLA for the appreciation. It means a lot.
In response to your first question, let me break down the whole formula with IFERROR, INDEX, MATCH & SUM and explain it to you.
The first part of the formula here is IFERROR(INDEX(Jan!AH$9:AH$13,MATCH($B10,Jan!$B$9:$B$13,0)),0)
First of all the MATCH function looks through the value in range B9:B13 of Jan sheet whether it matches the value in cell B10. If it gets matched, it will return the related value according to the index from the AH9:AH13 range. The IFERROR Function is used to return a value(i.e. 0) if it can not find any proper value to return.
Similarly, I have gone through all 12 months’ sheets and added them with the SUM function.
In response to your second question, the & sign is used to concatenate cell AH$8 with the letter H. As it is considered a half match, 0.5 is multiplied. We have considered two half-matches to have the full match count.