# How to Calculate the Annual Salary in Excel – 6 Steps

Formula to Calculate the Salary in Excel:

Net Salary = Gross Pay – Total Allowances

### 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.
`=IF(\$B7=0," ",\$E\$6`)

• 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.
`=IF(\$B7=0," ",\$F\$6)`

• Press Enter and double-click the Fill Handle.

• Select a month from the drop-down list.

• Go to G6 and enter the formula.
`=IF(F6="January",31,IF(F6="February",28,IF(F6="March",31,IF(F6="April",30,IF(F6="May",31,IF(F6="June",30,IF(F6="July",31,IF(F6="August",31,IF(F6="September",30,IF(F6="October",31,IF(F6="November",30,IF(F6="December",31," "))))))))))))`

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.
`=IF(B6=0,"",ROUND(K6/G6*J6,-1))`

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.

`=IF(B6=0,"",ROUNDDOWN(K6/G6*J6,0))`

• Drag down the Fill Handle.

### Step 4 – Add Salary Data

• The basic salary is 50% of the CTC.
• Enter the following formula in M6.
`=ROUNDDOWN(\$L6*50%,0)`

• Press Enter and drag the Fill Handle.

• HRA is 25% of the CTC. Enter the formula in N6.
`=ROUNDDOWN(\$L6*25%,0)`

• Drag the Fill Handle.

• Medical allowance is 10% of the CTC. Use this formula in O6.
`=ROUNDDOWN(\$L6*10%,0) `

• 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.
`=SUM(M6:R6)`

• 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 Salary9%. Enter the following formula in T6.
`=ROUNDUP(\$M6*9%,0) `

• 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.
`=(IF(M6*12>523600,157804.25+(M6*12-523600)*37%,IF(M6*12>209425,47843+(M6*12-209425)*35%,IF(M6*12>164925,33603+(M6*12-164925)*32%,IF(M6*12>86375,14751+(M6*12-86375)*24%,IF(M6*12>40525,4664+(M6*12-40525)*22%,IF(M6*12>9950,995+(M6*12-9950)*12%,IF(M6*12<=9950,M6*12*10%,""))))))))/12`

• Press Enter and drag the Fill Handle.

• Enter the TDS manually.

• Add all the deducted values using the following formula in W6.
`=SUM(T6:V6)`

• Press Enter and drag the Fill Handle.

### Step 6 – Calculate the Net Salary

• Enter the following formula in X6.
`=\$S6-\$T6`

• Drag the Fill Handle.

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

Get FREE Advanced Excel Exercises with Solutions!
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

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF