Skip to content
Tutorials Center
My learning

Topics

Lesson 44 of 93 ยท Tables, Sorting and Filtering

Finding and Removing Duplicates

Find, highlight and remove duplicates in Excel with Remove Duplicates, the UNIQUE function, COUNTIF flags and conditional formatting, and avoid the risks.

Duplicates sneak into data from every direction: the same customer typed twice, a CSV imported on top of an earlier import, a report with overlapping dates. They inflate totals, break lookups (which return only the first match) and make counts lie. Excel gives you several ways to deal with them. You can highlight them, flag them with a formula, remove them in place, or build a unique list with a formula and leave the source alone.

Deleting data is a one-way street, so the most important part of this lesson is the last one: how deduplication goes wrong. The commands are real Excel; the live examples are stand-ins in JavaScript and pandas that apply the same logic so you can watch which rows are kept.

Four tools

Highlight Conditional formatting shows duplicates.
Flag A COUNTIF column marks them.
Remove Data, Remove Duplicates deletes rows.
UNIQUE A formula builds a clean copy.
1 / 2

What counts as a duplicate

Before using any tool, decide what "the same" means. Two rows can be duplicates on all columns (an exact copy) or only on a key such as email address, order number or the pair (customer, date). Excel's tools compare:

  • Case-insensitively: Ana and ANA match.
  • Exactly otherwise: Ana and Ana (with a trailing space) do not match, and neither do Smith, J. and J. Smith.

Many "missing" duplicates are really spacing problems. Clean the key column first with TRIM and consistent case (see Text Functions).

Highlight duplicates

Select the column, then Home โ†’ Conditional Formatting โ†’ Highlight Cells Rules โ†’ Duplicate Values... and pick a color. Choose Unique in the same dialog to highlight the cells that appear only once. This only changes the look: nothing is deleted, and the highlighting updates when you edit the data. Rules for more complex cases are in Conditional Formatting. Note that this tool looks at a single selected range, so for "same customer and same date" use a formula-based rule or the flag column below.

Flag duplicates with COUNTIF

A helper column gives you something you can sort and filter on. There are two common versions, assuming the keys are in A2:A9:

TEXT
=COUNTIF($A$2:$A$9, A2) > 1       TRUE for every copy, including the first
=COUNTIF($A$2:A2, A2) > 1         TRUE only for the 2nd, 3rd... copies

The second formula uses an expanding range: the start is anchored ($A$2) and the end moves down as you copy the formula (A2, A3, ...). Each row asks "has this value already appeared above me?" That marks every repeat but spares the first occurrence, which is exactly the set of rows you would delete to keep one of each. See Cell References for anchoring and SUMIF, COUNTIF and Their IFS Versions for the function itself.

For several columns use COUNTIFS:

TEXT
=COUNTIFS($A$2:A2, A2, $B$2:B2, B2) > 1      same customer AND same date

Two warnings: COUNTIF treats *, ? and ~ in the criterion as wildcards, and it gives wrong answers for text values longer than 255 characters. For such data use SUMPRODUCT(--(range=A2)) instead (see SUMPRODUCT).

JS Flagging duplicates with an expanding COUNTIF

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Row 4 matches row 2 even though the capitalization differs. Row 6 has a trailing space, so before trimming its count is 1 and it looks unique. After trimming, it is correctly flagged as a repeat of Ben's address in row 3.

Remove Duplicates

Click inside the data and choose Data โ†’ Remove Duplicates. A dialog lists every column with a checkbox, and a My data has headers option. Whichever columns you tick form the key. Two rows are duplicates if all ticked columns match.

  • If you tick every column, only fully identical rows are removed.
  • If you tick only Email, rows with the same email are removed even when other columns differ, and the other columns' data in the removed rows is lost.
  • Excel keeps the first occurrence (the topmost row) and deletes the others, then reports "N duplicate values found and removed; M unique values remain".

There is no preview and the result is a normal edit, so Ctrl+Z is your safety net, and only until you do something else. Remove Duplicates also compares the value as displayed, so the same date formatted two ways may not be recognized as identical. Work on a copy when the data matters.

