How to Create Employee Monthly Leave Record Format in Excel (With Easy Steps)

Excel is an excellent tool for tracking different types of parameters. Leave tracker for employees is a very important part of tracking and measuring their performance in an organization. In this tutorial, we are going to see how you can create a dynamic employee monthly leave record format in Excel that auto-updates with entries.

How to Create Employee Monthly Leave Record Format in Excel (With Easy Steps)

The goal here is not just to make a chart that contains the records of leave, but to make a dynamic record and report of the entries. We will aim for a calendar-wish dynamic record that makes it easy to summarize all the leaves taken and visualize the whole situation. And we will finally add a graph at the end so that it looks more presentable and easy to get the whole story. All the steps are divided into different sections to track the sub-steps of them and their purpose easily.

Step 1: Make List for All Types of Leaves

First, create a spreadsheet that contains all of the types of leaves the organization grants. Let’s assume, for our demo leave record format, there are a total of six types of leaves. We include this in this spreadsheet.

Let’s also add a list of all the months. This, too, will occur many times after this and make the later work easier. To enter months and years, let’s input them like this first.

Then select cell E4 and press Ctrl+1 to format the cell. In the format cells box, go to the Numbers tab and select the Custom category on the left of the box. On the right side of it, type in mmmm-yyyy in the Type field.

After that, click on OK. As a result, you will find that the date format will look something like this.

Now fill up the column for the rest of the months.

Next, make a list of all the employees and their IDs that will help us track their leaves.

Read More: How to Create Leave Tracker in Excel

Step 2: Create Dataset to Record All Leaves Taken

We need a chart for all the leaves taken by employees if we want to track the monthly leave record format in Excel. To do that, let’s make a chart that can track all the employees and the leaves they have taken. We are gonna need a combination of IFERROR and VLOOKUP functions here. Follow these steps.

• We have selected the following headings for the dataset.

• Here, we want to make a drop-down style input for the employees. To do that, select the range from cell C5 to where you want to make the list and go to the Data tab.
• Then select Data Validation from the Data Tools group.

• Now in the Data Validation box, select the Settings tab, and in the Validation criteria section, select List in the Allow field, and in the Source field, manually select the IDs from the Lists spreadsheet or write down the following manually.

• Then click on OK.
• Now select cell D5 and write down the following formula.

• After that, press Enter and replicate the formula for the rest of the cells in the column by clicking and dragging the fill handle icon till the end.
• Now we want to add a list as inputs in the Leave Type column too. For that, select cell F5 at the end of the list and add data validations by selecting Data Validation from the Data Tools group in the Data group.

• Similarly, select List in Allow field, and in the Source field, write down the following.

• After that, click on OK.
• For Vacation IDs, select cell B5 and write down the following formula.

• Finally, press Enter. The format to track employee monthly leave records is now complete.

Now, fill out the formula for your leaves. You can also add these later on.

Let’s name the spreadsheet “Leave Table” before moving on to the next part of the process.

Read More: How to Create Employee Leave Record Format in Excel

Step 3: Create Dynamic Monthly Leave Record Format

Now on to the main thing, we need a calendar-like format that can record the employee monthly leave tracker in Excel. You need to enable the Developer tab first. Then follow these steps that help to break down this long process. We also need the usage of the INDEX, IF, EOMONTH, TEXT, IFERROR, VLOOKUP, COUNTIF, and SUM functions here. Follow these steps to see how to finally make an employee monthly leave record format from all of the above charts.

• First, go to the Developer tab and click on Insert. Then select Combo Box from Form Controls.

• Select a place by clicking and dragging on the spreadsheet. Then right-click on it and select Format Control from the context menu.

• Now select the Control tab in the Format Control box.
• In the input range field, write down the following formula.

• Then write down the following in the Cell link field.

• And in the Drop down lines field write down 12 as we want all of the months to show in the list.

• After that, click on OK.
• Now select the month in it and it will look something like this.

• After that, select cell Y4 and write down the following formula.

• After pressing Enter it will now look something like the figure below. These two cells will be helpful for future cell references.

• Now put in the Employee IDs and Employee Names in this spreadsheet.

• After that, select cell D7 and write the following formula in it.

