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
- 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: “”
- 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.
The mismatch for Salary Expense is $100. Here, add it to your cash book balance.
Step 4 – Calculate the Adjusted Balance
- Go to E14 and enter the following formula
=E9+C11-C13
This is the output.
Read More: How to Reconcile Data in Excel
- Your cash book balance should match the bank statement
Download Practice Workbook
Download the workbook and practice.
Credit Card Reconciliation.xlsx
Related Articles
- How to Do Intercompany Reconciliation in Excel
- How to Do Reconciliation in Excel
- How to Reconcile Two Sets of Data in Excel
- How to Reconcile Data in 2 Excel Sheets
- How to Perform Bank Reconciliation Using VLOOKUP in Excel
