How to Do Three Way Reconciliation in Excel: 5 Methods

Method 1 – Inserting Bank Statement and Transactions Record in Dataset

• Insert the bank statement for the month of July in a sheet titled “Inserting Bank Statement.”
• Insert dates in column B, transaction descriptions in column C, and transaction amounts in column D.

• Insert our transactions record for July in a sheet titled “Inserting Transactions Record.”
• 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.

Method 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-

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

Method 3 – Comparing Book Balance and Bank Balance to Check Discrepancies

• Bring the book balance value from the sheet “Calculating Book Balance” and the bank balance value from the sheet “Calculating Bank Balance” and 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.

Method 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 transaction amounts-

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

Method 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-

• Get the total discrepancies amount that matches the bank and book balance difference.
• Identify the transaction’s names where the discrepancies lie and can correct them quickly.

Things to Remember

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

Download Practice Workbook

You can download the Excel file from here for practice.

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 below: Automation of Bank Reconciliation with Excel Macros.