How to Create a Monthly Salary Sheet Format in Excel

Excel is the most widely used tool when it comes to dealing with huge datasets. We can perform myriads of tasks of multiple dimensions in Excel. Sometimes we take the help of Excel to calculate monthly salary of employees. In this article, I will show you how to create a monthly salary sheet format in Excel.

Create a Monthly Salary Sheet Format in Excel: 6 Easy Steps

This is the dataset for this article. I have some employees and their basic salary. I will calculate their net salary in this format.

Step 1: Calculate Allowances of Each Employee from Dataset

First of all, I will calculate the allowances for the employees. Let’s assume that the allowances are 30% of the basic salary.

• Go to D5. Write down the following formula

• Now press ENTER. Excel will calculate the allowances.

• After that, use Fill Handle to AutoFill up to D9.

Step 2: Use SUM Function to Find Gross Salary

The next step is to calculate the gross salary. This will be the summation of Basic salary and Allowances. So I will use the SUM function.

• Go to E5 and write down the formula

• Press ENTER. Excel will calculate the gross salary.

• After that AutoFill up to E9.

Step 3: Calculate Provident Fund for Each Employee

In this section, I will calculate the provident fund per month. Let’s assume that the salary deduction due to the provident fund is 5% of the basic salary.

• Go to C14 and write down the following formula

• Press ENTER. Excel will calculate the deducted salary for PF.

• After that AutoFill up to E9.

Step 4: Apply IFS Function to Determine Tax Amount

Now I will calculate the tax amount using the IFS function. The condition is such that,

• if the basic salary is greater than $1250, the tax rate is 15% of the basic salary
• If the 1100 <= basic salary < $1000, tax rate is 10% of the basic salary
• If the basic salary is below $1000, the tax rate is 0%.
• Go to D14. Write down the following formula

• Now, press ENTER. Excel will return the output.

• After that, use the Fill Handle to AutoFill up to D18.

Step 5: Calculate Total Deduction from Gross Salary

After that, I will calculate the total deduction by adding PF and Tax.

• Go to E14 and write down the formula

• Press ENTER. Excel will calculate the Total Deduction.

• After that AutoFill up to E18.

Step 6: Calculate Net Salary to Complete Monthly Salary Sheet Format

Finally, I will calculate the net salary by subtracting the total deduction from the gross salary.

• Go to F5 and write down the formula

• Now press ENTER. Excel will calculate the net salary.

• Use the Fill Handle to AutoFill up to F9

Things to Remember

• Allowances may include house rent allowance, medical allowance, travel allowances, etc.
• Excel checks the logical tests until it finds one TRUE, If Excel finds the 1st logical test TRUE, it does not check the 2nd, 3rd, and other tests.

Download Practice Workbook

Download this workbook and practice while going through the article.

Conclusion

In this article, I have demonstrated 6 easy steps to create a monthly salary sheet format in Excel. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment down below.

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