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.
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.
- After that, use Fill Handle to AutoFill up to D9.
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
- How to Calculate DA on Basic Salary in Excel (3 Easy Ways)
- Create Salary Slip Format with Formula in Excel Sheet
- How to Create Tally Salary Slip Format in Excel (With Easy Steps)
- Calculate Bonus on Salary in Excel (7 Suitable Methods)
- How to calculate salary increase percentage in Excel [Free Template]
- How to Create Automatic Salary Slip Generator Using Excel
