How to Reconcile Credit Card Statements in Excel

Quick Answer: Put the statement's transactions on one sheet and your receipts or ledger entries on another, each with date, description and a signed amount. Use COUNTIFS to find a ledger entry with the same amount within a few days of each statement line, mark the ones with no match or more than one match, and confirm that previous balance plus charges minus credits equals the statement's new balance.

Reconciling a credit card statement in Excel means matching every line the card issuer printed to something in your own records, and explaining every line that doesn't match. You need the statement as rows, your receipts or ledger as rows, a matching formula keyed on amount and date, and a final tie-out of the statement's own totals. An hour the first time; minutes after that.

What are you actually checking?

Three separate questions, and a reconciliation in Excel should answer each one explicitly:

1. Is the statement internally consistent? Previous balance + purchases + fees + interest − payments − credits = new balance. If your rows don't add up to that, rows are missing or mis-signed, and nothing else is worth doing until they do.
2. Does every statement line have a record on your side? A receipt, an expense report line or a ledger entry. Statement lines without one are either missing paperwork or charges you didn't make.
3. Does every record on your side appear on the statement? Receipts without a statement line are usually timing (the charge posted on the next statement), occasionally a charge that went to a different card.

Questions 2 and 3 are the same matching exercise run in both directions.

Getting the statement lines into Excel

You need one row per statement transaction with at least: posting date, description and amount. Three sources, from easiest to hardest:

• The issuer's activity download. Most card websites export recent activity as CSV. Check that the date range matches the statement period exactly, because the export knows nothing about closing dates.
• Excel's Get Data from PDF. Excel for Microsoft 365 on Windows can import tables from a PDF through Get Data on the Data tab. Card statements often split into one table per page, and you'll need to append them and remove header rows in Power Query.
• A statement converter. QuickBankConvert reads card statement PDFs and returns rows with date, description and signed amount, plus the statement period and opening and closing balances shown above them, which you need for step one. A free account downloads the result as Excel. The credit card statement to Excel page shows the layout.

Whatever the source, decide on one sign convention and stick to it. This guide uses: charges negative, payments and credits positive. If your export shows charges as positive numbers, add a column with =-C2 and use that instead.

Setting up the workbook

Create three sheets:

Turn each range into an Excel table (Ctrl+T) so formulas fill down automatically as you add rows. Make sure dates are real dates, not text: if =ISNUMBER(A2) returns FALSE, the date column needs converting with Data > Text to Columns or DATEVALUE.

Round amounts to cents with =ROUND(C2,2). PDF extraction and currency conversion both produce values like 54.0999999 that will refuse to match 54.10.

How do you match statement lines to receipts in Excel?

Matching on amount alone fails as soon as you have two 12.99 subscriptions. Matching on amount and exact date fails because the receipt date is the purchase date and the statement shows the posting date, often a day or three later. The workable middle: same amount, date within a window.

On the Statement sheet, in the Match count column (D2):

=COUNTIFS(Ledger!$C:$C,C2,Ledger!$A:$A,">="&(A2-4),Ledger!$A:$A,"<="&A2)

That counts ledger rows with the same amount dated from four days before the posting date up to the posting date. Then in Status (E2):

=IF(D2=1,"Matched",IF(D2=0,"No record","Check: "&D2&" candidates"))

Run the mirror formula on the Ledger sheet, looking forward instead of back:

=COUNTIFS(Statement!$C:$C,C2,Statement!$A:$A,">="&A2,Statement!$A:$A,"<="&(A2+4))

A ledger row with zero matches is either on next month's statement or on another card.

If you'd rather pull back the matching description so you can eyeball it, XLOOKUP does that, and its if_not_found argument lets you show "none" instead of #N/A. XLOOKUP returns only the first match, though, so keep the COUNTIFS column to catch the multiple-candidate cases.

Worked example

Reading it: the streaming charge has two ledger candidates, probably because last month's receipt sits in the ledger too; widen nothing, just check dates. The second airline line has no record, which is the classic duplicate charge, and the one to raise with the issuer. The refund has no record because nobody logs refunds; add it to the ledger rather than treating it as a problem.

How do you flag duplicate charges?

Two identical statement lines are not always an error (two coffees on one day), but they are always worth a look. Microsoft's guide to finding and removing duplicates describes Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, which works on a single column. For duplicates across amount and description together, add a helper column:

=COUNTIFS($B:$B,B2,$C:$C,C2)

and apply conditional formatting to rows where it's above 1. Avoid Data > Remove Duplicates on statement data: Microsoft notes the removal is permanent, and a genuine second purchase would vanish from your reconciliation.

Tying out to the statement

On the Tie-out sheet, put the statement's printed figures in one column and your sums in the next:

• Previous balance (from the statement)
• Sum of negative amounts: =SUMIF(Statement!C:C,"<0")
• Sum of positive amounts: =SUMIF(Statement!C:C,">0")
• Calculated new balance, with a balance owed shown as a positive number: previous balance minus both sums, because charges are negative and credits positive (1,000 owed, 500 of charges and a 900 payment gives 1000 − (−500) − 900 = 600)
• New balance (from the statement)
• Difference

If the difference isn't zero, a row is missing, duplicated or has the wrong sign. Sort the Statement sheet by amount and compare against the PDF page by page; a missing row usually sits at a page break, where extraction tools and copy-paste both lose lines. Interest and fees are the other usual suspects because they sit in a separate section of the statement.

Monthly checklist

• Statement rows in, signs consistent, amounts rounded.
• Tie-out difference is zero before matching starts.
• Every "No record" on the statement sheet has a receipt found, a note, or a dispute filed.
• Every "Check" row resolved by date or description.
• Unmatched ledger rows carried forward to next month's statement.
• Workbook saved with the statement PDF and month in the file name.

If you post the reconciled card to accounting software afterwards, the QuickBooks card reconciliation guide covers the same check inside QuickBooks. For checking-account reconciliations in a spreadsheet, see bank reconciliation in Excel, and once the rows are clean, credit card spending analysis shows what to do with a year of them.

Reconciling a backlog of several cards over many months is mostly a data-gathering job. QuickBankConvert's Pro plan adds batch upload for exactly that kind of folder of PDFs; pricing lists what each plan includes.

Related guides

• Credit card statement to Excel
• Bank reconciliation in Excel
• Credit card statement spending analysis
• Reconcile a credit card in QuickBooks