Data → Remove Duplicates is one click, and that’s the problem. It keeps the first copy of each duplicate it meets, silently deletes the rest, and reports only a count — “14 duplicate values removed”. Which fourteen? Removed why? Were two of them genuinely different orders that happened to share a name? The dialog has already closed. The rows are already gone.

Deleting data should never be the first move. The professional routine is three steps — find, understand, then remove — and it turns the most dangerous click in the Data tab into a safe one.

First: what is a duplicate, here?

Before any tool, answer the only question that matters: two rows are duplicates when what matches? The whole row? The email address? Name and date? Get this wrong and no tool can save you:

  • Match on too few columns and you’ll delete real data — two “John Murphy” rows may be two different orders.
  • Match on the whole row and near-duplicates survive — "Dublin" and "Dublin " don’t match, which is why the cleaning routine always runs first. Trim before you compare; invisible spaces are how duplicates hide.

Your answer — the key — drives everything below.

Second: count before you cut

Add a helper column that counts each key’s appearances:

=COUNTIFS([Email], [@Email])

(One condition per key column — the COUNTIFS pattern.) Every row now announces its own multiplicity: 1 means unique, anything higher means this row has siblings. Filter to counts above 1 and look at them — this is the step Remove Duplicates skips, and it’s where the actual decisions live:

count first — the duplicates identify themselves niamh@byrne.ie 1 sean@obrien.ie 2 aoife@murphy.ie 1 sean@obrien.ie 2 =COUNTIFS(...) filter to >1, then LOOK: same order twice? or two real orders? no row has been deleted yet — that's the point
The count column turns invisible duplicates into visible facts. Deletion becomes a decision you make with your eyes open — not a report you receive.

Same order keyed twice? Duplicate — mark it. Two real orders sharing an email? Not a duplicate — your key was too narrow; widen it and recount. An import run loaded twice? That’s not row-by-row cleanup, that’s a source problem for the recipe to fix upstream.

While deciding which copy to keep, the usual rule is “most recent” or “most complete” — sort by your key then by date descending, and the keeper floats to the top of each group.

Third: now remove — with a receipt

Only now is the one-click tool safe, because you know exactly what it will do. Work on a copy (rule zero never changes), select the Table, Remove Duplicates, tick only your key columns, and the count it reports should match the number you already expected. That agreement is your receipt. If it removed 14 and you expected 9, stop and find the five.

Prefer never deleting at all? Two civilised alternatives. Keep every row and add an IsDuplicate flag — =COUNTIFS(...)>1 wrapped in an IF — then exclude flagged rows in your analysis; the data survives, the sums are right, and the audit trail is free. Or produce a clean copy and leave the original untouched: =UNIQUE(Orders) spills a de-duplicated table that updates itself — removal as a view, not an amputation.

The habit

Duplicates are stage two of the path because they sit exactly where clean data meets judgement: the counting is mechanical, but what counts as the same thing is a decision only someone who understands the data can make. Make it explicitly, make it before deleting, and keep the receipt.

Count, look, decide — then, and only then, remove. Quick is nice; recoverable is better.