How to Calculate the Annual Salary in Excel – 6 Steps

Formula to Calculate the Salary in Excel:

Step 1 – Create a List of Employees and a Supporting Data sheet

• Enter name and position of the employee and format the salary sheet.

• Add a sheet with supporting information.

Gender, month, and year were stored here.

 Step 2 – Prepare the Columns to Insert Data and Calculate Annual Salary

Prepare the gender, year, and month columns.

• Set a drop-down list for Gender: Go to D6.
• Go to the Data tab.
• Choose Data Validation in Data Tools.

• In the Data Validation window, select List in Allow.

• Choose the gender options for the Supporting Info sheet in the Source field.
• Click OK.

You can see a drop-down list in D6.

• Apply the drop-down list to the rest of the cells using the Fill Handle.

• Set the gender of the employees in the drop-down list.

• Add drop-down lists for the Year and Month columns.

This is the output.

• Go to E6.
• Open the Data Validation window.
• Choose the list of years as Source in the Supporting Info sheet.

A drop-down list is added to the dataset.

• To select the year, enter the following formula in E5.

• Press Enter and then drag the Fill Handle.

• In E5, choose a year from the drop-down list and it will be displayed in the rest of the cells.

Add another drop-down list for month.

• Open the Data Validation window.
• Choose the list of years as Source in the Supporting Info sheet.

A drop-down list is added to F6.

• Enter the following formula in F7.

• Press Enter and double-click the Fill Handle.

• Select a month from the drop-down list.

• Go to G6 and enter the formula.

It shows the number of days based on the month shown in F6.

• Press Enter and double-click the Fill Handle.

 Step 3 – Enter the Leave Information

• Expand the dataset to enter additional information.

• Insert the leaves manually.

• Go to J6 to calculate the number of worked days of each employee.

It is calculated based on the allowed leaves and taken leaves.

• Press Enter and double-click the Fill Handle.

• Enter the CTC of each employee manually.

Here, February 2016 was selected. Calculate the salary for that month.

• Enter the following formula in L6 to get the CTC.

• Drag down the Fill Handle.

 Step 4 – Add Salary Data

Calculate the gross salary.

• The basic salary is 50% of the CTC.
• Enter the following formula in M6.

• Press Enter and drag the Fill Handle.

• HRA is 25% of the CTC. Enter the formula in N6.

• Drag the Fill Handle.

• Medical allowance is 10% of the CTC. Use this formula in O6.

• Drag the Fill Handle.

• Enter the TA, Conveyance, and Bonus values manually.

• Calculate the total using the SUM function in S6. Use the following formula.

• Press Enter and drag the Fill Handle.

The gross salary of each employee is displayed.

Step 5 – Calculate PF, Tax & TDS and Deduct Them from Gross Salary

• Add columns on the right side for the deductions.

• Calculate the PF based on the Basic Salary – 9%. Enter the following formula in T6.

• Press Enter and drag the Fill Handle.

Enter a formula to calculate the personal tax, based on the annual income.

• Create a formula based on those values and use it in U6.

• Press Enter and drag the Fill Handle.

• Enter the TDS manually.

• Add all the deducted values using the following formula in W6.

• Press Enter and drag the Fill Handle.

 Step 6 – Calculate the Net Salary

• Enter the following formula in X6.

• Drag the Fill Handle.

Download Sample Workbook

Download the practice workbook.

<< Go Back to Salary | Formula List | Learn Excel