A salary slip is crucial for the employees of a company. We can easily create a salary slip format with formulas in Excel sheets. Creating a salary slip format in Excel is an easy task. This is a time-saving task also. Today, in this article, we’ll learn three quick and suitable steps to create a salary slip format in Excel sheet with formula effectively with appropriate illustrations.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Introduction to Salary Slip in Excel
The Salary Slip can also be called the Pay Slip. An employee receives a Salary Slip, which is a receipt created by the accountant rather than an employer, at the end of each month. It includes a thorough breakdown of all of the employee’s pay components, including Company Name, Employees’ Name, Pay Slip Month, Bank Account Number, Identification Number, Basic Salary, Gross Salary, Allowances, Provident Fund, reimbursements, Bonus paid, TDS, etc., as well as deductions for a given time period, typically a month. The employee may get it by mail or on paper. Wage slips are required by law to be provided to employees on a regular basis as evidence of salary payments and deductions. These templates make it easier for HR experts, HR executives, administrative personnel, etc. to produce wage slips in a short amount of time.
3 Quick Steps to Create Salary Slip Format with Formula in Excel Sheet
Let’s assume we have an Excel large worksheet that contains the information about several Employees’ salary statements of SOFTEKO Group. The name of the Employee, the Identification Number, the Basic Salary of the Employee, and the Medical, House Rent, and Transportation allowance, Provident Fund, and Income Tax deduction are given in Columns B, C, D, E, F, G, I and J respectively. We will create a salary slip format in Excel. From our dataset, we can easily create a salary slip format of SOFTEKO Group in Excel by using the mathematical formula, the SUM function, and so on. Here’s an overview of the dataset for today’s task.
Step 1: Create Dataset for Salary Slip Format
In this portion, we will insert the details of the employees of the SOFTEKO Group such as The name of the Employee, the Identification Number, the Basic Salary of the Employee, and the Medical, House Rent, Transportation allowance, Provident Fund, Income Tax, etc.
Read More: How to Create Automatic Salary Slip Generator Using Excel
Step 2: Calculate Gross Total Using Formula in Excel Sheet
In this step, we will make a salary slip format in Excel sheet to calculate the Gross Total. We will apply the mathematical formula and the SUM function to calculate the Gross Total of the SOFTEKO Group. We can easily do that. This is also a time-saving task. Let’s say, the Medical allowance is 15%, the House rent allowance is 35%, and the Transportation allowance is 10% of the Basic salary of an employee. Let’s follow the instructions below to calculate the Gross Total!
- First of all, we will calculate the Medical allowance of the employees which is 15% of the Basic salary. To do that, firstly, select cell After that, write down the Mathematical formula in that cell. The Mathematical formula is,
=D7*15%
- Hence, simply press Enter on your keyboard. As a result, you will get Vinchant’s Medical allowance. The Medical allowance is $11,221.20.
- Further, AutoFill the Mathematical formula to the rest of the cells in column E.
- Now, we will calculate the House rent allowance of the employees which is 35% of the Basic salary. To do that, firstly, select cell After that, write down the Mathematical formula in that cell. The Mathematical formula is,
=D7*35%- Hence, simply press Enter on your keyboard. As a result, you will get Vinchant’s House rent allowance. The House rent allowance is $26,182.80.
- After that, AutoFill the Mathematical formula to the rest of the cells in column F.
- Later, we will calculate the Transportation allowance of the employees which is 10% of the Basic salary. To do that, firstly, select cell After that, write down the Mathematical formula in that cell. The Mathematical formula is,
=D7*10%- Hence, simply press Enter on your keyboard. As a result, you will get Vinchant’s Transportation allowance. The Transportation allowance is $7,480.80.
- After that, AutoFill the Mathematical formula to the rest of the cells in column G.
- At last, we will be able to calculate the Gross Total of the employees. The Gross Total salary is the summation of the Basic salary, Medical, House Rent, and Transportation allowance. To do that, we will apply the SUM function. Now, select cell H7 and write down the SUM function in that cell. The SUM function is,
=SUM(D7:G7)
- Further, again, press Enter on your keyboard. As a result, you will get Vinchant’s Gross Total salary using the SUM function. The Gross Total salary is $119,692.80.
- After that, AutoFill the SUM function to the rest of the cells in column G.
- After completing the above process, you will be able to calculate the Gross Total salary of the employees which has been given in the below screenshot.
Read More: Per Day Salary Calculation Formula in Excel (2 Suitable Examples)
Step 3: Calculate Net Payable Value to Complete Salary Slip Format
Last but not the least, we will make a salary slip format in Excel sheet with formula to calculate the Net Payable. We will calculate the Net Payable of the employees of the SOFTEKO Group. The Provident Fund and Income Tax will be deducted from the Gross Total salary which is the Net Payable salary. Let’s say, the Provident Fund is 20%, and the Income Tax deduction is 12% of the Basic salary of an employee. Let’s follow the instructions below to calculate the Net Payable!
- First of all, we will calculate the Provident Fund of the employees which are 20% of the Basic salary. To do that, firstly, select cell After that, write down the Mathematical formula in that cell. The Mathematical formula is,
=D7*20%- Hence, again, press Enter on your keyboard. As a result, you will get Vinchant’s Provident Fund The Provident Fund balance is $8,976.96.
- Further, AutoFill the Mathematical formula to the rest of the cells in column I.
- Similarly, calculate the Income Tax of the employees which has been given in the below screenshot.
- Now, we will calculate the Net Payable salary of the employees. To calculate it, write down the below Mathematical The Mathematical formula is,
=H7-I7-J7- Where H7 is the Gross total salary, I7 is the Provident Fund balance, and J7 is the Income Tax of the employee.
- Hence, simply press Enter on your keyboard. As a result, you will get Vinchant’s Net Payable salary. The Vinchant’s Net Payable salary is $101,738.88.
- Further, AutoFill the Mathematical formula to the rest of the cells in column K.
- Finally, you will be able to calculate the salary slip format in Excel which has been given in the below screenshot.
Read More: How to Create a Monthly Salary Sheet Format in Excel (with Easy Steps)
Things to Remember
👉 #N/A! error arises when the formula or a function in the formula fails to find the referenced data.
👉 #DIV/0! error happens when a value is divided by zero(0) or the cell reference is blank.
Conclusion
I hope all of the suitable methods mentioned above to create a salary slip format in Excel with formula will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
