Mohammad Shah Miran
Excel provides a wide range of applications to its users. It is frequently used for data entry, money management, keeping track of things, and budgeting. Consider a situation where a new company wants to prepare its employee payroll. How will it accomplish its task? Well, to aid his problem, today we will discuss how to make payroll in Excel.
Download Practice Workbook
You can download and practice the dataset that we have used to prepare this article.
What Is Payroll in Excel?
If we want to make the definition short, payroll is the process of disbursing wages to an organization’s employees. It involves compiling employee lists, recording their salaries and payments, tracking working hours, calculating associated taxes and other expenses, figuring out their benefits and pay, etc. Excel allows us to calculate payroll and periodically check it.
4 Steps to Make Payroll in Excel
Let’s assume we have a dataset, namely “United State Steel Corporation”. You can use any dataset suitable for you.
Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.
Step 1: Prepare the Dataset
The very first step to calculate a company’s payroll is to prepare the dataset with the additional information. Look at the image we have attached below.
- Here, we want to calculate the payroll for “Michael Scott” for his one week of work.
- To do so, we have to know the values of working hours, hourly pay, commissions, and the benefits received from the company. We will use those values according to our given dataset.
Read More: How to Create Payroll Calculator in Excel (with Easy Steps)
Step 2: Calculate Gross Pay
To calculate the gross pay, we will take the necessary information from the dataset given previously.
- In this case, the employee has worked for the company for one month and will now be paid for every week.
- So firstly, enter the following formula in cell C10.
=C7*C6+C9Here, C6, C7, and C9 contain the values of Hourly Pay, Working hours/week, and Commissions respectively.
- To see the output, press the Enter button.
Read More: How to Calculate Hours and Minutes for Payroll Excel (7 Easy Ways)
Step 3: Determine Employer Contributions
As every company offers some benefits to its staff members, such as FICA taxes, retirement, insurance amounts, etc. So, we will sum up those values in terms of “employer contributions”.
- To begin with, write the following formula in cell C15. Here, we use SUM function to determine employer contributions.
=SUM(C12:C14)- Then press the Enter button to see the output result.
Read More: How to Generate Payroll in Excel VBA (with Easy Steps)
Step 4: Calculate the Total Accrued Payroll
This is the final step of our procedure. We have calculated gross pay and employer contributions so far. Now it is time to calculate the total accrued payroll.
- Put the following formula in the cell.
=SUM(C10+C15+C17)As you have already understood, we use the SUM function to calculate the total accumulated salary. Here, C10, C15, and C17 cells refer to Gross Pay, Employer Contributions, PTO respectively.
- Now press Enter to see the output.
Read More: How to Make a Payroll Accrual Calculator in Excel
How to Make a Payroll System with Payslip in Microsoft Excel
An employee receives a payslip, a piece of paper detailing his earnings and the amount of tax deducted, at the conclusion of each week or month. As our title suggests, we will now discuss with you how to make a payroll system with payslip.
- Firstly, Enter the given formula in E5.
=C5*D5Here we multiply Pay/Hour to Working Hours/ Week to calculate the Gross Pay.
- Press the Enter button as we have done below and see the output.
- To get the other value, drag the Fill Handle tool from E5 to E10.
- Now to calculate the income tax, we take 15% of Gross Pay that one individual has earned in the given time frame.
- Enter the given formula in the cell.
=E5*0.15Here, E5 refers to Gross Pay.
- Subsequently, press the Enter button
- Drag the Fill Handle tool to get the other value.
- Now to calculate the Net Pay, subtract Net Tax from Gross Pay. We can use the following formula in G5 to accomplish the task.
=E5-F5- Press the Enter button afterward.
- Lower the cursor to the C10 cell to use the AutoFill tool.
- Now it’s time to make a payslip in accordance with our payroll. Here, we will make a payslip for one of the employee named “Dwight Schrute”. To do so, enter the following formula in the E8 cell.
=Sum(E4:E7) 
- Now calculate the Net Pay by entering the following formula in the C8 cell.
=C7-E8
Things to Remember
- It is to be mentioned that if an employee does extra work in his workplace, don’t forget to do the necessary calculations on your Excel sheet. Those values will be added to Gross Pay.
Practice Section
We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it yourself.
Conclusion
We hope you grasped the process of how to make payroll in Excel. Further, if you have any queries, feel free to comment below, and we will get back to you soon.