• Now select cell E7 and write down the following formula in it.

• Then select the cell again and click and drag the fill handle to cell AH7.
• After that, select the range D7:AH7 and press Ctrl+1 to format them.
• Next, select the Number tab in the Format Cells box, and in the Type field of the Custom section, type in dd.

• After that, click on OK. The spreadsheet will look like this at this point.

• Then select cell D8 and write down the following formula.

• Now click and drag the fill handle icon to cell AH8 to make it look like this.

• At this instant, let’s hide the month reference and starting date.
• After that, select the range D8:AH8 and press Ctrl+1 to format.
• This time, in the Format Cells box, go to the Alignment tab and select 90 degrees as orientation.

• Now, make the fonts bold to look good, and finally, it will look something like this.

• After that, select the range D6:AH6 and select Merge & Center from the Alignment group in the Home tab.

• Now write the following in the merged cell and format it as an mmmm in the custom box.

• After that click on OK and the spreadsheet will now look like this.

• Now let’s fill it with color to make it more appealing.

• Next, select cell D9 and write down the following formula.

• After pressing Enter you will have the cell automatically filled up from the data of the previous spreadsheet.

• Now fill the formula for the whole chart by clicking and dragging the fill handle icon to the end first and then to the right.

• Now fill in these cells beside the chart as shown in the figure below.

• After that, select cell AI9 and write down the following formula.

• Now, press Enter. Next, replicate the formula till the cell AN18 by first clicking and dragging the fill handle icon down and then to the right and you will have the chart to look something like this.

• Let’s grey out the values with 0 to make the number of leaves stand out.

• Now, let’s make a column for total leaves.

• After that, select cell AO9 and write down the following formula in it.

• Once you press Enter, you will have the total leaves taken by the first employee.

• Next, select the cell again and click and drag the fill handle icon to the end of the column to replicate the formula for them and you will finally have the employee monthly leave record format ready in Excel.

Our final employee leaves record format will look something like this.

Let’s name the spreadsheet “Leave Calendar” as it represents a monthly calendar. You can also change the month from the combo box we have added and data entered in the “Leave Table” will automatically update the data to the appropriate month.

Read More: How to Track Employee Vacation Time in Excel

Step 4: Verify Employee Leave Record with Data

At this instant, let’s test if our record format is indeed dynamic and works for new data or not.

To begin with, let us first enter data in the spreadsheet we have created in step 2 called “Leave Table”.

We can see from the list above that he had already taken two sick leaves prior to this. And total leaves he had taken on the previous dataset was 2. Now go back to the employee monthly leave record format we have created in the same Excel workbook in step 3.

Keep in mind that we only changed data in the “Leave Table” sheet and it is automatically updated in the “Leave Calendar” sheet too.

Read More: How to Create Maternity Leave Calculator in Excel

Step 5: Generate Final Report

For better visualization of the whole story and to make our data more presentable, we need to plot charts from the organized data. Follow these steps to plot one from this employee’s monthly leave record format in Excel.

• First, select three ranges- B6:C18, and AI6:AN18 in the “Leave Calendar” by holding Ctrl on your keyboard and clicking and dragging with your mouse.
• Then go to the Insert tab on your ribbon and select Recommended Charts from the Charts

• As a result, the Insert Chart box will pop up. Now select the All Charts tab in it. Then on the right side of the box, select Column as this is more suitable for the visualization of this type of data. And then select the chart you prefer from the right side of the box.

• After clicking on OK, you will have a graph appear on top of the spreadsheet.

• Finally, let’s make some modifications to make it more pleasing and we will have our final product.

As we can see from the graph the fourth employee has taken 3 days of sick leave and the fifth employee has taken a day of casual leave and two days of maternity leave. Whereas the ninth employee didn’t take any leave at all. The rest of them have taken at least one leave of absence.

This chart created is also dynamic. Any data entered in the second spreadsheet also will update the graph automatically.

Download Free Template

You can download the workbook used for the demonstration of the step-by-step guide from the link below. You can also use this as a template for your customized dataset.

Conclusion

That is how you can make a dynamic employee leave record format in Excel. Hope you will be able to reliably make an employee monthly leave record format of your own in Excel. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know below.