Duplicate data is one of the quietest ways to wreck a report. Two rows for the same customer double a revenue figure. Three spellings of one supplier split its spend across three lines. Nobody sees an error, because every number looks plausible.

This guide shows small teams how to find duplicate data in a spreadsheet, decide what to merge, and keep it from coming back.

Why duplicate data slips through

Most duplicates are not exact copies. They come from small differences that a person reads as the same thing and a spreadsheet does not.

  • "Acme Ltd", "ACME Limited" and "Acme Ltd." in the same column.
  • A trailing space after a name that hides it from a lookup.
  • The same invoice exported twice from two overlapping date ranges.
  • A customer who signed up with two email addresses.

An exact-match filter finds none of these. That is why a quick "remove duplicates" click often leaves the worst ones behind.

Step 1: Decide what makes a record unique

Before you search, pick the key. A key is the field, or combination of fields, that should appear only once. For invoices it may be the invoice number. For customers it may be the email address plus the postcode.

Write the key down. If your team cannot agree on it, that disagreement is the real problem, and no tool will fix it.

Watch for weak keys

A name alone is a weak key. Two different people can share a name, and one person can have many spellings. Prefer an ID, an email address or a document number.

Step 2: Clean the key before comparing

Standardise the key column on a copy of the data, not the original.

  1. Trim leading and trailing spaces.
  2. Convert text to one case.
  3. Remove punctuation that carries no meaning, such as full stops in company names.
  4. Convert dates to one format.

After this, run a count of each key value. Any value that appears more than once is a candidate duplicate. In a spreadsheet, a COUNTIF column or a pivot table does the job.

Step 3: Review candidates by hand

Do not delete in bulk. Sort the flagged rows so matches sit together and read them.

Ask three questions for each pair:

  • Are these the same real thing, or two things that look alike?
  • Which row has the more complete and more recent details?
  • Does anything in the other row need to be kept, such as a second phone number?

Merge the useful details into one row, then remove the other. Keep a log of what you removed and why. Our data sorting page explains how ordering your data makes this kind of review faster.

Step 4: Check totals before and after

A cleanup should change totals only in ways you can explain. Record the row count and the key totals before you start. Afterwards, compare them.

For example, imagine a sales sheet with 1,200 rows. You remove 35 true duplicates. The row count should fall by exactly 35, and the revenue total should fall by the sum of those 35 rows. If revenue drops by more, you removed something you should not have. This is the same idea behind data validation: test the result against a rule you can state in advance.

Stop duplicates at the door

Cleaning is a repeat cost. Reduce it at the source.

  • Use dropdown lists for fields such as supplier or product category.
  • Require a unique ID when a record is created.
  • Run the same cleanup steps every time you import a new file.
  • Assign one person to own each master list.

If your reports feed decisions, a short check for duplicate data belongs in the routine before every release. The business intelligence page describes how clean inputs lead to reports you can trust.

A short checklist

  • Name the key field for each table.
  • Clean the key on a copy.
  • Count each key value and flag repeats.
  • Review pairs by hand and merge details.
  • Compare row counts and totals before and after.
  • Log every change.

Ten minutes on this list is usually cheaper than explaining a wrong number in a meeting.