4 Ways to Find Discrepancy in Bank Reconciliation

How to find Discrepancy in bank reconciliation

Want to learn how to find a discrepancy in bank reconciliation? Read on.

You can efficiently discover discrepancies in bank reconciliations through various methods.

One effective approach involves utilizing Microsoft Excel, a widely accessible tool.

Leveraging Excel simplifies the process, allowing users to swiftly identify and rectify discrepancies.

It’s worth noting that these methods are not only accessible but also significantly accelerate the bank reconciliation task by swiftly pinpointing discrepancies.

To further expedite the process, our tool offers an additional layer of efficiency, ensuring a seamless and rapid identification of discrepancies in your bank reconciliation.

This combination of Excel and our tool not only enhances accuracy but also saves valuable time, enabling a more efficient financial management workflow.

Use Excel VLOOKUP

To identify discrepancies between our cash book and bank statement, we’ll start with Excel’s VLOOKUP tool.

This tool allows us to easily find values in one table by searching for a specific identifier, in this case, the check number.

Let’s take an example: Check number 100001 shows a recorded amount of 10,000 in our cash book.

We’ll use VLOOKUP to locate the corresponding entry in the bank statement.

If, for instance, the bank statement records check number 100001 with an amount of 10,100, we subtract these values to pinpoint the discrepancy.

You can apply this process broadly to compare records between our company and the bank statement.

By using VLOOKUP, you can efficiently identify any inconsistencies in both sets of data.

Utilize the VLOOKUP function also to identify discrepancies in both deposits and issued checks.

Consider employing batch numbers to streamline the process.

Explore our website for a tutorial on leveraging batch numbers for deposit reconciliation.

Employ PIVOT table

To identify discrepancies in bank reconciliations, consider utilizing a pivot table. Streamline the process by categorizing positive amounts in one table and negative amounts in another.

two tables for bank reconciliation

Apply a pivot table to both sets of data for a quicker and more efficient reconciliation.

PIVOT table finding discrepancy

This method enhances accuracy and expedites the reconciliation process significantly.

Examine check number 100001, and you’ll notice a (100) discrepancy.

This indicates that the entry for check 100001 is 100 value lower than the corresponding bank record.

It’s essential to conduct a thorough review to ascertain whether this deviation is the result of a recording error.

Find discrepancy with COUNTIF

Another common cause of discrepancies arises when the same bank transaction is recorded multiple times, either in the depositor’s cash book or in the bank statement.

For example, if the bank mistakenly records check 100002 twice, leading to its appearance twice in the bank statement, this duplication can be identified using the COUNTIF function, ultimately revealing the discrepancy.

Try our tool

Explore our user-friendly tool, the free reconciler, located above.

It efficiently reconciles thousands of rows effortlessly, similar to Excel’s VLOOKUP function. The process is straightforward – no need for complex formulas or functions.

For seamless usage, ensure your data is arranged according to the instructions outlined in the video tutorial accompanying the tool.

Enjoy the benefits hassle-free!


Our tool goes beyond reconciling records from 1 to 2; it also seamlessly reconciles from records 2 to 1.

This dual reconciliation feature simplifies the process, aiding in the identification of any unrecorded transactions.

It ensures a comprehensive analysis for a more thorough understanding of your data.

Free Accounting Training

How I prepare & generate financial Statements in under an hour.

Email Address *

Scroll to Top