Requirements of The Project

Calculate salary sheet in excel based on these requirements. Write formulas and functions as per conditions.

  1. House rent (HR) 50% of basic
  2. Medical allowance (MA) 10% of basic.
  3. Provident fund (PF) 10% of basic.
  4. Income Tax (IT) 0 if basic is under 2000

Income Tax 5% if basic is 2000 to 5000

Income Tax 10% if basic is more than 5000

  1. PF and Income Tax are not payable with net salary
  2. Finding net salary.

Solution

Note: Write down the basic salary of each employer’s in c column. Suppose the basic salaries are in the screenshot.

salary sheet in excel

salary sheet in excel

Finding House rent

1.Write C2*50% in D2 cell

finding percentages in excel

Apply this rule in every cell of house rent column

Finding Medical Allowance

2.Write C2*10% in E2 cell

how to add percentage in excel

Apply this rule in every cell of Medical Allowance column

Finding PF

3.Write C2*10% in F2 cell

excel formula to calculate percentage of grand total

Apply this rule in every cell of PF column

Finding IT

4.To find income Tax type this rule in G2 cell

=If(C2<2000,0,IF(AND(C2>=2000,C2<=5000),C2*5%,IF(C2>5000,C2*10%)))

how to make salary sheet in excel with formula

Note: Now we’ll get the result automatically from D2 to G2 cell

  1. Select D2:G2
  2. Keep press “Fill Handle” under selection take to G10 cell

Finding Net Salary

[ PF and Income Tax are not payable with net salary ]

=SUM(C2:E2)-SUM(F2:G2)

how to calculate net pay

  1. Now it’s a complete salary sheet.
complete salary sheet in excel

complete salary sheet in excel

Instant share

Learn More