How to Do Three Way Reconciliation in Excel (Step-By-Step)

In this article, you’ll learn how to do three way reconciliation in Excel with 5 simple steps. You’ll also learn what is three way reconciliation before learning the steps in this article.
The three way reconciliation in Excel is useful to compare transaction records from 3 different sources and match them to find any error in calculation.
Below you’ll see an overview of doing three way reconciliation in Excel and finding discrepancies among the transaction values.

Download Practice Workbook

You can download the Excel file from here for practice.

What is Three Way Reconciliation in Excel?

Three way reconciliation in Excel refers to a specific financial or accounting process that involves comparing and matching data from three different sources to ensure accuracy and consistency. The three sources generally are:

• Books of the Company: This represents the company’s internal financial records, which include transactions, invoices, receipts, and other financial data.
• Bank Statements: These are statements provided by the bank, reflecting the company’s bank account transactions for a specific period.
• General Ledger: The general ledger is a record of all financial transactions of a company, organized by accounts.

A Step-by-Step Guide to Do Three Way Reconciliation in Excel

Here, you’ll get a step-by-step guide to do three way reconciliation in Excel with 5 simple steps.

Step 1. Inserting Bank Statement and Transactions Record in Dataset

• We’ll insert our bank statement for the month of July in a sheet titled “Inserting Bank Statement”.
• Here, we’ll insert dates in column B, transaction descriptions in column C and transaction amounts in column D respectively.

• Then, we insert our transactions record for July in a sheet titled “Inserting Transactions Record”.
• Here, we’ll insert dates in column B, transaction descriptions in column C and transaction amounts in column D similarly as we inserted in the “Inserting Bank Statement” sheet.

Step 2. Calculating Book and Bank Balance Using SUM Function

• Put the following formula in cell D16 of sheet “Calculating Bank Balance” using the SUM function to calculate the total bank balance-

• Similarly, put the following formula in cell D16 of sheet “Calculating Book Balance” using the SUM function to calculate the total book balance-

Step 3. Comparing Book Balance and Bank Balance to Check Discrepancies

• Now, bring the book balance value from the sheet “Calculating Book Balance” and the bank balance value from the sheet “Calculating Bank Balance” and then put them in cells D16 and D17 of the sheet “Comparing Book & Bank Balance”-

• Put the following formula in cell D18 to compare the book and bank balance-

• We get a value of $350. That means there are discrepancies in the transaction amounts that amount to $350 and we have to find it out.

Step 4. Identifying Outstanding Transactions Using IF Function

• Create a column titled Reconciled Amount in column E and put the following formula in cell E6 then apply the Fill Handle tool to get the outstanding transactions amounts-

• Here, we compare the transaction amounts from both the bank statement and the transactions statement and extract those values that don’t match with the help of the IF function.

Step 5. Identifying Discrepancies Using IF and SUM Functions

• Insert another column titled Discrepancies in column F and write the formula in cell F6 then apply the Fill Handle tool to get the discrepancies amount from both statements-

• Put the following formula in cell F18 to sum the discrepancies values-

• So, now we get the total discrepancies amount which matches with the difference between the bank and book balance.
• We can also identify the transaction’s names where the discrepancies lie and can correct them quickly.

Things to Remember

• Be careful when inserting bank statements and transactions record in your Excel file.
• Put the sheets’ names carefully in formulas when bringing them in the desired sheet from the source sheet. Otherwise, you’ll get an error.
• After finding the discrepancies in the transaction details correct them to perform three way reconciliation.

Frequently Asked Questions

1. What are the 5 types of reconciliation?
Bank reconciliation, vendor reconciliation, business-specific reconciliation, intercompany reconciliation, and customer reconciliation are the five main categories of account reconciliation. And they all assist you in maintaining your balances.

2. What is the formula for reconciliation in Excel?
There are three Excel functions for reconciliation, they are VLOOKUP, SUMIF, and COUNTIF functions.
The VLOOKUP function can locate values in a database and provide the value based on the number of columns in that database. You can compare the amount entered in two different financial registers using this VLOOKUP function.
The sum of the found values in a range is then added by the SUMIF function. This function is helpful for adding just values that correspond to the two different financial registers that are provided.
The number of times values are present in each given range is also added by the COUNTIF function. This function is used to catch duplicate values in the given range.

3. How do you automate reconciliation in Excel?
You can automate reconciliation in Excel by using VBA Macros. To know about this topic, visit the article provided below: Automation of Bank Reconciliation with Excel Macros.

Conclusion

In conclusion, conducting a three way reconciliation in Excel is a valuable process for comparing transaction records from three distinct sources and identifying potential errors. You have learned a step-by-step process to perform three way reconciliation in Excel in this article. Please let us know in the comment section if there is any query or suggestions related to this topic.