How to Calculate Net Salary in Excel (With Easy Steps)

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.


Download Practice Workbook

Download the following workbook to practice by yourself.


Step by Step Procedures to Calculate Net Salary in Excel

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 by Step Procedures to Calculate Net Salary in Excel

Read More: How to Create Salary Slip Format with Formula in Excel Sheet


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 by Step Procedures to Calculate Net Salary in Excel

Read More: How to Calculate HRA on Basic Salary in Excel (3 Quick Methods)


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.

Insert Data Validation 

  • 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.

Insert Data Validation To Calculate Salary in Excel

  • Finally, we insert the dataset range in the Source option.
  • The range of datasets is:
=DATASET!$B$5:$B$12

Insert Data Validation To Calculate Salary in Excel


Similar Readings


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.

Step by Step Procedures to Calculate Net Salary in Excel

  • 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)

Step by Step Procedures to Calculate Net Salary in Excel

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 B5 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

Step by Step Procedures to Calculate Net Salary in Excel

=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:

Read More: How to Create a Monthly Salary Sheet Format in Excel (with Easy Steps)


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:

=$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.

Step by Step Procedures to Calculate Net Salary in Excel

  • 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 by Step Procedures to Calculate Net Salary in Excel

Read More: How to Calculate Monthly Salary in Excel (with Easy Steps)


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:

Step by Step Procedures to Calculate Net Salary in Excel

  • 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:

Step by Step Procedures to Calculate Net Salary in Excel

  • Lastly, select cell C8 and use the below formula:
=C6-C7
  • The final outcome will look like the below image:

Step by Step Procedures to Calculate Net Salary in Excel

Read More: How to Make Salary Sheet in Excel with Formula (with Detailed Steps)


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.


Related Articles

Farzana
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo