Removing Duplicates in Excel Versus SELECT DISTINCT

Remove Duplicates is a button, and it decides things silently: which rows count as duplicates, which one survives and whether [email protected] and [email protected] are the same person. SQL makes you say all of that. This post reproduces the button with DISTINCT and window functions, shows how to choose the surviving row on purpose and covers finding and deleting duplicates in a table. It is a spoke of translating Excel formulas into SQL.

The data

WITH contacts(id, email, name, created) AS (
  VALUES
    (1, '[email protected]',  'Ann',   DATE '2024-01-01'),
    (2, '[email protected] ', 'Ann',   DATE '2024-02-01'),
    (3, '[email protected]',  'Bob',   DATE '2024-01-05'),
    (4, '[email protected]',  'Ann B', DATE '2024-03-01')
)

Row 2 has an uppercase letter and a trailing space. Rows 1, 2 and 4 are, to a person, the same contact.

What Remove Duplicates does

You select the columns to compare, and Excel deletes rows that match an earlier row on all of them. Three properties are worth knowing:

• It keeps the first row in the sheet's order and deletes the later ones.

• It compares text without regard to case: [email protected] and [email protected] are duplicates.

• It does not trim spaces: [email protected] with a trailing space is different from [email protected].

On the email column alone, Excel would treat rows 1 and 4 as duplicates (case-insensitive match) and keep row 1, and would keep row 2 as a separate email because of the trailing space, leaving rows 1, 2 and 3. That is rarely what the person who pressed the button wanted, and they will not notice.

SELECT DISTINCT

SELECT DISTINCT returns one copy of each distinct row among the selected columns:

SELECT DISTINCT email FROM contacts ORDER BY email;

   email
-----------
 [email protected]
 [email protected]
 [email protected]

Three distinct values in PostgreSQL, because comparison is case-sensitive and spaces count. DISTINCT has no notion of which row to keep, since it returns only the selected columns; you cannot select id and name and still collapse on email. If you add them to the select list, the rows differ again and nothing collapses. So DISTINCT answers "which different values exist", which is a list of unique values, not "give me one full row per key".

Normalize before comparing to get the answer Excel's case-insensitivity suggests, and a trim to fix the space:

SELECT DISTINCT lower(trim(email)) AS email FROM contacts ORDER BY 1;

  email
----------
 [email protected]
 [email protected]

Two unique emails. That is the honest count of different contacts by email.

Keep one full row per key

To keep one complete row for each normalized email, with a rule for which one, number the rows within each group and keep the first:

SELECT id, lower(trim(email)) AS email, name, created
FROM (
  SELECT c.*,
         row_number() OVER (PARTITION BY lower(trim(email)) ORDER BY created, id) AS rn
  FROM contacts c
) t
WHERE rn = 1
ORDER BY id;

 id |  email   | name |  created
----+----------+------+------------
  1 | [email protected] | Ann  | 2024-01-01
  3 | [email protected] | Bob  | 2024-01-05

PARTITION BY defines the duplicate group (the normalized email), and ORDER BY created, id defines which row is first: the earliest, with the id as a tie-breaker. To keep the latest instead, use ORDER BY created DESC, id DESC, which would keep rows 4 and 3. This is the step Remove Duplicates hides: "first" in a sheet means "whichever came first in the rows", an accident of how the data was pasted. In SQL you decide: earliest, latest, the most complete record, the one with a non-null phone number.

PostgreSQL has a shorthand, DISTINCT ON:

SELECT DISTINCT ON (lower(trim(email))) id, lower(trim(email)) AS email, name, created
FROM contacts
ORDER BY lower(trim(email)), created, id;

The ORDER BY must start with the DISTINCT ON expression, and the remaining terms decide which row of each group survives. It is concise and specific to PostgreSQL; the row_number() form works everywhere.

Find duplicates first

Before deleting anything, look at what is duplicated, the way you would count matches with COUNTIF versus COUNT with a filter in SQL. Duplicate keys in a lookup table also cause the doubled totals described in VLOOKUP and XLOOKUP versus SQL JOIN:

SELECT lower(trim(email)) AS email, count(*) AS n, array_agg(id ORDER BY id) AS ids
FROM contacts
GROUP BY lower(trim(email))
HAVING count(*) > 1;

  email   | n |  ids
----------+---+---------
 [email protected] | 3 | {1,2,4}

One duplicate group with three rows, listing the ids. The HAVING clause keeps only groups with more than one row. Note also that the names differ (Ann, Ann, Ann B): the rows are duplicates on email and not identical records. Choosing which survives loses the other values, and a person may want to merge fields before deleting. Remove Duplicates would have thrown the extra information away without a word.

In Excel, the matching step is Conditional Formatting, Highlight Cells Rules, Duplicate Values, or a helper column with =COUNTIF(A:A, A2)>1. The SQL above is the equivalent, with the advantage that it lists the ids.

Delete duplicates from a table

In a database, you delete by naming the rows to remove. Use the window query to identify them, and check the count before you run it:

SELECT count(*) FROM (
  SELECT row_number() OVER (PARTITION BY lower(trim(email)) ORDER BY created, id) AS rn
  FROM contacts
) t WHERE rn > 1;

For the sample this returns 2, the rows 2 and 4. Then delete inside a transaction:

BEGIN;
DELETE FROM contacts
WHERE id IN (
  SELECT id FROM (
    SELECT id, row_number() OVER (PARTITION BY lower(trim(email)) ORDER BY created, id) AS rn
    FROM contacts
  ) t
  WHERE rn > 1
);
-- check the count and the survivors, then COMMIT or ROLLBACK

The delete removes ids 2 and 4 and leaves 1 and 3. Take a backup or work on a copy first, and check that no other table refers to the deleted ids through a foreign key: the delete either fails or cascades. When the table has no key (a staging table of raw rows), use the physical row identifier of your engine (ctid in PostgreSQL, rowid in SQLite and Oracle), with care, or create a new table of the distinct rows and swap it in.

The long-term fix is a unique constraint on the normalized key, so duplicates cannot return: a unique index on lower(trim(email)) in PostgreSQL. Cleaning once without the constraint means cleaning again next quarter.

Duplicates across several columns

Remove Duplicates lets you tick several columns; two rows are duplicates only when all of them match. In SQL, list them in PARTITION BY or GROUP BY: PARTITION BY lower(trim(email)), name treats Ann and Ann B as different contacts and keeps both rows 1 and 4 apart. Choose the columns that define identity in the business, not the ones that happen to be available. A name is a poor key; an email is better; a customer id from the source system is best.

Duplicates that are not exact

Exact matching after normalization is the baseline. Real data has near-duplicates: Jon Smith and John Smith, a changed address, a transposed digit. Remove Duplicates and DISTINCT cannot help. Fuzzy matching needs trigram similarity (pg_trgm), phonetic functions or dedicated tools, and it produces candidates that a person should review, not rows to delete blindly. Keep exact deduplication and fuzzy matching separate, and never delete on a similarity score without review.

Verify

Check that the number of surviving rows equals the number of distinct normalized keys. Check that no key appears twice in the result. Compare with the Excel outcome only after aligning the rules (case, spaces, which row is first), and document the rules. If the workbook's result and the SQL differ by a few rows, the case and space rules are the first place to look.

When Remove Duplicates is the right tool

A one-time cleanup of a small list. If you will use the list once and can eyeball the result, the button is fine.

You do not care which row survives. For a list of unique values with no other columns, any method works.

The data lives only in the sheet. Loading it into a database for a single dedupe is more work than the click.

You want to see the effect. Excel tells you how many duplicates it removed and how many remain; check that message and keep a copy.

Excel's size limits for the sheet are listed in Excel specifications and limits, and PostgreSQL documents DISTINCT in its SELECT reference. To check a small extract as JSON, use the CSV to JSON converter.