Credit Card Reconciliation in Excel (With Template)

TL;DR: Credit card reconciliation proves that every charge, payment, fee and credit on the statement is in your records, and nothing in your records is missing from the statement. In Excel: convert the statement to rows, prove it balances on its own, match each line to your ledger or receipts with a key, investigate the unmatched items, and confirm the adjusted balances agree.

Credit cards are where small business books most often go quietly wrong. Charges are frequent and small, several people may hold cards on one account, receipts go missing, and refunds show up weeks after the purchase. Reconciling monthly keeps all of that under control. Leaving it for a year turns it into a project.

This guide sets out a reconciliation workbook we use for cards, explains the formulas, and covers the card-specific quirks that trip people up.

What you're reconciling

Two sets of records:

1. The statement: what the card issuer says happened in the cycle. Previous balance, payments, credits, charges, fees, interest, new balance.
2. Your records: the card account in your accounting software, an expense spreadsheet, or a pile of receipts.

The goal is to show that they agree, or to explain exactly why they don't (usually timing).

A useful distinction: reconciling a card against your ledger proves your books are right. Reconciling it against receipts proves each charge is documented and legitimate. Businesses often need both. The workbook below handles either.

Step 1: get the statement into rows

Convert the PDF to a table with one row per transaction. Our credit card statement converter outputs Date, Description, Amount, Type (charge, payment, credit, fee, interest) and, for multi-card accounts, Cardholder. If you download activity from the issuer's website instead, make sure the date range matches the statement cycle exactly and remove pending items.

Put the data on a sheet called Statement as an Excel Table named Stmt.

Step 2: prove the statement balances

Before matching anything, make sure the statement data is complete:

Previous balance + charges + fees + interest − payments − credits = new balance

With a signed Amount column (charges, fees and interest positive; payments and credits negative):

=PrevBalance + SUM(Stmt[Amount])

Compare with the new balance on the statement. If they differ, the conversion is missing something; fix it before going further. Our balance checker runs this test if you prefer not to set it up.

If the issuer prints subtotals per cardholder or per section, check those too. They narrow down where a missing line is.

Step 3: get your records into rows

On a sheet called Ledger, put your side as a table named Ldg: Date, Payee or Description, Amount (same sign convention as the statement), and Reference if you have one. Export it from your accounting software's register for the card account, covering the same cycle dates, or build it from your expense log.

Step 4: build a matching key

Matching on amount alone fails as soon as two charges share an amount. Matching on date and amount fails when the posting date differs from the transaction date. We use a two-stage approach.

Stage 1: exact key (date + amount).

In both tables:

=TEXT([@Date],"yyyy-mm-dd")&"|"&TEXT([@Amount],"0.00")

Stage 2: amount within a date window. For items that don't match exactly, look for the same amount within a few days.

Step 5: match statement lines to the ledger

In Stmt, add a Match column:

=IF(COUNTIF(Ldg[Key],[@Key])>0,"Matched","")

And a fuzzy match for the rest:

=IF([@Match]<>"","",
 IF(COUNTIFS(Ldg[Amount],[@Amount],Ldg[Date],">="&[@Date]-3,Ldg[Date],"<="&[@Date]+3)>0,"Near",""))

Do the same in Ldg, looking up the statement.

One caveat: COUNTIF-based matching doesn't handle duplicates perfectly. If there are two 12.50 charges on the statement and one in the ledger, both statement rows will say "Matched." To handle that, add an occurrence number to the key:

=[@Key]&"#"&COUNTIF(INDEX([Key],1):[@Key],[@Key])

Now the first 12.50 on a date is ...|12.50#1, the second ...|12.50#2, and they match one-to-one.

Step 6: investigate unmatched items

Filter both tables for blank Match. Each unmatched item falls into one of these buckets:

Unmatched on	Likely cause	Action
Statement only	Charge not recorded	Enter it in the books (with receipt)
Statement only	Fee or interest not recorded	Enter it
Statement only	Refund not recorded	Enter it
Ledger only	Charge recorded but posts next cycle	Timing difference; note it
Ledger only	Duplicate entry	Delete the duplicate
Ledger only	Charge recorded on wrong card	Move it
Both, different amounts	Tip added, currency conversion, typo	Correct the ledger

"Near" matches are usually date differences (transaction date vs. posting date). Check them quickly and accept them if they're the same transaction.

Step 7: the reconciliation summary

On a Summary sheet:

	Amount
