Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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 has 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.


Download Practice Workbook

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


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.

3 Steps to Do Payroll Reconciliation in Excel

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.

how to do payroll reconciliation in excel

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

Read More: How to Make Payroll in Excel (with Easy Steps)


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.

Create Similar Salary Sheet for Ongoing Month

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

  • Here, select cell E7 and enter the following formula.
=D7*15%
  • After that, press ENTER. It’s the medical allowance (we assumed it as 15% of the basic salary).

Calculating Medical Allowance of Payroll

  • 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.

using fill handle to do payroll reconciliation

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.

House Rent Allowance

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

Transport

  • Later, we used the SUM function to calculate the Gross Total salary.
  • Firstly, go to cell H7 and insert the formula below.
=SUM(D7:G7)
  • Then, press ENTER.

Calculating Gross Total in Payroll sheet

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.

  • At first, go to cell K7 and write down the following formula.
=H7-I7-J7
  • As usual, press the ENTER key.

Create Similar Salary Sheet for Ongoing Month to do payroll reconciliation

Read More: How to Create Payroll Calculator in Excel (with Easy Steps)


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.

Compare in New Sheet

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

  • Initially, select cell E7 and paste the following formula.
=IFERROR(VLOOKUP(C7,Sep!$C$7:$K$15,9,FALSE),"")

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

Arguments of VLOOKUP function to do payroll reconciliation

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.

Extracting Net Payable amount of october

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

  • So, go to cell G7 and put down the formula below.
=IFERROR(E7-F7,“”)
  • 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?

Payroll reconciliation in Excel

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.

comparing in reconciliation sheet in Excel

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: How to Make a Payroll System in Microsoft Excel with Payslip


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. Please visit our website, Exceldemy, a one-stop Excel solution provider, to explore more.


Related Articles

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo