How to remove duplicates in Excel

Remove Duplicates is the quickest, but it deletes rows in place. The other three leave your data alone.

Excel's Remove Duplicates deletes rows the moment you click OK. There is no preview and no report of which rows went, so two minutes of preparation saves a lot of guessing afterwards.

First, work on a copy of the file, or at least keep Ctrl+Z (Cmd+Z on Mac) ready. Second, highlight the duplicates before you remove anything: select the column that defines a duplicate, then go to Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values. Every repeated value lights up, so you can scan what is about to go.

If you want a flag you can filter on instead, add a helper column with =COUNTIF($C$2:$C$1000,C2)>1 (swap C for your key column and 1000 for your last row). It shows TRUE on every row whose value appears more than once, so you can review the repeats before deleting anything.

Remove Duplicates, on the Data tab, deletes every row that matches an earlier row on the columns you pick. It is the quickest way to clear exact repeats from a list.

Select only that column, then Data, Remove Duplicates. If the column sits next to other data, Excel asks what to do with it first:

The UNIQUE function returns a list with the duplicates left out and keeps your original as it is. Type =UNIQUE(A2:D200) in an empty cell, with your own range, and the duplicate-free rows spill into the cells below and to the right. Point it at one column, like =UNIQUE(C2:C200), to get that column's values once each.

The result updates when the source list changes. If the cells it needs are not empty, it shows #SPILL! until you clear them. The copy doesn't keep the original's formatting, so a date column comes out as numbers like 46029 until you format it as a date again. UNIQUE compares whole rows of the range you give it, and its third argument, TRUE, does something else: it returns only the values that appear exactly once.

UNIQUE is in Excel for Microsoft 365, Excel 2021 and later, and Excel for the web. In older versions, use Data, Advanced (in the Sort & Filter group), choose Copy to another location, pick a destination cell and check Unique records only.

Power Query is worth the setup when the same list arrives every week: you build the steps once and refresh them on each new file.

Remove Duplicates compares the value each cell displays. It does ignore casing, so WESTMARCH and Westmarch count as the same, but everything else has to match exactly, and that is where real contact lists slip through:

Operelio's Deduplicate tool is built for the rows Excel leaves behind. Pick one or more columns to match on and it compares every row on those columns. Before you click Run, a live preview counts the duplicate groups and how many rows will be removed. Your original file is never changed: the result is a new file.

You also choose whether the first or the last occurrence in each group survives, which matters when later rows carry the more recent data. Then pick how to match: exact, smart or similarity. Smart matching catches the same record written differently, such as Westmarch Ltd versus Westmarch, Ltd. or (415) 555-0172 versus +1 415 555 0172. Similarity matching catches typos and small differences, such as Jon Smith versus John Smith. When you match on email with exact or smart matching, blank emails don't count as a match unless you switch that off. You can download the removed rows as a separate file to review exactly what was dropped. All three ways of matching are on every plan. It reads .csv, .xlsx, and .xls files.

If the list is headed for a CRM, this step matters more than it looks: HubSpot matches contacts by email when you import, so two rows with the same email become one contact, and one person under two email addresses becomes two.

Google Sheets has its own tool, under Data, Data cleanup, Remove duplicates, and a UNIQUE function that works like Excel's. The menus and their quirks differ, so Sheets gets its own how-to.