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

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

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

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