Microsoft Excel has huge uses in different sectors. Mostly, medium and small enterprises and companies use Microsoft Excel to save costs from using paid software. In this article, we will show how to calculate annual salary in Excel with detailed steps.

**Watch Video – Calculate Annual Salary in Excel**

**Calculate Annual Salary in Excel: Simple Steps**

In the below section, we will show each step to calculate the annual salary of employees of any organization. We are considering a dataset that comprises **10 **employees. We will show the **CTC**, gross salary, deduction from salary, and finally the net salary.

**Formula to Calculate Salary in Excel:**

Salary is the payment of an employee paid by the owing company. But there is some deduction from the salary like **PF**, income tax, etc. The formula to calculate the Net salary is given below.

**Net Salary = Gross Pay – Total Allowances**

**📌 ****Step 1: Make a List of Employees with Supporting Data**

- First, we collect the name and positions of the employee in the dataset. We also set the format of the salary sheet.

- Now, we add another sheet of supporting information in the Excel file.

So, we have stored gender, month, and year information here.

**📌 ****Step 2: Prepare Main Sheet Columns to Insert Data and Calculate Annual Salary**

In this section, we will prepare the gender, year, and month column of Excel cells to insert information. We will apply the drop-down list feature to choose gender, year, and month.

- First, we will set a drop-down list for the
**Gender**Go to**Cell D6**. - Then click on the
**Data**tab. - Then choose
**Data Validation**from the**Data Tools**section.

- The
**Data Validation**window appears. - Now, select
**List**in the**Allow**field.

- Next, choose the gender options from the
**Supporting Info**sheet in the**Source**field. - Finally, click on
**OK**.

- Look at the data set now.

We can see a drop-down list on **Cell D6**.

- After this, expand the drop-down list to the rest of the cells using the
**Fill Handle**icon.

- Now, set the gender of the employees from the drop-down list.

Similarly, we will add drop-down lists for the **Year **and **Month **columns.

- View the rest of the columns.

- Now, go to
**Cell E6**of the**Year**column. - Then enter the
**Data Validation**window as shown before. - After that, choose the list of years as the
**Source**from the**Supporting Info**sheet.

- We can see a drop-down list is added to the dataset.

- We want to select the year one time for each sheet. So, we put the following formula that will follow
**Cell E5**.

**)**

`=IF($B7=0," ",$E$6`

The year value showing on **Cell E5** will reflect on the rest of the cells.

- Now, press the
**Enter**button and then drag the**Fill Handle**icon.

- After that, set a year on
**Cell E5**from the drop-down list and that will reflect on the rest of the cells.

- Next, add another drop-down list for the month.
- Now, follow the steps of
**Data Validation**as shown before. - Then choose the
**Source**from the**Supporting Info**sheet.

- Look at the following image. A drop-down list there has been added to
**Cell F6**

- Now, put the following formula on
**Cell F7**.

`=IF($B7=0," ",$F$6)`

- Finally, press the
**Enter**button and double-click on the**Fill Handle**icon.

- After that, select the desired month from the drop-down list.

- Go to
**Cell G6**and put the below 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," "))))))))))))`

This formula shows the number of days based on the month shown on **Cell F6**.

- Press the
**Enter**button and pull the**Fill Handle**icon.

We have already inserted the general information of each employee in the salary sheet.

Now, we will add the **CTC **and leave information of the employees.

**📌 ****Step 3: Insert Leave Information**

- First, expand the dataset to insert the rest of the information.

- Manually insert the leaves of a certain month.

- Move to
**Cell 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 the
**Enter**button and pull the**Fill Handle**icon.

- Then, enter the
**CTC**of each employee manually.

Here, we selected **February 2016**. We will calculate the salary of employees for that month.

- Put the following formula on
**Cell L6**to get the**CTC**for each employee for specific months considering leaves.

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

- Now, drag the
**Fill Handle**icon downwards.

### 📌 Step 4: Add Salary Data

In this step, we will calculate the gross salary. Gross salary is divided into some components. Basic salary, House Rent Allowance (HRA), Medical allowance, TA, Conveyance, etc. are the components of gross salary. In the following section, we will show the calculation of gross salary.

- First, the basic salary which is
**50%**of the**CTC**. - Put the following formula on
**Cell M6**for that.

`=ROUNDDOWN($L6*50%,0)`

- Press the
**Enter**button and drag the**Fill Handle**icon.

- On the other hand,
**HRA**is**25%**of the**CTC**. Put the below formula on**Cell N6**.

`=ROUNDDOWN($L6*25%,0)`

- Double-click the
**Fill Handle**icon.

- Medical allowance is
**10%**of the**CTC**. Apply this formula to**Cell O6**.

`=ROUNDDOWN($L6*10%,0) `

- Again, drag the
**Fill Handle**icon.

- This time, insert the
**TA**,**Conveyance**, and**Bonus**values manually.

- Now, calculate the total using the
**SUM function**on**Cell S6**. Apply the following formula.

`=SUM(M6:R6)`

- Press the
**Enter**button and drag the**Fill Handle**icon.

We get the gross salary of each employee.

**📌 ****Step 5: Calculate PF, Tax & TDS and Deduct Them from Gross Salary**

We calculated the gross salary in the previous section. But this is not the net salary. We have to deduct the **Provident Fund** (**PF**), **Personal Tax**, **and TDS **from this gross salary.

- First, add columns on the right side for the deduction.

- We calculate the
**PF**based on the**Basic Salary**which is**9%**. Put the following formula on**Cell T6**.

`=ROUNDUP($M6*9%,0) `

- Press the
**Enter**button and drag the**Fill Handle**icon.

After that, we put a formula to calculate the personal tax. This is based on the annual income of an employee. There are different levels of tax based on income quantity.

- First, we create a formula based on those values and insert that into
**Cell 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`

- After that, press the
**Enter**button and drag the**Fill Handle**icon.

- After that, put the
**TDS**manually.

- Then, add all the deducted values using the following formula on
**Cell W6**.

`=SUM(T6:V6)`

- Finally, press
**Enter**and drag the**Fill Handle**icon.

### 📌 Step 6: Calculate Net Salary

Now, we will use this deducted value to calculate the net salary.

- Put the following formula on
**Cell X6**.

`=$S6-$T6`

- Finally, drag the
**Fill Handle**icon.

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

**Conclusion**

In this article, we described all the steps of how to calculate annual salary in Excel. I hope this will satisfy your needs.

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