6 Easy Ways to Use Excel to do Bank Reconciliation

Today, you’ll learn how to use Excel to do bank reconciliation.

Here are the ways to do bank reconciliation:

1. Prepare the Bank Reconciliation Report

Preparation of the Bank Reconciliation Report is a straightforward process which can be facilitated by Excel.

Bank reconciliation template in Excel Format

The use of formulas ensures that accurate amounts are reflected in the report. For instance, the SUM function can be employed to verify or correct totals.

2. Identify Deposits in Transit

To identify deposits in transit, the SUMIF function in Excel proves useful for matching book deposits with corresponding entries in the bank statements.

Utilizing batch numbers for book deposits and bank statement credits expedites the reconciliation process.

After matching, any remaining unmatched deposits are considered deposits in transit.

3. Determine Outstanding Checks

Determining outstanding checks involves comparing checks recorded in the books with those in the bank statement.

The VLOOKUP function in Excel can be employed for this purpose.

Reconciling issued checks and identifying outstanding checks.

After matching, any checks that remain unmatched are identified as outstanding checks.

4. Recognize Bank Errors

Excel can be utilized to recognize bank errors, such as inaccuracies in the recording of cleared checks.

The VLOOKUP function is employed to retrieve values from the bank statement using check numbers, and any differences detected prompt further investigation to identify and rectify bank errors.

5. Detect Recording Errors in the Books

Similar to recognizing bank errors, Excel can be leveraged to detect recording errors in the books.

Using VLOOKUP, discrepancies in the recording of checks, whether overstated or understated, can be identified.

Additionally, the COUNTIF function can be utilized to detect instances of multiple accounting entries for a single check issuance.

COUNTIF find multiple accounting entries in Excel.

6. Record Unrecorded Transactions

Occasionally, an accountant or bookkeeper may overlook recording certain financial transactions, such as unrecorded deposits, interest income, bank charges, fund transfers, and unrecorded checks.

By employing a combination of VLOOKUP, COUNTIF, and SUMIF functions, these unrecorded transactions can be identified and subsequently recorded in the books.

How to make bank reconciliation?

Click here for more in-depth guide of how to make a bank reconciliation.

Jason John Wethe
Follow Me
Latest posts by Jason John Wethe (see all)
Scroll to Top