Updated July 5, 2023
What is Payroll in Excel?
Companies use Payroll in Excel to perform pay-related calculations like in-hand salary, deductions, and taxes for their employees using Microsoft Excel.
Managing payroll manually can take up a lot of time and resources. To simplify the process, many companies rely on software like Tally or ADP or even outsource payroll to external service providers. However, some businesses prefer to manage payroll on their own because it gives them complete control over their employees’ pay and expenses.
So, companies that want to create payroll internally need a powerful, flexible, user-friendly, and easy-to-use tool. The perfect tool that fulfills all these requirements is Microsoft Excel. It has a vast range of formulas and a very simple layout, making it easy to use.
This article will show you the step-by-step process of how to create payroll in Excel using a very simple example.
Table of Contents
Example: How to Make Payroll in Excel? (Stepwise)
Let’s understand how to make Payroll in Excel with a few steps.
We have provided a downloadable payroll template in Excel for your better understanding.
Step #1: Open a New Excel Spreadsheet
The first step in making payroll in Excel is to open a new Excel sheet. For this:
- Go to the “Search Box” at the bottom-left end of the Windows desktop screen.
- Type “Excel”
- Click the Excel icon to open a new blank Excel spreadsheet.
Step #2: Save the File
Save the file in a preferred location so you can easily access the file whenever you need.
Step #3: Add Columns
Next, add some columns in the sheet to add data like employee name, pay/hour, total hours worked, etc., for the payroll calculation. Enter the column names in the following hierarchy:
| Sr. No. | Payroll Parameters | Add in Column | Details |
| 1 | Employee Name | A | Contains the employee names. |
| 2 | Pay/Hour | B | Total money an employee earns for one hour of work. |
| 3 | Total Hours Worked | C | Total hours the employee worked in a month or a particular pay period. |
| 4 | Overtime Pay/Hour | D | Pay an employee earns for working beyond the regular working hours. |
| 5 | Total Overtime Hours | E | Total hours the employee worked overtime in a month or a particular pay period. |
| 6 | Gross Pay | F | The amount you have to pay the employee (before deductions) |
| 7 | Income Tax | G | Tax payable on Gross pay. |
| 8 | Other Deductibles (If Any) |
H | Deductibles other than income tax like TDS, EPF, PT, etc. |
| 9 | Net Pay | I | In-hand salary of the employee. |
The above data in Excel will look like this:
Step #4: Enter Employee Data
After creating columns, enter the necessary details in the appropriate columns like the employee’s name in column A, the total no. of hours worked by an employee in column C, and so on.
Step #5: Calculate Gross Pay
Now, we will calculate the Gross pay in column F. We will use the following formula to calculate gross pay.
In Excel, we can write the above formula as follows:
=(B2*C2)+(D2*E2)
- Select “Cell F2” and enter the above formula.
- Press “Enter”. Excel will calculate the gross salary and display 3500 in Cell F2.
- Similarly, to calculate the gross pay for all employees, drag the cell downwards to apply the same formula to Cell F3 to Cell F6.
Step #6: Find Income Tax Amount
Gross pay is always the base for calculating Income Tax. To calculate the Income Tax, you must know the tax percentage your employee pays on their total gross pay. In this example, let’s assume only one tax rate of 15% for all employees.
The formula to calculate Income Tax in payroll is as follows:
So, the above formula in Excel will be,
=F2 * 0.15
- Select “Cell G2” and enter the above formula.
- Press “Enter”.
The formula will calculate and display the income tax as 525 in Cell G2.
- Drag the cell downwards from Cell G2 to Cell G6 to apply the formula for other employees.
Step #7: Subtract Deductions
Mention all the deductions related to your employees apart from income tax in column H. It includes all deductions like Health/Life insurance, EMI on loans, Employee Provident Fund, Professional tax, etc. You must also mention any other deductibles for your employees, and if there are no other deductibles, you can set the value to zero.
Step #8: Calculate Net Pay
Net pay is the final amount the company pays the employees after all deductions from the gross pay. It is also known as in-hand salary.
The formula to calculate Net Pay in payroll is as follows:
So, the formula in Excel will become,
=F2-(G2+H2)
- Select “Cell I2” and enter the above formula.
- Press “Enter”.
The formula calculates the net pay and displays 2775 in Cell I2, as shown below.
- Drag the cell downwards to apply the formula to the rest of the cells.
Step #9: Calculate the Sum of Payroll Parameters
Finally, let’s calculate the sum of all the important parameters of this payroll dataset.
Let us start by calculating the sum of the total hours that all employees worked.
- For this, enter the below formula in “Cell C7”.
=SUM(C2:C7
- Press “Enter”. The formula calculates the sum and displays 763 in Cell C8.
Repeat the above steps for total overtime hours, gross pay, income tax, other deductibles, and net pay.
The formula to calculate the sum of all the above parameters are as follows:
- Total Overtime Hours: =SUM(E2:E6)
- Gross Pay: =SUM(F2:F6)
- Income Tax: =SUM(G2:G6)
- Other Deductibles: =SUM(H2:H6)
- Net Pay: =SUM(I2:I6)
We have successfully created a basic Payroll layout in Excel. You can customize this template according to your requirements.
At the end of each month, HR has to provide every employee with a salary slip that includes their base pay, overtime pay, deductions, gross income, final pay, and more. Thus, after successfully generating the payroll, the HR department can simply use the calculated details to create a detailed salary slip or statement for each employee.
Things to Remember About Payroll in Excel
- Although there are several tools that you can use for creating and managing payroll, using Excel is more secure as you have direct control over every data.
- We have only provided a basic payroll layout. If required, you can add more columns, like health insurance premiums, life cover premiums, etc.
- We advise that you do not delete any information from the payroll table until the end of the fiscal year, even for employees who have left the company. Deleting any data may cause errors when creating financial reports.
Frequently Asked Questions (FAQs)
Q1. What are the four types of payroll?
Answer: The most common types of payroll cycles are weekly, every two weeks (bi-weekly), twice a month (semi-monthly), and monthly. Different organizations use different payroll cycles, and also it varies between countries.
Q2. What are the basic steps in processing payroll?
Answer: There are four basic steps in calculating payroll, which are as follows:
- Create a Payroll table in Excel with all required columns
- Add employee information
- Calculate gross pay, income tax, and total deductibles
- Determine the net pay for all employees
- Finally, create a salary slip for each employee.
Q3. What is popular payroll software?
Answer: Payroll software is a computer application that automatically calculates and manages employee salaries. There are various paid software, like Gusto, ADP, QuickBooks, Justworks, etc. There are also free options, like OnPay, Zoho Payroll, and HR.my.
Recommended Articles
This EDUCBA article acts as a step-by-step guide on how to calculate Payroll in Excel from scratch. Here, we show what formulas and parameters to use. We have also added a downloadable Excel template for your use. You can also go through our other suggested articles:-