Statement new balance	(from statement)
+ charges in ledger not yet on statement	=SUMIFS(Ldg[Amount],Ldg[Match],"",Ldg[Reason],"Timing")
− credits in ledger not yet on statement	(if any)
= Adjusted statement balance
Ledger balance at cycle end	(from your books)
+ items on statement not yet recorded	(after you enter them, this should be zero)
= Adjusted ledger balance
Difference	should be 0.00

When the difference is zero, you're reconciled. Save the workbook with the cycle in the file name. If you're working in accounting software, you'll typically do the final tick-off in the software's own reconcile screen; the Excel workbook is still useful for investigating differences or for businesses that don't use software.

Card-specific quirks

Transaction date vs. posting date

Card statements usually show both, or only the posting date. Your records probably use the transaction date (when you bought something). Charges near the cycle end may be in your ledger in one month and on the statement the next. That's a timing difference, not an error.

Payments

Your card payment comes from a bank account. In your books, it should be a transfer from the bank to the card, not an expense. On the statement, it's a credit. Match it to the transfer in your ledger. If you see the payment recorded as an expense, fix it; otherwise expenses are overstated.

Refunds and returns

A refund appears as a credit, sometimes weeks after the original purchase. Record it against the same expense category as the original charge, so the net is right.

Interest and fees

Easy to forget because nobody has a receipt for them. They're on the statement; make sure they're in the books.

Foreign transactions

The billed amount in your home currency may differ from what you recorded if you used the receipt's foreign amount. Record the billed amount, and treat any foreign transaction fee as a separate bank charge.

Restaurant tips and hotel holds

