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

### 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`

• 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)`

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

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

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

• 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

## 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, 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

Advanced Excel Exercises with Solutions PDF