How to Reconcile Credit Card Statements in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


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.


How to Reconcile Credit Card Statements in Excel: 4 Suitable Steps

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.

Read More: How to Do Bank Reconciliation in Excel


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.

    Read More: How to Reconcile Vendor Statements in Excel


    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.

    Read More: Automation of Bank Reconciliation with Excel Macros


    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

    Read More: How to Reconcile Data 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

    Download Practice Workbook

    Download this workbook and practice while going through the article.


    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.


    Related Articles


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

    What is ExcelDemy?

    ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
    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

     

     

    ExcelDemy
    Logo