The pending amount (before the tip, or the hotel's hold) differs from the final posted amount. Always use the posted amount from the statement.

Multiple cardholders

Add a Cardholder column and reconcile per person if your ledger tracks that. It makes chasing missing receipts much easier.

Reconciling against receipts

If the goal is documentation rather than ledger accuracy, swap the Ledger sheet for a Receipts sheet: one row per receipt, with Date, Merchant, Amount and the file name or location of the receipt. Run the same matching. Unmatched statement lines are missing receipts; unmatched receipts are either personal purchases paid elsewhere, charges that post next month, or receipts for the wrong card.

A Receipt status column (Y/N) on the Statement sheet becomes your monthly chase list.

Reconciling several cards on one account

Business card programs often have one statement with several cardholders, or several separate card accounts with one issuer. Two ways to structure the workbook:

One workbook per statement, with a Cardholder column in both the Statement and Ledger tables. Matching works the same; you just add Cardholder to the key if your ledger records it. The summary reconciles the whole statement, and a pivot by Cardholder shows each person's unmatched items. This is the simplest option when the issuer bills everything on one statement.

One workbook per card account when each card has its own statement and its own account in your books. Keep them in the same folder with consistent names, and make a small index sheet listing each card, its statement new balance, its ledger balance and its difference. When every row on the index shows zero, the month is done.

Either way, send each cardholder their own unmatched list rather than the whole thing. People respond faster to a short list of their own charges than to a spreadsheet of everyone's.

What to keep for the file

For each reconciled cycle, keep:

• The statement PDF.
• The workbook with the Statement, Ledger and Summary sheets as they were at reconciliation.
• Notes on any adjustments you made and why (missing charge entered, duplicate removed, timing item carried forward).

Carry the timing items forward deliberately. Next month's first task is to confirm that last month's outstanding items appeared on the new statement. If a charge you expected still hasn't posted after a cycle or two, find out why; it may have been reversed, disputed or recorded on the wrong card.

Spotting problems beyond bookkeeping

Reconciliation is mainly about accuracy, but it's also a good moment to glance for things that shouldn't be there: a subscription nobody uses, an unfamiliar merchant, a charge in a city where nobody travelled, or the same vendor billing twice. Sorting the statement by merchant and by amount takes a minute and occasionally saves real money. If something looks unauthorized, contact the issuer promptly; card issuers have time limits for disputes, and they're printed in your cardholder agreement.

Turning it into a template

Once built, save the workbook as a template:

1. Clear the data in Stmt and Ldg but keep the formulas and table structure.
2. Keep the Summary sheet with the formulas, leaving input cells for previous balance, new balance and ledger balance.
3. Add conditional formatting: unmatched rows in amber, the difference cell green at zero and red otherwise.
4. Save as Card-Reconciliation-Template.xltx.

Each month: paste in the new statement and ledger data, type three numbers, and review the amber rows. Our bank reconciliation template guide uses the same structure for bank accounts, so you can keep both side by side.

A worked example

An invented business card for one cycle:

• Previous balance: 3,210.44
• Payment: −3,210.44
• Charges: 2,875.19 across 41 lines
• Credit (refund): −64.00
• Interest: 0.00
• New balance: 2,811.19

The statement data balances: 3,210.44 − 3,210.44 + 2,875.19 − 64.00 = 2,811.19.

Matching against the ledger:

• 38 charges match exactly.
• 2 charges are "Near" matches, recorded on the transaction date two days before the posting date. Accepted.
• 1 charge (a 19.99 software subscription) is on the statement but not in the ledger. The owner forgot to record it. Entered.
• The refund of 64.00 isn't in the ledger. Entered against the original category.
• The payment matches a transfer from checking.
• 1 ledger entry (a 142.80 supplier charge on the last day of the cycle) isn't on the statement; it posts next cycle. Timing difference, noted.

Summary: statement new balance 2,811.19 + 142.80 timing = 2,953.99 adjusted. Ledger balance after entering the missing items is 2,953.99. Difference: zero.

How long it should take

For a single card with a few dozen charges a month, once the template is set up, the whole reconciliation should take roughly fifteen to thirty minutes: a few minutes to convert and paste, a few to review the unmatched list, and the rest to enter what's missing. If it regularly takes much longer, look upstream. Usually charges aren't being recorded as they happen, or receipts aren't being collected, and the reconciliation is doing the job of daily bookkeeping. Fixing the habit is cheaper than a faster spreadsheet.

Common mistakes

Recording the card payment as an expense. It's a transfer.

Ignoring small differences. A 3.00 difference might be one forgotten fee, or two offsetting errors. Find it.

Reconciling to activity downloads with the wrong date range. Use the statement cycle dates.

Matching by amount only. Duplicate amounts are common on cards. Use a key with dates and occurrence numbers.

Skipping months. Each month builds on the last. If one is wrong, every month after is harder.

Our take

Card reconciliation is mostly mechanical once the statement is in rows and the matching formulas are set up. The judgment is in the unmatched list, and that list should be short. If it isn't, the problem is usually upstream: charges not being recorded promptly, or card payments being treated as expenses. Fix the process and the reconciliation becomes a ten-minute monthly job.

Troubleshooting card reconciliations

The difference equals a payment. A payment from the bank was recorded on the bank side but not the card side, or posted on the card after the statement closed. Check the payment's posting date on the card statement.

Pending charges. Charges pending at statement close aren't on the statement. If you recorded them from receipts, they're outstanding on your reconciliation until they post.

Charges posted for a different amount. Restaurant tips, hotel holds and fuel pre-authorizations often post for a different amount from the receipt. Correct your books to the posted amount.

Refunds on the next statement. A return made near the statement date may post next month. Note it as outstanding.

Foreign currency. The card statement amount in your currency is what counts; use it rather than the receipt's foreign amount.

A mini checklist each month

1. Statement previous balance equals last month's reconciled balance.
2. Every charge, credit, fee and interest line matched or explained.
3. Payments matched to the bank account's payment lines.
4. Pending and late-posting items listed as outstanding.
5. Difference exactly zero.

A second worked example: three cards, one company

A small company has three employee cards on one account. The bookkeeper adds a Cardholder column to the converted statement, matches each charge to the receipts in the expense app, and lists missing receipts by cardholder. Two employees have one missing receipt each; one has nine. The reconciliation balances, but the missing receipts list goes to the owner. Reconciliation isn't only about the total; it shows where the process is weak.

FAQ

How do I reconcile a credit card statement in Excel?

Convert the statement to rows, confirm it balances on its own, match each line to your ledger or receipts with a date-and-amount key, investigate unmatched items, and confirm the adjusted statement and ledger balances agree.

Why doesn't my credit card reconcile?

Common causes are unrecorded fees, interest or refunds, charges that post in the next cycle, payments recorded as expenses instead of transfers, duplicates, and differences between pending and posted amounts.

Should I use the transaction date or the posting date?

The statement uses posting dates. Your books may use transaction dates. Expect some items near the cycle end to fall into different months, and treat them as timing differences.

How should card payments appear in my books?

As a transfer from your bank account to the credit card account. Recording them as expenses double counts spending.

Can I use the same template for receipts?

Yes. Replace the ledger sheet with a receipts list and run the same matching. Unmatched statement lines show which charges still need receipts.

How often should I reconcile credit cards?

Monthly, when each statement arrives. It keeps the unmatched list short and makes missing receipts easier to track down while memories are fresh.