How to Reconcile Credit Card Statements in Excel - 4 Steps

The dataset below showcases bank and the accountant’s cash book statements.

There are discrepancies in these statements.

Step 1 – Prepare the dataset to perform Credit Card Reconciliation

Include the differences between the statements.

Step 2 – Find Mismatches Between Statements

Insert a helper column in the cash book statement.

Enter the following formula in F15.

=IF(COUNTIF($D$6:$D$11,D15),"","Mismatched")

Formula Breakdown:

COUNTIF($D$6:$D$11,D15) → This is the logical test. If the value in D15 is present in D6:D11, the condition is TRUE. Otherwise, FALSE.
Output: TRUE.
IF(COUNTIF($D$6:$D$11,D15),””,”Mismatched”) → If the condition is TRUE, the output will be a blank. Otherwise, “Mismatched”.
IF(TRUE,””,”Mismatched”)
Output: “”

Press ENTER to see the output.

Drag down the Fill Handle to see the result in the rest of the cells.

Step 3 – Record Mismatches in a Reconciliation Statement

Adjust mismatches in the reconciliation statement.
Here, the Electricity Bill and Salary Expenses are mismatched. The Electricity Bill is $20 less than the original payment. Due payment was $200, but $220 was paid. In the cash book balance, deduct $20.