Excel: Find and Remove Duplicate Rows the Safe Way

You need to find and delete repeated rows in a list without wrecking the rest of your data.

Why: “Remove Duplicates” permanently deletes the extra copies, so it’s worth previewing duplicates first and being deliberate about which columns count. A row is a duplicate only if every column you select matches another row — so the columns you tick decide what “duplicate” even means.

Fix 1: Highlight duplicates first so you can review them

Before deleting anything, see what’s actually repeated. Conditional formatting only colors the cells — it changes no data.

1. Select the cells you want to check for duplicates.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. In the dialog, next to values with, choose a format (for example Light Red Fill with Dark Red Text) and click OK.

Every duplicate value is now shaded. Scroll through, confirm they’re genuine duplicates and not coincidental matches, then move on to removing them.

Fix 2: Delete duplicates with Data > Remove Duplicates

This permanently removes the extra rows, keeping the first occurrence of each.

1. Click any cell inside your data (or select the specific range).
2. Go to Data > Remove Duplicates.
3. Under Columns, check or uncheck the columns that define a duplicate:
   • Tick all columns to remove only rows that are identical across every field.
   • Tick a single column (say, Email) to remove rows that repeat in just that column, even if other fields differ.
4. If your range includes a header row, tick My data has headers so the headers aren’t treated as data.
5. Click OK. Excel reports how many duplicate values were removed and how many unique values remain.

Excel keeps the first occurrence of each value and deletes the later identical ones.

Fix 3: Protect yourself before you delete

Remove Duplicates is permanent, so set up a safety net:

• Copy the data first. Before you delete, move or copy the original to another worksheet so nothing is lost if you pick the wrong columns.
• Remove outlines and subtotals. You can’t remove duplicates from outlined or subtotaled data — clear those first via Data > Subtotal > Remove All.
• Mind blank cells and spaces. The counts of removed and remaining values can include empty cells and stray spaces, so clean trailing spaces (TRIM) beforehand if they’d cause near-duplicates to be treated as distinct.

FAQ

Can I keep the duplicates and just hide them? Yes — use Data > Advanced (filter) and tick Unique records only to filter for unique values. That hides duplicates without deleting them, so you can restore the full list by clearing the filter.

Which copy does Excel keep? The first occurrence in the list. If a specific row should win, sort the data so your preferred copy is on top before running Remove Duplicates.

I removed duplicates but some obvious repeats stayed. Those rows differ in a column you left ticked (a trailing space, a different date, capitalization in some setups). Narrow the ticked columns to just the field that defines a duplicate, or clean the data with TRIM first.

Sources: Microsoft Support — Find and remove duplicates · Microsoft Support — Filter for unique values or remove duplicate values