How to Calculate Annual Leave in Excel (with Detailed Steps)

When an employee works in an organization, he earns some leaves based on criteria. Leave is an essential part of corporate or job life. We can track and calculate the leave of each employee in a proper way using Excel. Let’s have a look at the entire process to calculate annual leave in Excel.

Steps to Calculate Annual Leave in Excel

We know that there are multiple types of leave an employee enjoy. Those leaves may vary from one company to another. In this article, we will show how to calculate each leave separately. Each step is discussed in detail in the below section.

📌 Step 1: Insert Employee Information

We are going to calculate the annual leaves of an organization consisting of 10 employees. In this section, we will insert all the basic information in Excel now.

  • First, we input the Name and IDs of employees.

📌 Step 2: Create a List of Leaves Allowed by Company

  • Now, we will input the names of months in the dataset.

  • Before inserting the month name, change the format of the cells. Select all the cells of the Month column.
  • Press the right button of the mouse.
  • Click on the Format Cells option.

Create a List of Leaves Allowed by Company for annual leave

  • Click on the Number tab.
  • Choose our desired Type from the Custom field.

Create a List of Leaves Allowed by Company for annual leave

  • Finally, press the OK button.

  • Now, we add a list of leaves with their codewords.

📌 Step 3: Create a Leave Record

In this section, we will record the leaves of each employee in the dataset. This information will be further used to calculate monthly and annual leaves. We need to use data validation and some formulas in this section.

  • First, we create a worksheet with different column names.

  • Now, apply Data Validation for the Employee ID column.
  • Click on Cell C5.
  • Go to the Data tab.
  • Click on the Data Validation option from the Data Tools group.

Create a Leave Record in Excel

  • The Data Validation window appears here.
  • Choose List in the Allow box.
  • Our source sheet is where we saved the basic information. We choose the Employee ID cells here.

 Create a Leave Record in Excel

  • Press the OK button last.

We can see a drop-down button beside Cell C5.

  • Extend this Data Validation to all the cells of that column.
  • Click on the Drop-down button and see the options.

 Create a Leave Record in Excel

  • Similarly, way to apply data validation for the Leave Code column.
  • Here, our source is the cells of the leaves type.

 Create a Leave Record in Excel

  • Data Validation was successfully applied for both columns.

  • In the Name column, we will apply a VLOOKUP formula that will extract the names of employees based on the Employee ID.
  • Apply the following formula to Cell D5.
=IFERROR(VLOOKUP(C5,'Data Set'!B5:C14,2,FALSE),"")

 Create a Leave Record in Excel

  • We will use another formula based on Employee ID and leave Date.
  • Apply the formula below to Cell B5.

This form a unique ID combining the Employee ID and Date value.

  • Now, input leaves information on the 1st row.

 Create a Leave Record in Excel

  • Finally, all leave information of the employees and sort them date-wise.

📌 Step 4: Calculate Monthly Leaves

Now, we will calculate the monthly leave of each employee for each month indicating the dates on the calendar. We will use the Control Box option here to control the month selection.

  • Click on the Developer tab first.
  • Now, choose Combo Box from the Insert group.

Calculate Monthly Leaves in Excel

  • Now, place the Control Box in a certain position.
  • Press the right button of the mouse.
  • Click Format Control from the Context Menu.

  • Click on the Control tab.
  • We select a range at the Input range This range contains the name of the month.
  • We will link a cell, that will show the serial number of that month based on the source.
  • In the drop-down list, we put 12, because of 12 months of the year.

Calculate Monthly Leaves in Excel

  • Finally, press OK.

We can see the selected month from the drop-down and the corresponding month number is showing.

  • We input a formula on Cell S4.
=INDEX('Data Set'!E5:E16,'Monthly Leave Record'!M4)

Calculate Monthly Leaves in Excel

This will extract the starting date of the month the depending on month number.

  • Input the ID and Name in the columns in the dataset.

  • Go to cell D7 and input S4.

  • Go to cell E7 and put the following formula.

Calculate Monthly Leaves in Excel

  • Press the Enter button.

