How to Reconcile Credit Card Statements in Excel

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. It is possible to reconcile credit card statements in Excel. In this article, I will explain how to reconcile credit card statements in Excel.


Download Practice Workbook

Download this workbook and practice while going through the article.


Introduction to Credit Card Reconciliation

Accountants employ a process called credit card reconciliation to ensure that transactions on a credit card statement and those on the business’s general ledger match up. To maintain effective and accurate bookkeeping, businesses need to ensure that the statements reconcile.
The simplest way for accountants to do this is to compare the general ledger with company credit card statements. The ledger is correct if each payment in the book matches a payment in the statement.


4 Suitable Steps to Reconcile Credit Card Statements in Excel

This is the dataset for today’s article. We have statements from both the bank and the cash book from the company. However, there are some discrepancies in these statements. So we need to reconcile them. I will show step by step how to do that.

how to reconcile credit card statements in excel


Step 1: Prepare Format for Credit Card Reconciliation

Our first step is to prepare a format for credit card reconciliation. The format includes all the differences between the statements from the bank and the cash book. We have to add or subtract amounts from the unadjusted balance. The format will look like this.


Step 2: Find Mismatches Between Statements

The next step is to find out the mismatches that are present in the statements. To do so, we will use the COUNTIF and IF functions.

  • First of all, create a helper column in the cash book statement.

how to reconcile credit card statements in excel

  • Then, write down 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 the range D6:D11, the condition will be TRUE, else FALSE.
  • Output: TRUE.
  • IF(COUNTIF($D$6:$D$11,D15),””,”Mismatched”) This is the formula. If the condition is TRUE, output will be a blank, else, “Mismatched”.
         IF(TRUE,””,”Mismatched”)
  • Output: “”
  • Now, press ENTER. Excel will return the output.
  • how to reconcile credit card statements in excel

    • Now, use the Fill Handle to AutoFill up to F20.


    Step 3: Record Mismatches in Reconciliation Statement

    Now, you need to adjust the mismatches in the reconciliation statement. While doing so, you must be careful about which amount to add and which one to subtract.
    In our case, the Electricity Bill and Salary Expenses are mismatched. The Electricity Bill is $20 lesser than the original payment. That means, the due was $200, but mistakenly the payment was $220. So from the cash book balance, we have to deduct $20.

    how to reconcile credit card statements in excel

    Similarly, the mismatch for Salary Expense is $100. But this time you have to add it to your cash book balance.


    Step 4: Calculate Adjusted Balance

    Now, you have to calculate the adjusted balance. To do so,

    • Go to E14 and write down the following formula
    =E9+C11-C13

    how to reconcile credit card statements in excel

    • Now, press ENTER. Excel will calculate the adjusted balance.

    how to reconcile credit card statements in excel


    Things to Remember

    • Be careful about when to add and when to subtract an amount.
    • Your cash book balance should match with the bank statement

    Conclusion

    In this article, I have illustrated how to reconcile credit card statements in Excel. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Please visit Exceldemy for more useful articles like this.

    Akib

    Akib

    Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

    We will be happy to hear your thoughts

    Leave a reply

    ExcelDemy
    Logo