How to Make Payroll in Excel (2 Suitable Examples)

If you work in HR, making payroll might be an integral part of your job. In Excel, it is easy to automate the payroll by creating a master payroll sheet. In case you need individual payroll slips, Excel can automate that as well.

In this article, you will learn how to make payroll in Excel. You will learn how to create a master sheet with payroll data for all the employees. Then, you will learn a method to create payroll slips for individual employees so that you can hand them over at the end of the month.

To demonstrate the methods of making a payroll in Excel, we have an example sheet with 10 employees. There are employee IDs, their names, their positions in the company, the hours they work regularly, and their overtime hours.

To prepare the payroll, we need to consider their hourly pay rate and their overtime rate. There are some other deductions as well that we will have to include in our calculations. Let’s create the payroll using this data:

➤ First, we need to calculate the gross pay. Insert this formula in the H2 cell:

➤ Autofill the column to calculate everyone’s gross pay.

➤ Do the income tax calculations next. For this example, we are assuming 15% income tax. It will vary depending on your local laws.
➤ Insert the following formula in the J2 cell:

➤ Autofill the rest of the cells to complete the column.

➤ To calculate the net pay, use the following formula:

In the previous method, we made a long sheet for everyone’s payroll. However, as an HR personnel, you might be assigned to create individual payroll slips as well. Let’s learn how to do that:

➤ First of all, create a format for the payroll slip in a new worksheet. Include their IDs, hours, payment, deductions, etc. We are using the following table-like structure for this:

➤ Go to the C3 cell (Employee Name).
➤ Head to the Data tab and select Data Tools > Data Validation > Data Validation.

➤ In this new window for Data Validation, select List from the “Allow:” section.
➤ Write this formula in the “Source:” section:

➤ Press OK to confirm.

➤ Now, we have to import the other data into the current worksheet. Use the following formulas:

➤ In the C3 cell, you can select any employee from the dropdown menu, and their payroll data will show up. You can print the current sheet as their payroll slip.

Frequently Asked Questions

What is the formula for calculating payroll in Excel?

There is no de facto formula for calculating payroll in Excel. However, you can use the SUM function to add up the salaries. Here is an example:
=SUM(A1:A10)
We are assuming that the salaries exist in the A1:A10 range. If you need to subtract something, you can put it in negative, and that will be subtracted automatically.

Is there any free payroll software?

Microsoft Excel can be used Online for free if you sign in to your Microsoft 365 account. Other than that, you can use dedicated payroll programs like Homebase, Payroll4Free, HR.my, etc.

How to calculate payroll?

In general, payroll can be calculated by multiplying the hours an employee has worked by their hourly pay rate. However, this may vary according to the business.

What is the formula for the payroll ratio?

Here is the formula for the payroll ratio in Excel:
=A1/B1
Here, A1 is the reference to the total payroll expenses, and B1 references the total revenue.

How to calculate the salary ratio?

You can use the following formula to calculate the salary ratio:
=A2/B2
Replace A2 with the salary of a person, and B2 with the salary of another person.

Wrapping Up

In this article, we have learned how to make payroll in Excel. We hope that you have become confident in making payroll by yourself after reading this tutorial. If you are in doubt, write your questions below, and we will try to cover them. Download the workbook used in this tutorial to have a readymade template in your hand to practice.