How to Make a Payroll System in Microsoft Excel with Payslip

Get FREE Advanced Excel Exercises with Solutions!

In this tutorial, I am going to show you the step-by-step procedures on how to make a payroll system in Microsoft Excel with a payslip. You can use these steps for any type of dataset to generate simple and reusable payrolls. Throughout this tutorial, you will also learn some important excel tools and techniques which will be very useful in any excel related task.


Download Practice Workbook

You can download the practice workbook from here.


Step-by-Step Procedures to Make a Payroll System in Microsoft Excel with Payslip

We have taken a concise dataset to explain the steps clearly. The dataset has approximately 6 rows and 8 columns. Initially, we are keeping all the cells in General format and the cells with monetary values in Accounting format. For all the datasets, we have 8 unique columns which are Employee, Pay/Hour, Total Hours Worked, Overtime/Hour, Total Overtime, Gross Pay, Income Tax, and Net Pay. Although we may vary the number of columns later on if that is needed.


Step-1 Creating Base Dataset of Payroll System

In this first step, we will create our starting dataset which we will use to make a complete payroll system in Microsoft Excel with a payslip.

  • First, insert the Employee names in the first column.
  • Then, create 4 more columns for Pay/Hour, Total Hours Worked, Overtime/Hour, and Total Overtime as in the image below.
  • Next, type in the necessary data values in the appropriate columns.

how to make a payroll system in microsoft excel with payslip

Read More: How to Calculate Hours and Minutes for Payroll Excel (7 Easy Ways)


Step-2 Finding Net Pay

Here, we will be applying some simple formulas to find the Gross Pay, Income Tax (15%), and Net Pay.

  • To begin with, go to cell G5 and insert the formula below:
=C5*D5+E5*F5
  • Now, press Enter and copy this formula down using Fill Handle.

finding net pay to make a payroll system in Microsoft Excel with a payslip

  • Next, navigate to cell H5 and enter the formula below:
=0.15*G5
  • Then, press the Enter key and copy this formula down to other cells.

  • After that, type in the formula below in cell I5:
=G5-H5
  • Then, press Enter and also copy this formula to the cells below.

Read More: How to Make Payroll in Excel (with Easy Steps)


Step-3 Calculating Necessary Sum Values

Let us now perform the necessary calculations to find out the total values of all the fields in the dataset.

  • To begin this step, go to cell D11 and type in this formula:
=SUM(D5:D10)

calculating sum values to make a payroll system in Microsoft Excel with a payslip

  • Now, navigate to the cell F11 and type in the following formula:
=SUM(F5:F10)

total overtime hours to make a payroll system in Microsoft Excel with a payslip

  • After that, insert this formula inside cell G11 and press Enter:
=SUM(G5:G10)

total gross pay to make a payroll system in Microsoft Excel with a payslip

  • Next, type in the formula below in cell H11 and again press Enter to confirm:
=SUM(H5:H10)

  • Now, double-click on cell I11 and type in this formula:
=SUM(I5:I10)

Read More: How to Create Payroll Calculator in Excel (with Easy Steps)


Step-4 Creating Employee Payslip

Now, in this final step, we will make a simple payslip for a sample employee which you can extend to other employees.

  • For this, type the starting fields as Employee Name, Gender, Earnings, Paid Days, and LOP Days as in the image below.
  • Then, enter the values for the individual employee. Here we have considered Daniel Smith as a sample employee.

payslip base dataset to make a payroll system in Microsoft Excel with a payslip

  • Next, enter the values for Basic pay, Overtime Hours, and Overtime Rate.
  • Now, go to cell C10 and insert the following formula:
=C8*C9

  • Then, type in this formula in cell C11:
=C10+C7

  • Next, insert the Salary Advance and Other Deduction fields with their respective values.

  • Then, insert the Tax and Loan fields as well as their monetary values.

  • Now, go to cell E10 and type in the following formula:
=SUM(E6:E9)

  • Finally, enter the Net Pay formula inside cell E11 and press Enter:
=C11-E10

Read More: How to Calculate Payroll Overtime with Formula in Excel


Conclusion

I hope that you were able to apply the methods that I showed in this tutorial on how to make a payroll system in Microsoft Excel with a payslip. As you can see, it involves quite a few steps to achieve this. So carefully follow these while applying them to your own dataset. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.


Related Articles

Nazmul Hossain Shovon

Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo