# How to Create a Monthly Salary Sheet Format in Excel

Here’s the dataset we’ll build on. We have some employees and their basic salary. We will calculate their net salary and deductions.

### Step 1 – Calculate the Allowances of Each Employee from the Dataset

Let’s assume that the allowances are 30% of the basic salary.

• Go to D5 and copy the following formula:
`=C5*30%`

• Press Enter.

• Use the Fill Handle to AutoFill up to D9.

### Step 2 – Use the SUM Function to Find Gross Salary

• Go to E5 and insert the following formula:
`=SUM(C5:D5)`

• Press Enter.

• AutoFill down to E9.

### Step 3 – Calculate the Provident Fund for Each Employee

Let’s assume that the salary deduction due to the provident fund is 5% of the basic salary.

• Go to C14 and input the following formula:
`=C5*5%`

• Press Enter.

• AutoFill down to E9.

### Step 4 – Apply the IFS Function to Determine the Tax Amount

Let’s assume the following conditions for the tax:

wpsm_box type=”blue” float=”none” textalign=”center”]

• If the basic salary is greater than \$1,250, the tax rate is 15% of the basic salary.
• If the salary is between \$1,000 and \$1,250, the tax rate is 10% of the basic salary.
• If the basic salary is below \$1,000, the tax rate is 0%.
[/wpsm_box]
• Go to D14 and use the following formula:
`=IFS(C5>=1250,C5*15%,C5>=1100,C5*10%,C5<1100,0)`

• Press Enter.

• Use the Fill Handle to AutoFill to D18.

### Step 5 – Calculate the Total Deduction from Gross Salary

• Go to E14 and input this formula:
`=C14+D14`

• Press Enter.

• AutoFill to E18.

### Step 6 – Calculate the Net Salary to Complete the Monthly Salary Sheet

• Go to F5 and insert the following formula:
`=E5-E14`

• Press Enter.

• Use the Fill Handle to AutoFill to F9.

## Things to Remember

• Allowances may include house rent allowance, medical allowance, travel allowances, and more, and will have to be input manually.
• For the IFS function, Excel checks the logical tests until it finds one TRUE. If the 1st logical test is TRUE, it does not check the rest of the tests.

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

Get FREE Advanced Excel Exercises with Solutions!
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF