How to Make a Payroll System with Payslip in Microsoft Excel – 4 Steps

The sample dataset has 6 rows and 8 columns. Cells with monetary values are in Accounting format.

Step 1 – Creating a Dataset

• Enter the Employee names in the first column.
• Create 4 more columns for Pay/Hour, Total Hours worked, Overtime/Hour, and Total Overtime.
• Enter data values.

Step 2 – Finding the Net Pay

Formulas will be used to find the Gross Pay, Income Tax (15%), and Net Pay.

• In G5, enter the formula below:

• Press Enter and copy the formula using the Fill Handle.

• In H5, enter the formula below:

• Press Enter and copy the formula using the Fill Handle.

• Enter the formula below in I5:

• Press Enter and copy the formula using the Fill Handle.

Step 3 – Calculating Total Values

• In D11, enter this formula:

• In F11, enter this formula:

• In G11, enter this formula:

• Press Enter.

• Enter the formula below in H11 and press Enter.

• Double-click I11 and enter this formula:

Step 4 – Creating the Employee Payslip

• Enter the starting fields: Employee Name, Gender, Earnings, Paid Days, and LOP Days as shown below.
• Enter the values. Here, Daniel Smith is the sample employee.

• Enter the values for Basic pay, Overtime Hours, and Overtime Rate.
• In C10, enter the following formula:

• Use this formula in C11:

• Enter the Salary Advance and Other Deduction fields with their respective values.

• Enter the Tax and Loan fields and their monetary values.

• In E10, use the following formula:

• Enter the Net Pay formula in E11 and press Enter:

Read More: Payroll Exercises in Excel

Download Practice Workbook

Download the practice workbook here.

<< Go Back to Formula List | Learn Excel