Once your receipts are in a spreadsheet, the obvious question is whether they're complete. There may be charges whose receipts went missing, and receipts for purchases that were later refunded. The reliable check is to match the receipts against your card statement line by line. By hand that can take an hour; in Excel it takes one formula.

1. Put both lists in one file

You need two things:

  • Your receipts: the spreadsheet from the receipt tool, with No. in column A, Date in B, Store in C and Total in D, on a sheet called Receipts.
  • Your card transactions: most banks and card issuers let you export transactions for a date range as CSV or Excel from online banking. The menu is usually called something like Download transactions or Export.

Copy the transactions into a new sheet in the receipts file and name it Statement. Keep only the columns you need, in this order: A transaction date, B description (merchant), C amount. If your export shows card spending as negative numbers, convert them to positive first: type -1 in a spare cell, copy it, select the amount column and use Paste Special → Multiply.

2. Make sure the dates are real dates

The match is on date and amount, so both sheets need real Excel dates, not text that looks like a date. Bank exports often arrive as text, for example 14/09/2026 or 20260914.

A quick test: real dates are right-aligned by default, text is left-aligned. If they're text, select the column and run Data → Text to Columns → Next → Next → Column data format: Date and choose the order your bank uses (DMY or MDY), then Finish. More fixes are in Fixing Excel dates that show as numbers, text or the wrong day. Files downloaded from ReceiptMoa already contain real dates.

3. Find a receipt for each charge

On the Statement sheet, type Receipt in D1, put this in D2 and fill down:

=COUNTIFS(Receipts!D:D, C2, Receipts!B:B, ">="&A2-1, Receipts!B:B, "<="&A2+1)

It counts receipts with the same amount and a date within one day either side. The one-day margin covers purchases near midnight and banks that post a transaction a day after it was made. If your statement lists the posting date rather than the transaction date, widen the margin to 2 or 3 days.

  • 1: there's a matching receipt.
  • 0: a charge with no receipt. It may be lost, or it may be something that never came with a paper receipt, such as a subscription, an online order or transit fare.
  • 2 or more: several receipts with the same amount on the same day. Either you really bought it twice, or the same receipt went in twice. See How to find duplicate receipts in an Excel spreadsheet.

4. Find a charge for each receipt

Check the other direction too. On the Receipts sheet, type Statement in E1, put this in E2 and fill down:

=COUNTIFS(Statement!C:C, D2, Statement!A:A, ">="&B2-1, Statement!A:A, "<="&B2+1)

A 0 here means the receipt wasn't paid with this card. It may have been cash, another card, or a purchase that was cancelled. For cash, mark it the way How to track cash spending when there's no receipt suggests so it doesn't confuse you again next month.

5. Highlight the zeros

With a long list, zeros are easy to miss. Color them on both sheets:

  1. Select the data range, for example A2:D200 on Statement.
  2. Go to Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
  3. Enter =$D2=0 (or =$E2=0 on the Receipts sheet) and pick a fill color.

Now you only need to look at the colored rows. A filter on the count column showing only 0 works just as well.

6. Why amounts often don't match

A zero doesn't always mean something is missing. The same purchase can show up with a different amount or date:

  • Refunds and partial refunds: the statement may list the original charge and the refund as two lines, the refund as a negative or credit amount. Remove both or add a note.
  • Tips: at restaurants in many countries, the itemized receipt shows the bill before tip, but the card is charged the bill plus tip. Use the signed card slip, or add the tip to the receipt total.
  • Foreign purchases: the statement shows the amount converted to your home currency, often with a fee added. See How to convert foreign currency receipts in a spreadsheet.
  • Pay-at-pump fuel, hotels, car rentals: some merchants place a temporary hold first and settle the final amount later. The statement normally shows the final amount, which may differ from the hold you saw in your banking app.
  • Different merchant names: the statement may show a company name or a payment processor instead of the shop sign. That's why this guide matches on date and amount, not on the store name.
  • A misread receipt total: a single wrong digit from the photo or from typing. If two unmatched rows share a date and have similar amounts, look at the receipt photo again and correct it.

If a charge is still unexplained after all that, your card issuer can tell you more about the merchant.

Summary

Export your card transactions, paste them on a Statement sheet, convert the dates to real dates, then use COUNTIFS on both sheets to count rows with the same amount and a date within a day. A 0 means no match; 2 or more suggests a duplicate. Doing this once a month catches lost receipts and refunded purchases early. For the rest of the month-end routine, see A 30-minute monthly expense review, step by step.