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. To reconcile them:

how to reconcile credit card statements in excel

Step 1 – Prepare the dataset to perform Credit Card Reconciliation

Include all the differences between the statements.

Read More: How to Do Bank Reconciliation in Excel

Step 2 – Find Mismatches Between Statements

Use the COUNTIF and IF functions.

  • Insert a helper column in the cash book statement.

how to reconcile credit card statements in excel

  • Enter the following formula in F15.

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”.
  • Output: “”


  • Press ENTER to see the output.

how to reconcile credit card statements in excel

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

Read More: How to Reconcile Vendor Statements in Excel

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.

how to reconcile credit card statements in excel

The mismatch for Salary Expense is $100. Here, add it to your cash book balance.

Read More: Automation of Bank Reconciliation with Excel Macros

Step 4 – Calculate the Adjusted Balance

  • Go to E14 and enter the following formula

how to reconcile credit card statements in excel

  • Press ENTER.

This is the output.

how to reconcile credit card statements in excel

Read More: How to Reconcile Data in Excel

Things to Remember

  • Your cash book balance should match the bank statement

Download Practice Workbook

Download the workbook and practice.

Related Articles

<< Go Back to Excel Reconciliation Formula | Excel for Accounting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Akib Bin Rashid
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF