To reconcile credit card statements in Excel, list the statement charges beside your ledger entries and receipts, mark each charge matched or unmatched, and confirm the statement total agrees with the ledger once timing items are listed. Investigate every unmatched charge, and escalate any that stay unsupported after the follow-up date.
Setting up the credit card statement and ledger side by side
Set up the sheet with the statement on the left, the ledger entries on the right, and a status column between them, so each charge can be read across one row. Use one workbook per card and one tab per statement. Keep the layout the same every month so the checks do not change.
The tabs
Three tabs are enough:
- Statement. One row per line from the card statement: transaction date, posting date, merchant, amount and a statement line number.
- Ledger. One row per entry in the card's liability account for the same period: date, payee, amount, account coded and an entry reference. Export it from your accounting system rather than retyping it.
- Reconciliation. The summary: statement balance, ledger balance, reconciling items and the difference.
Getting the statement into rows
Most card issuers let you download a statement as a PDF, and some also offer a CSV or spreadsheet export. If you have a file, use it. If you have only a PDF, you can retype the lines, or use a tool that reads the PDF into rows. PreciseBankFlow reads credit card statements as well as bank statements, checks that the statement foots, and exports the lines to Excel, which saves the retyping. It is in early access, so check the homepage for current availability.
Sign convention
Pick one sign convention and use it on both tabs. A card is a liability, so purchases increase what you owe and payments and credits reduce it. Many people show purchases as positive and payments as negative on both the statement and ledger tabs. Mixing conventions is a common reason the totals do not agree, so state the rule in a note at the top of the sheet.
Columns to add
Beside the statement rows, add these columns: Receipt? (yes or no), Ledger match (the entry reference, or blank), Status (matched, unmatched, timing or fee) and Note. A final column, Follow-up date, helps with the escalation step later. Freeze the header row so it stays visible as you scroll.
Matching credit card charges to receipts and expense entries
Matching means taking each statement charge and proving two things: a receipt supports it, and a ledger entry records it at the same amount. Work down the statement one line at a time, fill in the receipt and ledger columns, and set the status. A charge is matched only when both are present.
The matching routine
For each statement row:
- Search the ledger tab for the same amount. In Excel,
=COUNTIF(Ledger!D:D, C2)tells you how many ledger rows carry that amount, and=XLOOKUP(C2, Ledger!D:D, Ledger!F:F, "none")returns the entry reference of the first one. Use them as a guide, not as proof. - Check that the date and merchant are plausible. Two charges of the same amount on different days are two items, so make sure each ledger row is used once.
- Find the receipt and confirm that the merchant, date and total agree with the statement line.
- Record the entry reference and mark the row matched.
Duplicate amounts are the main trap. If you pay for the same 49.00 software subscription in two different months, a lookup on amount alone can point both statement rows at the same ledger entry. Number the ledger rows and let each one be claimed only once.
A worksheet with five sample charges
The table below is an example of the reconciliation sheet. The merchants, dates and amounts are made up.
| # | Date | Merchant | Amount | Receipt | Ledger entry | Status |
|---|---|---|---|---|---|---|
| 1 | Mar 03 | Office supply store | 86.40 | Yes | JE-2214, 86.40 | Matched |
| 2 | Mar 08 | Airline | 412.00 | Yes | JE-2219, 412.00 | Matched |
| 3 | Mar 12 | Restaurant | 63.25 | No | JE-2223, 63.25 | Unmatched: no receipt |
| 4 | Mar 19 | Software subscription | 49.00 | Yes | None | Unmatched: not in ledger |
| 5 | Mar 28 | Hotel | 238.50 | Yes | JE-2231, 283.50 | Unmatched: amount differs |
Each unmatched row fails in a different way. Row 3 has a ledger entry but no receipt, so the expense is recorded without proof. Row 4 has a receipt but no ledger entry, so the books are missing a liability and an expense. Row 5 looks like a transposed pair of digits: the ledger says 283.50 and the statement says 238.50, a difference of 45.00. Fixing it takes a correcting entry. Only rows 1 and 2 are done.
Matching in both directions
Do the match twice. First go from the statement to the ledger, which finds charges missing from the books. Then go from the ledger to the statement, which finds entries that never appeared on the card, such as an expense recorded against the card that was actually paid another way. One pass alone misses the other kind of error.
For the same job inside accounting software, including the QuickBooks screens that mark charges and credits, see reconciling QuickBooks credit card charges and credits. This guide covers the Excel sheet that works alongside any system.
How do credit card statement closing dates affect the reconciliation?
A credit card statement closes on the issuer's cycle date, not the last day of your month, so the statement period rarely matches your accounting period. A statement closing on the 14th covers mid-month to mid-month. The ledger has to be cut at the same dates before the two can be compared.
This is the biggest difference from a bank reconciliation. A bank statement is often issued for a calendar month. A card statement is issued for a billing cycle, and the closing day can be any day of the month.
Two ways to handle the cut-off
Reconcile to the statement date. Compare the ledger balance on the statement's closing date to the statement balance. Pull ledger entries dated up to and including that day. This gives a clean tie-out, and it is the simplest method for a monthly routine.
Reconcile to the month end. If your close requires a balance at the last day of the month, start from the last statement balance, add the charges that posted between the closing date and month end, subtract payments, and compare the result with the ledger. Those post-statement charges are listed as a timing item, since they will appear on next month's statement.
Transaction date versus posting date
The statement shows two dates for many charges: when you made the purchase and when the issuer posted it. A charge made on the 13th may post on the 15th and so fall into the next cycle. The ledger usually records the purchase date, while the statement cycle follows the posting date. A purchase that sits in your March books but on the April statement is a timing item, not an error.
Payments in transit
Payments to the card behave like outstanding items too. A payment you sent on the 12th may post on the 16th. If the statement closes on the 14th, the payment is in your ledger but not yet on the statement. List it as a reconciling item on the statement side and expect it to clear next cycle.
Credit card refunds, chargebacks and foreign transaction fees
Lines that are not plain purchases need their own treatment, because they do not have an ordinary receipt. Refunds and chargebacks reduce the balance and should offset an original charge. Fees and interest are created by the issuer, so the statement is their only source and they must be booked from it.
Refunds and credits
A refund appears as a credit. Match it to the original purchase and to the return document, such as a credit note from the merchant. In the ledger, record it against the same expense account as the original, not as other income. If the refund arrives in a later cycle than the purchase, the two sit in different months, and both are legitimate.
Chargebacks and disputes
A chargeback happens when a disputed charge is reversed by the issuer. The statement shows a temporary credit, and it may be reversed again if the dispute is lost. Keep disputed charges visible on the sheet with a status of "disputed", the date raised and the case reference. Do not remove the charge from the ledger until the dispute is settled in your favor. For the rules on spotting charges that appear twice, see the same-payee, same-amount duplicate transaction rules.
Foreign transaction fees
Some cards add a fee to purchases made in another currency or processed abroad. It usually shows up as a separate line near the original charge, or folded into the amount. The receipt will show the foreign amount, so the statement figure will not equal the receipt total. Record the fee in a bank or card charges expense account, or add it to the cost of the item if your policy says so, and mark the row "fee" on the sheet so the difference is explained.
Interest, annual fees and late charges
These have no receipt. The statement itself is the support. Book each one from the statement to an interest or fees account, and note the statement date on the row. A late charge deserves a second look, since it may mean a payment was missed.
Which unmatched credit card charges should you escalate?
Escalate an unmatched charge when it stays unsupported past the follow-up date, when it is large for your policy, or when the cardholder cannot explain it. A missing receipt on a lunch is a reminder. A missing receipt on a large or unfamiliar charge is a control issue.
Setting the rules before the close
Decide the rules once and write them down. A workable set looks like this:
- Ask first. Send the cardholder the list of unmatched charges and give a deadline, such as five business days.
- Escalate by amount. Charges above a set figure go to a manager at once, whether or not the cardholder replies.
- Escalate by silence. Any charge still without a receipt after the deadline goes to the cardholder's manager.
- Escalate by pattern. Several missing receipts from one card in one month, or charges with no business explanation, go to the controller.
What to record
For each escalated item, log the date raised, who was told, the response and the outcome. Possible outcomes are that a receipt arrives, the expense is reclassified as personal and repaid, a fraud claim is filed with the issuer, or the amount is written off with approval. Keep the log on the workbook so the next reviewer sees the history.
A reason to act fast
Disputes have time limits, so a charge that looks fraudulent is worth raising promptly. Check your card agreement for the window rather than assuming one. Waiting for a monthly reconciliation to notice a bad charge uses up part of that time.
Cash-like spending and small floats
Not every small expense runs through the card. If staff also spend from a cash float, that float has its own count and form, covered in the petty cash reconciliation form. When one group of entities shares a card and costs are split, the split balances are matched using the method in the intercompany reconciliation template.