We need to change the date format. That will help in the visibility of the dataset.

  • Choose our desired format from the Custom option.

  • Now, expand the cells to a total of 31 cells for 31 days of the month.

  • We want to get the names of the days of the corresponding dates.
  • Put the following formula on Cell D8.

Calculate Monthly Leaves in Excel

  • Hit Enter and expand that formula to the rest of the cells on the right side.

  • Change the alignment of days.
  • Click the Alignment tab.
  • Rotate 90 degrees.

Calculate Monthly Leaves in Excel

  • Look at the dataset.

  • Now, select Range D6:AH6.
  • Click on Merge & Center.

  • Input S4 on that cell.

  • Look at the worksheet.

The start of the month is showing. But we need only the month’s name.

  • Change the format of that cell from the Format Cells window.

  • Only the month name is showing now.

  • Now, change the font and background color on the rows based on elements.

  • Now, we will show the date-wise leaves of each employee.
  • Put the following formula on Cell D9.

Calculate Monthly Leaves in Excel

  • Expand the formula to all cells of the month section.

We can see leaves with corresponding codes showing.

  • Now change the month name from March to February.

Leave dates have changed successfully. Now, we will calculate each type of leave for each employee.

  • Add 5 new columns with leaves names and codes.

  • Put the following formula on Cell AI19.

Calculate Monthly Leaves in Excel

  • Expand that formula to the rest of the cells.

  • Now, we will apply Conditional Formatting that will show 0 (zero) values as shades.
  • Select Range AI9:AM18.
  • Go to the Conditional Formatting group.
  • Choose Equal To from the Highlight Cells Rules.

Calculate Monthly Leaves in Excel

  • Put 0 on the marked box and select a Custom Format.
  • Again, press the OK button.

Calculate Monthly Leaves in Excel

  • Finally, we will calculate the leaves of each employee.

  • Add a new column named Total.
  • Got o cell AN9 and put the following formula.

Calculate Monthly Leaves in Excel

  • Drag the Fill Handle icon.

We get the total leaves of each employee for a specific month.

📌 Step 5: Calculate Annual Leaves

In this section, we will calculate the annual leave.

  • We copy the last worksheet and customize a new worksheet for calculation.

  • We have two cells for Start and End We input dates here.
  • As we want to calculate for the whole year, we start on 1st January 2022 and end on 31st December 2022.

  • Now, we input a formula based on the COUNTIFS function at cell D9.


=COUNTIFS(Record!$E:$E,">="&'Annual Leave'!$E$4,Record!$E:$E,"<="&'Annual Leave'!$G$4,Record!$C:$C,'Annual Leave'!$B9,Record!$F:$F,'Annual Leave'!D$8)

Calculate Annual Leaves

  • Press the Enter button.

  • Expand the formula to the whole leave area.

  • Apply a SUM formula at cell I9 for total leaves.

  • Press the Enter button and drag the Fill Handle icon.

Calculate Annual Leaves

  • Again, apply the conditional formatting to disappear the 0 (zero).

Download Sample Workbook

Download this practice workbook to exercise while you are reading this article.


In this article, we made a template of 10 employees to calculate annual leave in Excel in the simplest format.

<< Go Back to Leave Calculation | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

  1. Hi,

    I try use below mentioned formula but why I cant add 2 lookup value..($B9&D$7)


    • Hello KO,
      Thank you very much for your comment. The formula you mentioned in the comment is working smoothly from our side. If you share your workbook, we will be able to find out the solution to your problem. You can mail us at [email protected].

  2. Is there any formula for a drop down list using different type of leaves in the attendance sheet that data will not able to move if I jump in to different month.

    • Dear Prince,
      Thank you very much for reading our article.
      According to your query, 1st you wanted to know about a formula to create a drop-down list that will be used to select different types of leaves. You will get that in Step 3 of this article. In our Excel file, we selected leave using the drop-down list in Record sheet. Also when you move to any month leave information will be based on the Record sheet. So, information will not move from one month to another. Try this and hope you will get the solution.
      Otherwise, send your Excel file with what you want to get and we will try to provide a solution. You can mail us at [email protected].
      Joyanta Mitra

Leave a reply

Advanced Excel Exercises with Solutions PDF