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 that will be very useful in any Excel-related task.


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

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


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

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 copy this formula to the cells below.


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)


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: Payroll Exercises in Excel


Download Practice Workbook

You can download the practice workbook from here.


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. If you have any queries, please let me know in the comments.


<< Go Back to Payroll | 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.
Nazmul Hossain Shovon
Nazmul Hossain Shovon

Nazmul Hossain Shovon, a BUET graduate in Naval Architecture and Marine Engineering, embarked on his career with 8 months dedicated to the Exceldemy project's triumph. Transitioning into a Software Developer role, he specialized in web add-in development. At Exceldemy, he authored about 125 blog articles and solved many visitors’ problems, refining his writing skills and delving into Excel-related topics. With a primary passion for programming and software development, Shovon continually explores new horizons, fostering professional growth in his... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo