How to Do Payroll Reconciliation in Excel (with Easy Steps)

Are you running a small or medium business? Then, you must be concerned about payroll reconciliation. Payroll reconciliation is crucial since it can uncover mistakes or unfinished business that have to be addressed right away. In this article, we’ll demonstrate 3 easy and quick steps to do payroll reconciliation in Excel. So, go through the whole article to implement this in your work life too.

What Is Payroll Reconciliation?

Payroll reconciliation is comparing the amounts paid out during the current period to those in your payroll ledger in order to ensure that the numbers in the books match. This serves as the last check that the amounts you processed are accurate. Everything is documented in the ledger, including gross pay, taxes, withholdings, and net pay; these numbers should all be inspected and contrasted with the genuine payroll figures you’ve generated.

Why Payroll Reconciliation Is Necessary?

Payroll reconciliation may appear to be a massive undertaking with many moving components. But the good news is that you can (and should) split out the work to make it more manageable. The procedure will be started each pay period prior to the distribution of earnings, and additional reconciliation will be done at various points during the year.
Payroll reconciliation may not be the favorite work for your business, but it’s crucial to perform on a routine basis so that you can:

• Paying Employees Fairly: An astonishing number of employees claim they would start looking for a new job after just one or two wage issues.
• Avert Penalties and Fines: Payroll errors can result in fines from the Internal Revenue Service in addition to destroying morale.
• Keep Thorough Records: If you don’t reconcile payroll, your financial statement and records may be out-of-date and inaccurate. This results in significant inconveniences and problems throughout the tax season and raises the possibility of additional fines.

How to Do Payroll Reconciliation in Excel: 3 Simple Steps

Here, we’ll do payroll reconciliation of SOFTEKO. To do this, we’ll take the salary sheets for two consecutive months and then compare them. So, let’s explore the method step-by-step.
Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience.

Step 01: Open Salary Sheet of Previous Month

At the very beginning, bring up the Salary Sheet of the previous month. Here, we are assuming the present month is October. So, the previous month should be September. So, let’s see it in action.

📌 Steps:

• At first, open the salary sheet for the month of September.

This worksheet contains the Employee name, their corresponding ID, Basic salary, Allowance, Deductions and Net Payable amount for the month of September.

Step 02: Create Similar Salary Sheet for Ongoing Month

At this time, we have to create a similar type of sheet for the month of October. To do this, follow the steps below.

📌 Steps: 

• First of all, construct a basic outline just like the sheet of the previous month.
• Then, give all the basic salaries in Column D.

Now, we’ll show how to calculate different allowances. So, see the following.

• Here, select cell E7 and enter the following formula.

• After that, press ENTER. It’s the medical allowance (we assumed it as 15% of the basic salary).

• Now, bring the cursor to the right-bottom corner of cell E7 and it’ll look like a plus (+) sign. Actually, it’s the Fill Handle tool.
• Then, double-click on it.

As a result, the formula copied down to the lower cells in the column, and the following cells got results.

• Similarly, we calculated the House Rent as 35% of the Basic Salary.

• Also, we considered the Transport allowance as 10% of the basic salary.

• Later, we used the SUM function to calculate the Gross Total salary.
• Firstly, go to cell H7 and insert the formula below.

• Then, press ENTER.

Now, we should remove some part from the gross salary which is deductions. In this category, we took provident fund and income tax. We assumed PF as 20% of basic salary and Income Tax as 12%.

To calculate the Net Payable, we have to subtract the Deductions from the Gross Total.

• First, go to cell K7 and write down the following formula.

• As usual, press the ENTER key.

Step 03: Compare in New Sheet

Right now, we’ll compare the payroll for these two months in a single worksheet. It’s very simple and easy. Just follow along.

📌 Steps:

• Firstly, create a new worksheet with an outline like the following image.

To get the Net Payable amount for the month of September,

• Initially, select cell E7 and paste the following formula.

Here, the VLOOKUP function retrieves the Net Payable from the sheet Sep. The arguments of the function on the Sep worksheet are the following.

Besides, the IFERROR function replaces any error with a blank space. So, we don’t have to see any error signs on the worksheet.

• After that, press ENTER.

• Equivalently, get the Net Payable Oct also. We can extract these from the Oct worksheet.

Lastly, we opt to know the differences between these two accounts.

• So, go to cell G7 and put down the formula below.

• As always, tap ENTER.

Now, we can notice some incoherence in cells F7, G7, G9, G11, E16, and G16. Why are cells F7 and G7 blank? Did you get the point?

If you surf back to the sheet Oct, you can see that there is Net Payable amount visible in cell K7 though we are not getting it in the Reconciliation sheet.

Because the employee ID isn’t the same in these two worksheets, we wrote the erroneous ID in Oct sheet. As a result, the formula couldn’t get the result.
On the other hand, the basic salaries of Emma and Trump got changed in the two-salary sheet. Also, we didn’t get any Net Payable amount of September in cell E16 as this employee is a new who joined after September.

Read More: Payroll Exercises in Excel

Download Practice Workbook

You may download the following payroll reconciliation Excel workbook for better understanding and practice yourself.

Conclusion

This article explains how to do payroll reconciliation in Excel in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Have a nice day!

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