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.


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.

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.

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%

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

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.

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

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.


Related Articles

Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo