How To Do Payroll In Excel - Simple Steps

There are several options to carry out payroll but when you are having small-medium business and you need a powerful and cost-effective tool then you should use excel for it. Using excel formulae and functions, it is easy to carry out the payroll process.

The Payroll Process includes –

• Collecting employee data.
• Evaluating total hours worked.
• Computing pay and deductibles tax.

Here you calculate employee payroll as well as each penny you spend on them. For Payroll management, excel is one of the best tool as it is versatile and easy to use. Excel comes up with several functions that allow for simple calculations within seconds.

Why use Excel for your payroll process?

Here are few reasons which make to use Excel for payroll.

• 1. Collecting and Tracking of Employee Data - Excel provides ease and many options in storing and managing employee data. You can store data in alphabetical order, by particular dates and other options. It provides control over how you manage and manipulate data. It has the advantage of not making mistakes in payroll information management
• 2. Visual presentation - You can represent data visually in excel and this is one of the biggest pros of doing payroll in excel. When you are dealing with figures and calculations, you can represent it in charts, diagrams, or graphs.
• 3. Accuracy - It’s been a complex process to deal with payroll figures and when you are having higher number of transaction then it also becomes difficult to track and analyze it, but data manipulation is simple with this tool just because of functions and formulas available with excel. It’s not only easy to insert numbers into specific locations but also excel performs automatic calculations.

Steps to create Payroll in Excel –

Step 1 – Launch Excel blank spreadsheet.

Step 2 – In this newly created file, you will store employee payroll information then you need to create some field (column) that holds values.

Create column name in the following hierarchy –

• Column A – Employee Name.
• Column B - Hourly pay – contains employee’s pay rate per hour without any currency symbol.
• Column C – Total number of hours worked by an employee in a day.
• Column D - Overtime rate per hour without any currency symbol.
• Column E - Total Number of Overtime by an employee in a day.
• Column F – Gross pay – Payable amount without any deductibles.
• Column G – Income Tax – Tax payable on Gross Pay.
• Column H - Other Deductibles - Deductibles other than Income Tax such as EPF, PT, TDS, etc.
• Column I – Net Pay – Payment received in hand by an employee.

Step 3 – After deciding parameters relevant to your business, now it’s time to make entries. Now add the data according to the column with no formula.

Step 4 – Gross pay calculation – it is easy to calculate it in excel simply by using SUM and PROD function.

Step 5 – calculate Income Tax- in order to calculate it, first check how much percentage of tax your employee pays on the total gross pay. Income Tax is always calculated on Gross Pay.

Step 6 – Compute deductions - Here mention deductibles for a particular employee (if any) like premium of health/life insurance, professional taxes etc.

Step 7 – Now, finally it comes to Net Pay. It is the amount that comes after all the deductions from Gross Pay.

This is how we create Payroll in Excel.

If you are not so comfortable with the excel formulas and functions to make payroll management. You can learn to analyze payroll data in excel.