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

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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. ### 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.  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)` • Press ENTER. Excel will calculate the gross salary. • After that AutoFill up to E9. 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. • 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)` 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. ### 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. • 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` • Now press ENTER. Excel will calculate the net salary. • Use the Fill Handle to AutoFill up to F9 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 Bin Rashid

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  