Calculation of net salary in excel is one of the widely used features in excel. A company always has a huge amount of employee salary data with different types of salary structures. A company can get a lot of benefits due to this flexible practice to calculate net salary in excel. The net salary of an employee including bonus, profit, allowances, leave without payment and other deductions can be calculated smoothly in excel. In this particular article, you will be able to get step-by-step instructions to calculate net salary in Excel.

## Calculate Net Salary in Excel: Step by Step Procedures

**Step 1: Employee Database & Basic Salary Structure**

Here the below figure is showing the basic salary structure of a company. And we want to calculate the net salary by adding allowances and subtracting deductions subsequently.

**Step 2: Create Allowance and Deduction Structure**

Here, the below figure is showing the allowance percentages of basic salary given by the company among which medical expenses, yearly profit bonus, festival bonus, house rent are included and deduction percentage of basic salary made due to provident fund and income tax.

**Step 3: Apply Data Validation Feature**

- Firstly, select cell
**B5**. - Secondly use the
**Data Validation**feature in this cell.

- Then, we go to the
**Data Tab**. - From the
**Data Tools**section, we select the**Data Validation**option.

- Once we select the
**Data Validation**option, the**Data Validation**window will instantly pop up.

- Thereupon, from The feature
**Allow**in the**Data Validation**window, we select**List.** - Then click
**OK.**

- Finally, we insert the dataset range in the
**Source**option. - The
**range of datasets**is:

`=DATASET!$B$5:$B$12`

**Step 4: Calculating Gross Salary in Excel**

**Gross Salary**is the summation of**basic salary**and**allowance**.- At first, we use the
**Data Validation**feature. - Now a specific employee can be chosen from the list.
- Suppose we want to calculate the salary of
**Catherine Mitchell**.

**At first,**select the**Dataset range**.- Then
**the VLOOKUP function**of Excel is used. - Use the
**below formula**Â in cell**C5:**

`=VLOOKUP(B5,DATASET!B5:C12,2,FALSE)`

- Now, we can get medical expenses usingÂ the
**VLOOKUP**function in**cell C7.** - Finally, we get the
**Basic Salary**ofÂ**â€˜Catherine Mitchellâ€™.** - Now, to get medical expense
**the below formula**is used:

`=$C$5*VLOOKUP(B7,'ALLOWANCE AND DEDUCTION'!$E5:$F8,2,FALSE)`

**How Does the Formula Work?**

**VLOOKUP(B5,DATASET!B5:C12,2,FALSE)**

The function searches for value cell **B6**Â cell according to the **Database** values of employees in worksheets ranging from **B****5 to C12**. It returns the respective **2nd **columns result from the following selection where the **B6**Â cellâ€™s value is found.

**$C$5*VLOOKUP(B7,’ALLOWANCE AND DEDUCTION ‘!$E5:$F8,2,FALSE)**

**$C$6** value is then multiplied by the result. The result is **$200.00**

- The
**total value of allowances**can be calculated using**the SUM function:**

`=SUM(C7:C10)`

- Then to get the
**Gross Salary**we use the**SUM**function in cell**C6**and cell**C14.** **Total Allowances**through the**below formula:**

`=SUM(C5, C11)`

- The result will look like the
**below image:**

- Final outcome of the Gross Salary Calculation
**Â**will look like the**below image:**

**Step 5: Deduction Calculation**

- Firstly,Â we use the
**Data Validation**feature in cell**B5.** - Then to calculate
**the Basic Salary**use**the below formula:**

`=VLOOKUP(B5,DATASET!B5:C12,2,FALSE)`

- The result will look like the
**image below:**

- Select cell
**C8.** - Now to calculate the income tax use the below
**VLOOKUP**function formula:

`=$C$5*VLOOKUP(B7,'ALLOWANCE AND DEDUCTION'!$E11:$E12,2,FALSE)`

**How Does the Formula Work?**

**VLOOKUP(B8,’ALLOWANCE AND DEDUCTION ‘!$E11:$E12,2,FALSE)**

The **VLOOKUP** function searches for value cell **B8** in range E11:E12. It returns the respective 2nd columns result from the following selection where we found the B8 cellâ€™s value.

**$C$6*VLOOKUP(B8,’ALLOWANCE AND DEDUCTION ‘!$E5:$F8,2,FALSE)**

The **$C$6** value is then multiplied by the result. The result is **$240.00.**

- After that, use the
**Fill Handle**tool. - Finally, we can get the
**provident fund**amount as well.

**At last**, we use the**SUM**function in cell**C11**to get the total deduction:

`=SUM(C7:C8)`

**The final result**will look like the below image:

**Step 6: Calculating Net Salary**

- Now we can calculate
**Net Salary**. - Firstly, we
**copy and paste the link**to the**Gross Salary**inÂ the**Â Net Salary**sheet. - we use the
**below****formula:**

`='GROSS SALARY CALCULATION'!$C$12`

- The result will look like the
**below****image:**

- Secondly, we copy and paste
**the link into the net salary sheet.** - we use the below
**formula:**

`='DEDUCTION CALCULATION'!$C$9`

- The outcome will look like the
**below****image:**

**Lastly,**select cell**C8**and useÂ the**below formula:**

`=C6-C7`

**The final outcome**Â will look like the below image:

**Download Practice Workbook**

Download the following workbook to practice by yourself.

## Conclusion

Using the above step-by-step procedure, any company can calculate the net salary of employees in Excel. If we can follow the procedure properly,Â we get all the net salary amounts of employees in the most time-consuming way. You can follow Exceldemy.com for more procedures to get help with the calculation related toÂ Salary and let us know in the comment section below if you need help.

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