How to handle duplicate rows without writing a single formula

The client's CRM exports a new row every time someone touches a record, so their "orders" file has order 10231 three times — once when it was created, once when the address was fixed, once when the status changed to shipped. To a human reading it, it's obviously one order. To your SUM, it's three, and the revenue you report is inflated by every record anyone ever edited. You could reach for a nest of formulas to sort this out. You don't need to — Excel has built-in tools that do it faster and leave less to go wrong.

See the duplicates before you delete anything

Never remove duplicates you haven't looked at. First make them visible: select the key column, then Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Every repeated Order ID turns red, and you can scroll and see the shape of the problem — is it a handful of records, or is half the file duplicated? This is the step people skip, and it's the one that stops you deleting rows that only looked like duplicates.

The example: one order, three rows, one truth

Here's order 10231 as the CRM exported it:

Order ID | Status   | Amount | Updated
10231    | created  | 420    | 2026-06-01
10231    | created  | 420    | 2026-06-02
10231    | shipped  | 420    | 2026-06-05

Three rows, one £420 order, and your total currently says £1,260. Notice the trap: the first two rows are truly identical, but the third differs in the Status and Updated columns — it's not a duplicate, it's the latest version of the same order. This is why "remove exact duplicates" isn't enough on its own. The formula you'd normally reach for here is =COUNTIF(A:A, A2) to count repeats — but you don't need to write it, because the built-in tools handle both cases directly.

Remove them the no-formula way

1. Work on a copy. Duplicate the sheet first — Remove Duplicates deletes rows permanently, with no undo once you've saved and closed.
2. Kill exact duplicates. Select the table, then Data → Remove Duplicates, and tick every column. This removes only rows that match on all columns — the two identical "created" rows collapse to one, and the "shipped" row stays because it genuinely differs.
3. Keep only the latest per key. When you want one row per Order ID and the versions differ, don't use Remove Duplicates — sort by Updated newest-first, then Remove Duplicates ticking only the Order ID column, which keeps the first (newest) occurrence and drops the rest.
4. Or use Power Query for a repeatable version. Data → From Table, then Group By Order ID with "Max" on Updated, and the whole de-duplication re-runs on next month's file with one refresh.
5. Reconcile the count. After removing, check that your row count matches the client's order count. If it doesn't, you removed a real record or missed a duplicate.

Decide what a duplicate means before you touch the button

The mistake isn't using the wrong tool; it's deleting first and defining later. "Duplicate" means something specific to each dataset — an identical row, or an older version of the same key — and only the client's business rule tells you which. Get that definition first, make the duplicates visible second, remove them third, and reconcile fourth. Do it in that order and you never have to explain why the revenue dropped by a number you can't account for.

There's a billing angle, too. "Collapsed 1,400 CRM audit rows into 900 real orders and reconciled the count to your system" is a sentence any client understands and values. Duplicate handling done carelessly quietly loses you the account when the numbers drift; done visibly, with the before-and-after counts written down, it's exactly the kind of catch that earns the next referral.