When your spending total comes out higher than your bank or card statement, duplicates are the first suspect. Two people add the same receipt to a shared album, last week's photos get added again this week, or one purchase is copied once from its receipt and once from the statement. Here's how to find duplicates in Excel, simplest method first.

1. What counts as the same receipt?

Paying the same amount at the same place on the same day twice is rarer than you'd think. So rows with the same date and amount are duplicate candidates, and if the store also matches, it's very likely a duplicate. Store names can vary slightly depending on how they were read or typed, so leaving them out of the first check helps you catch everything.

2. Flag candidates with COUNTIFS

Say dates are in column B and amounts in column D. In a spare column such as F, enter this and fill it down:

=COUNTIFS($B$2:$B$1000, B2, $D$2:$D$1000, D2)

1 means the row is unique; 2 or more means other rows share its date and amount. Sort or filter column F for values above 1 and the candidates line up together.

To include the store as well, add one more condition:

=COUNTIFS($B$2:$B$1000, B2, $D$2:$D$1000, D2, $C$2:$C$1000, C2)

3. Highlight them with conditional formatting

If you'd rather not add a column, color the candidate rows instead.

  1. Select the data range, for example A2:D1000.
  2. Choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
  3. Enter =COUNTIFS($B$2:$B$1000, $B2, $D$2:$D$1000, $D2)>1 and pick a fill color.

Excel's built-in "Duplicate Values" rule only looks at one column at a time, so on the amount column it highlights every repeated amount. For date and amount together, use a formula rule like the one above.

4. Check each candidate

Not every highlighted row is a mistake. Sort them into these cases:

  • The same photo added twice: identical photos, or the same time and authorization code on the receipt. Delete one row.
  • Copied from both a receipt and the statement: the store name may be written differently, but the date and amount match. Delete one, and from now on log from receipts and use the statement only for checking.
  • Two genuine purchases: two orders at a coffee shop, or a bill split into two payments. There are two different photos with different times. Keep both and add a note like "separate purchase" so you don't question it again next month.

Matching rows to receipt photos makes this much faster. See How to name and file receipt photos.

5. Use Remove Duplicates with care

Data > Remove Duplicates deletes duplicates in one go, but it also removes genuine repeat purchases. Save a copy of the file first and select Date, Store and Total as the columns to compare. Even then, it's safer to review the candidates from step 2 before deleting anything.

6. Stop duplicates happening

  • After logging photos from a shared album, move them to a "Logged" album.
  • Define each batch by dates, such as "Monday to Sunday this week".
  • Don't paste card statement rows into your spending table. Keep them on a separate sheet for checking.

Summary

Finding duplicates means flagging rows with the same date and amount using COUNTIFS, then checking photos and times before deleting. Moving photos to a separate album once they're logged prevents most duplicates in the first place.