Because the first row survives, sort before you dedupe if you want a specific row to win. To keep the most recent entry per customer, sort by date from newest to oldest, and then remove duplicates on the customer column. See Sorting Data.

UNIQUE: a formula that builds a clean list

In Microsoft 365 and Excel 2021 or newer, UNIQUE returns the distinct values or rows and leaves the source alone:

TEXT
=UNIQUE(array, [by_col], [exactly_once])
  • =UNIQUE(A2:A9) lists each distinct value once, in order of first appearance.
  • =UNIQUE(A2:B9) treats each row as the item, so duplicates are rows that match in both columns.
  • =UNIQUE(A2:A9, , TRUE) returns only the values that occur exactly once, dropping any value that has a duplicate.
  • by_col set to TRUE compares columns instead of rows.

The result spills, updates when the source changes, and is ideal as the source of a dropdown (see Data Validation and Dropdown Lists). Count the distinct values with =COUNTA(UNIQUE(A2:A9)). Blank cells in the source appear as 0 in the output, so filter them out first with FILTER. More recipes are in FILTER, SORT, SORTBY and UNIQUE.

JS UNIQUE, exactly-once and Remove Duplicates stand-ins

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Notice the Ben rows: they match on customer and product but differ in quantity (1 and 4). Removing duplicates on those two columns silently throws away the quantity 4 sale. Whether that is correct depends on what the rows mean, which is the topic of the next section.

Deduplication risks

  • Real repeats versus duplicates. Two identical orders might be a data-entry mistake, or two customers genuinely buying the same thing. Decide with the data owner.
  • Choosing the wrong key. Too few key columns delete real records; too many leave true duplicates in place.
  • Near-duplicates. Jon Smith, John Smith and J. Smith are not found by exact matching. Standardize first (see Cleaning Messy Data Step by Step) or use Power Query's fuzzy matching.
  • Numbers as text. 00123 and 123 look like duplicates to a human but not to Excel when one is text.
  • Lost information. The deleted rows might hold the only phone number or the latest address. Merge the useful fields first.
  • No audit trail. Prefer a flag column and a filter, review the flagged rows, then delete. Or leave the source untouched and use UNIQUE.

The same ideas in pandas

duplicated is the flag column, drop_duplicates is Remove Duplicates, and subset is the set of ticked columns:

Python drop_duplicates and duplicated in pandas

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In Data โ†’ Remove Duplicates you tick only the Email column. What happens to two rows with the same email but different phone numbers?

    Only the ticked columns define the key. Excel keeps the first row and deletes the others entirely.
  2. 2. What does =COUNTIF($A$2:A2, A2) > 1 flag when copied down a column?

    The range expands as the formula is copied down, so a row is flagged only if its value already appeared above it.
  3. 3. What does =UNIQUE(A2:A9, , TRUE) return?

    The third argument, exactly_once, drops every value that has a duplicate.
  4. 4. Why might "[email protected]" and "[email protected] " (trailing space) both survive Remove Duplicates?

    A trailing space makes the text different. Use TRIM on the key column first.
  5. 5. How can you make sure the newest record per customer is the one Remove Duplicates keeps?

    Excel keeps the first (topmost) row of each key, so sorting beforehand decides which row wins.

Summary

  • Decide what a duplicate is: all columns, or a key. Excel compares case-insensitively but spaces and text/number differences matter.
  • Highlight with conditional formatting, flag with COUNTIF/COUNTIFS (expanding range for repeats only), remove with Data โ†’ Remove Duplicates, or build a clean list with UNIQUE.
  • Remove Duplicates keeps the first row and deletes the rest with no preview; sort first and work on a copy.
  • UNIQUE(array, by_col, exactly_once) is non-destructive and spills; pandas offers duplicated and drop_duplicates.
  • Check for lost data, near-duplicates and genuine repeats before deleting anything.

Next: Dynamic Arrays and Spilling.