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:

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

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

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

• Go to D14 and use the following formula:

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

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

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

Download Practice Workbook

Download this workbook and practice while going through the article.

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