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.

how to make a payroll system in microsoft excel with payslip


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:
=C5*D5+E5*F5

finding net pay to make a payroll system in Microsoft Excel with a payslip

  • In H5, enter the formula below:
=0.15*G5

  • Enter the formula below in I5:
=G5-H5


Step 3 – Calculating Total Values

  • In D11, enter this formula:
=SUM(D5:D10)

calculating sum values to make a payroll system in Microsoft Excel with a payslip

  • In F11, enter this formula:
=SUM(F5:F10)

total overtime hours to make a payroll system in Microsoft Excel with a payslip

  • In G11, enter this formula:
=SUM(G5:G10)
  • Press Enter.

total gross pay to make a payroll system in Microsoft Excel with a payslip

  • Enter the formula below in H11 and press Enter.
=SUM(H5:H10)

  • Double-click I11 and enter this formula:
=SUM(I5:I10)


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.

payslip base dataset to make a payroll system in Microsoft Excel with a payslip

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

  • Use this formula in C11:
=C10+C7

  • 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:
=SUM(E6:E9)

  • Enter the Net Pay formula in E11 and press Enter:
=C11-E10

Read More: Payroll Exercises in Excel


Download Practice Workbook

Download the practice workbook here.


 

<< Go Back to Payroll | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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