Free credit card reconciliation template for Excel (with a worked example)
A credit card reconciliation proves that the card balance in your books agrees with what the statement says you owe, and explains every dollar of difference. This free Excel template sets out the two-sided format for a card account, where the balance is money you owe rather than money you have, totals each list for you and tells you whether you're reconciled. Below: what's in the file, which side each item goes on, a worked example that balances to the cent, and the entries to post afterwards.
Short answer: download the credit card reconciliation template (.xlsx). Enter the statement's new balance and the card's balance in your books, both as positive amounts owed. Charges and payments you've recorded that the card issuer hasn't posted yet go on the statement side; interest, fees, missed charges and refunds that are on the statement but not in your books go on the book side. When both adjusted balances agree, the Difference reads 0.00 and the status says Reconciled. The Example sheet uses the same made-up card as our sample credit card statement: new balance 3,243.88, books 2,089.17, both adjusted to 2,130.28.
Download the free credit card reconciliation template (.xlsx)
Three sheets: a blank Reconciliation, a filled-in Example that reconciles to 0.00, and How to use. It uses only SUM, SUMIF, ROUND, IF, AND, OR, MOD, ABS and TEXT, so it works in Excel, Google Sheets (File, Import), LibreOffice and Numbers. No sign-up, no email, no macros.
1. What's in the template
- Header: the card (last 4 digits only), the statement closing date, prepared by and reviewed by, so the sheet works as a monthly workpaper.
- Statement side: the statement's new balance, plus charges in your books that haven't posted yet, minus payments and credits in your books that haven't posted yet, plus or minus card issuer errors, which gives the adjusted statement balance.
- Book side: the credit card (liability) account balance in your books, plus charges, interest and fees on the statement that aren't in your books, minus refunds, credits and payments on the statement that aren't in your books, plus or minus book errors, which gives the adjusted book balance.
- Difference and status: adjusted statement balance minus adjusted book balance. It reads Reconciled at 0.00. Otherwise a Where to look line names the amount to search for, half of it (a charge entered as a payment), and whether the difference divides by 9 (transposed digits).
- Five lists that feed the summary: charges not posted yet (15 rows), payments and credits not posted yet (10), card issuer errors (5), items on the statement but not in your books (20) and book errors (5). Yellow cells are inputs. Everything else is a formula.
It's the card version of our bank reconciliation template. The layout is the same; what changes is the direction. On a bank account a deposit raises the balance; on a card a charge raises what you owe and a payment lowers it, so every balance in this template is an amount owed, entered as a positive number.
2. The credit card reconciliation format
Each reconciling item goes on exactly one side, depending on who hasn't recorded it yet.
| Item | Goes on | Book entry? |
|---|---|---|
| Charge you've entered that posted after the closing date | Statement side, add | No, it's on next month's statement |
| Payment you've sent that the issuer hadn't posted by the closing date | Statement side, subtract | No, it posts next month |
| Card issuer error (wrong amount, a charge that isn't yours) | Statement side, add or subtract | No, dispute it with the issuer |
| Interest, annual fee or late fee | Book side, add | Yes |
| Charge with no receipt entered (subscriptions, employee cards) | Book side, add | Yes |
| Refund, statement credit or cash back not yet recorded | Book side, subtract | Yes |
| Book error (recorded for the wrong amount, or twice) | Book side, add or subtract | Yes, a correcting entry |
The rule of thumb is the same as for a bank account: if the card issuer hasn't recorded it, it goes on the statement side. If you haven't recorded it, it goes on the book side. Only book-side items change your ledger.
Use the closing date, not the due date. A card statement covers a period ending on the closing (statement) date; the payment due date is weeks later. Run your books report to the closing date, or every payment made between the two dates turns up as a difference.
3. Worked example
The template's Example sheet reconciles the fictional Business Cash Rewards Card ending 7316 from the made-up Example Community Bank for the statement period ending September 30, 2026. That's the same card as our sample credit card statement PDF. Before any adjustments, the statement says 3,243.88 is owed and the books say 2,089.17, a gap of 1,154.71.
Statement side
| New balance per card statement | 3,243.88 |
| Add: office supplies, receipt entered September 30, posted October 1 | 86.40 |
| Less: online payment from checking on September 29, posted October 2 | (1,200.00) |
| Adjusted statement balance | 2,130.28 |
Book side
| Credit card balance per books | 2,089.17 |
| Add: interest charge on the statement | 21.36 |
| Add: software subscription, no receipt submitted | 49.00 |
| Less: refund for returned printer toner | (38.25) |
| Add: fuel charge recorded as 312.40, posted as 321.40 | 9.00 |
| Adjusted book balance | 2,130.28 |
Both sides land on 2,130.28, so the difference is 0.00. The arithmetic: 3,243.88 + 86.40 − 1,200.00 = 2,130.28, and 2,089.17 + 21.36 + 49.00 − 38.25 + 9.00 = 2,130.28.
The payment is the biggest item and the most common one on a card reconciliation: it left checking on September 29, so it's in the books, but the issuer posted it after the closing date. It needs no entry; tick it off on October's statement. The book error is a transposition: 312.40 and 321.40 use the same digits and the 9.00 difference divides by 9.
4. How to fill it in, step by step
- Gather both sides for the same date. Get the card statement and run a report of the credit card account from your books up to the statement closing date (in QuickBooks Online an Account QuickReport, in Xero the Account Transactions report).
- Enter the two balances owed in the yellow cells at the top of the Statement side and the Book side, both as positive numbers. If your books report shows the liability as a negative number, drop the minus sign.
- Tick off what's on both. Match amounts first, then dates a few days apart: a card charge often posts a day or two after the purchase, and a payment a few days after you send it.
- List what's left in your books on the statement side: charges entered that posted after the closing date, and payments or refunds the issuer hadn't posted yet. Bring forward last month's items that still haven't posted.
- List what's left on the statement on the book side: interest, fees, charges nobody entered a receipt for, refunds, statement credits and cash back.
- Put amounts that differ under card issuer errors or book errors, with the sign that corrects them.
- Read the status. At 0.00, post the book-side entries below and file the sheet. If not, see when the difference isn't 0.00.
Reconcile oldest first. Each month starts from the previous month's reconciliation, so work forward in date order. If last month never reconciled, this month won't either. See reconciliation beginning balance is off.
5. Entries to post after you reconcile
Every book-side item needs an entry in your books, dated in the statement period, or next month starts out of balance. In the worked example:
| Item | Entry | Amount |
|---|---|---|
| Interest charge | Debit Interest expense, credit Credit card | 21.36 |
| Software subscription with no receipt | Debit Software (or Dues and subscriptions), credit Credit card | 49.00 |
| Refund for returned toner | Debit Credit card, credit Office supplies | 38.25 |
| Fuel charge recorded 9.00 too low | Debit Fuel (the expense it was coded to), credit Credit card | 9.00 |
In QuickBooks or Xero you'd normally enter these as ordinary card transactions (an expense or spend money for the charges, a credit card credit or receive money for the refund) or edit the mistyped charge, rather than a raw journal entry. Either way the card's balance moves to the adjusted book balance, 2,130.28 in the example. A credit card liability goes up with a credit and down with a debit, the opposite of a bank account.
6. When the difference isn't 0.00
The template's Where to look line uses the size of the difference:
- The difference equals one transaction: it's missing from one of the lists, or ticked on one side only.
- The difference is twice a transaction: that item has the wrong sign. On cards this is usually a refund entered as a charge, or a payment entered as a charge.
- The difference divides by 9: look for transposed digits, such as 321.40 entered as 312.40.
- The difference equals a payment: check the books report runs to the closing date, not the due date or today.
Also check the statement itself adds up: previous balance + charges + interest and fees − payments and credits = new balance. On the sample card that's 2,846.12 + 1,939.94 − 1,542.18 = 3,243.88. Our guide on how to find a reconciliation discrepancy walks through the causes in order; they're the same on a card account. If you imported the statement, check for duplicate transactions after an import too.
7. Card statement only as a PDF?
Ticking off is quicker when the statement is rows you can sort, not a PDF.
Turn the card statement PDF into rows first
Utilly's converter reads a card statement PDF, downloaded or scanned, into Excel, CSV, QuickBooks or Xero files, with charges and payments signed for your books, and checks the rows against the statement's previous and new balance. The preview is free with no sign-up. Try it on the sample card statement first: it's the same card as the Example sheet, and every download of the sample is free because the data is fictional.
Want the statement itself in Excel too? The credit card statement Excel template lays out the same sample card month by month with a balance-owed column. For a bank account, use the bank reconciliation template instead. The bank-account walkthrough is in how to reconcile a bank statement in Excel.
Method: the two-sided format (adjusted statement balance and adjusted book balance) is the standard reconciliation layout, here with the signs of a liability account. The example business, card, names and amounts are fictional. Template built and checked November 1, 2026: the Example sheet recalculates to a 0.00 difference in LibreOffice.
FAQ
What is a credit card reconciliation template?
A spreadsheet that compares what the card statement says you owe with the card's balance in your books. The statement balance is adjusted for charges and payments you've recorded that haven't posted yet; the book balance is adjusted for interest, fees, missed charges and refunds on the statement. When both adjusted balances agree, the card is reconciled.
Is the template free?
Yes. Download the .xlsx with no sign-up or email address. It has a blank sheet, a worked example that reconciles to 0.00 and instructions, and it works in Excel, Google Sheets (File, Import), LibreOffice and Numbers.
Should I enter the balances as negative numbers?
No. In this template every balance is an amount owed, entered as a positive number: the statement's new balance and the card account's balance in your books. If your books report shows the liability with a minus sign, drop it.
Where does a payment that hasn't posted yet go?
On the statement side, subtracted. You've recorded the payment in your books, but the card issuer posted it after the closing date, so it needs no entry. It shows up on next month's statement, where you tick it off.
Which items need an entry in my books?
Only book-side items: interest, annual or late fees, charges nobody entered a receipt for, refunds, statement credits and cash back you hadn't recorded, and corrections of book errors. Statement-side items need no entry.
How do I reconcile a credit card in Excel step by step?
Run a report of the card account from your books to the statement closing date, enter both balances owed, tick off what's on both, list what's left in your books on the statement side and what's left on the statement on the book side, then read the Difference. At 0.00, post the book-side entries.
Related guides
- How to reconcile a bank statement in Excel (step by step with a free template)
- Free bank reconciliation template for Excel (with a worked example)
- Free credit card statement template for Excel (with a sample)
- Free sample credit card statement PDF (fictional, with known totals)
- How to find a bank reconciliation discrepancy: 7 causes and a 15-minute method
- How to convert a credit card statement PDF to Excel
Related tools
- Bank reconciliation checker: compare a bank statement to your books and list missing, duplicate and mismatched transactions.
- Receipt to CSV: turn PDF or photo receipts and invoices into CSV, Excel, or QuickBooks and Xero bill imports.
