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

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.


Download Practice Workbook

Download this workbook and practice while going through the article.


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

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

monthly salary sheet format in excel


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
=C5*30%

  • Now press ENTER. Excel will calculate the allowances.

monthly salary sheet format in excel

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


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
=SUM(C5:D5)

monthly salary sheet format in excel

  • Press ENTER. Excel will calculate the gross salary.

  • After that AutoFill up to E9.

monthly salary sheet format in excel

Read More: Per Day Salary Calculation Formula in Excel (2 Suitable Examples)


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
=C5*5%

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

monthly salary sheet format in excel

  • 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
=IFS(C5>=1250,C5*15%,C5>=1100,C5*10%,C5<1100,0)

monthly salary sheet format in excel

Formula Explanation:

  • The first logical test is C5>=1250, which is TRUE. So Excel will not check other tests and return the output as C5*15%.
  • Now, press ENTER. Excel will return the output.

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

monthly salary sheet format in excel


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
=C14+D14

  • Press ENTER. Excel will calculate the Total Deduction.

monthly salary sheet format in excel

  • 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
=E5-E14

monthly salary sheet format in excel

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

  • Use the Fill Handle to AutoFill up to F9

monthly salary sheet format in excel

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


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.

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.


Related Articles

Akib
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo