How to Make a Payroll System in Microsoft Excel with Payslip

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.

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:

• Now, press Enter and copy this formula down using Fill Handle.

• Next, navigate to cell H5 and enter the formula below:

• Then, press the Enter key and copy this formula down to other cells.

• After that, type in the formula below in cell I5:

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

• Now, navigate to the cell F11 and type in the following formula:

• After that, insert this formula inside cell G11 and press Enter:

• Next, type in the formula below in cell H11 and again press Enter to confirm:

• Now, double-click on cell I11 and type in this formula:

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.

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

• Then, type in this formula in cell C11:

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

• Finally, enter the Net Pay formula inside cell E11 and press Enter:

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

• How to Make a Payroll Accrual Calculator in Excel
• Generate Payroll in Excel VBA (with Easy Steps)
• How to Do Payroll Reconciliation in Excel (with Easy Steps)
• Payroll Exercises in Excel: 5 Tricky Problems