How to Create Salary Slip Format with Formula in Excel Sheet

Get FREE Advanced Excel Exercises with Solutions!

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.


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.


Create Salary Slip Format with Formula in Excel Sheet: 3 Quick Steps

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.

salary slip format in excel sheet with formula


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.


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%

Make Salary Slip Format in Excel Sheet to Calculate Gross Total

  • 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.

Make Salary Slip Format in Excel Sheet to Calculate Gross Total

  • 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.

Make Salary Slip Format in Excel Sheet to Calculate Gross Total

  • 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.

Make Salary Slip Format in Excel Sheet to Calculate Gross Total

  • 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)

Make Salary Slip Format in Excel Sheet to Calculate Gross Total

  • 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.

Make Salary Slip Format in Excel Sheet to Calculate Gross Total

  • 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.

Make Salary Slip Format in Excel Sheet to Calculate Gross Total


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.

Generate Salary Slip Format in Excel Sheet to Calculate Net Payable

  • 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.

Generate Salary Slip Format in Excel Sheet to Calculate Net Payable

  • 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.

Generate Salary Slip Format in Excel Sheet to Calculate Net Payable


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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


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.


How to Make Salary Slip in Excel: Knowledge Hub


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo