Home / Tech
Tech

How to Remove Duplicates in Excel: 4 Easy Methods

Illustration for How to Remove Duplicates in Excel: 4 Easy Methods

Duplicate rows creep into almost every spreadsheet: a customer pasted in twice, an invoice number imported again, a mailing list merged from two sources. Knowing how to remove duplicates in Excel takes a few minutes to learn and can save hours of manual tidying. This guide covers four methods, from the one-click button to formulas, plus the mistakes that lead people to delete the wrong data.

Illustration showing the main idea of How to Remove Duplicates in Excel: 4 Easy Methods

Before you start: protect your original data

Microsoft Support warns that the Remove Duplicates feature permanently deletes the duplicate data, and it suggests copying the original data to another worksheet first. Do that now: right-click the sheet tab, choose Move or Copy, tick Create a copy and click OK. If you do delete the wrong rows, press Ctrl+Z straight away, but do not rely on undo once the file has been saved and closed.

Method 1: Use the Remove Duplicates button

This is the fastest route. It is available in Excel for Microsoft 365, Excel 2024, 2021, 2019 and 2016, and a version also exists in Excel for the web and Excel for Mac.

  1. Select the range of cells, or click any cell inside a table.
  2. Go to the Data tab and, in the Data Tools group, select Remove Duplicates.
  3. In the dialogue box, tick the columns Excel should compare. If your list has headings, keep My data has headers ticked.
  4. Select OK. Excel reports how many duplicate values it removed and how many unique values remain.

Three details catch people out. First, Microsoft explains that data is removed from all columns in your selection, even if you only tick some of them, so check the selection before you click OK. Second, Excel judges duplicates by the value shown in the cell, not always the value stored behind it, so a date displayed in two different formats can be treated as two different values. Third, you cannot remove duplicates from data that has an outline or subtotals; remove those first.

Method 2: Highlight duplicates first with conditional formatting

If you would rather review duplicates before anything is deleted, highlight them instead.

  1. Select the cells you want to check.
  2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
  3. In the box next to values with, pick the formatting you want, then select OK.

Every matching value is coloured, including the first occurrence, so you decide which ones to delete. Microsoft notes that Excel cannot highlight duplicates in the Values area of a PivotTable report. To clean up quickly, switch on a filter (Data > Filter), filter the column by cell colour and delete the rows you no longer need.

Method 3: Extract unique values with Advanced Filter

Filtering for unique values hides the duplicates temporarily, whereas removing them deletes them for good, as Microsoft’s guidance points out. Advanced Filter lets you keep your original list and build a clean copy beside it.

  1. Select the range, including the heading row.
  2. Go to Data > Sort & Filter > Advanced.
  3. Choose Copy to another location and tick Unique records only.
  4. Click in Copy to, pick an empty cell for the results and select OK.

Method 4: Flag duplicates with a formula

Formulas give you control when you want to decide exactly which rows count as duplicates. In a spare column next to your first data row, enter this formula and fill it down:

=COUNTIF($A$2:A2,A2)

It counts how many times the value in column A has appeared from the top of the list down to the current row, so the first occurrence shows 1 and later repeats show 2, 3 and so on. Filter the helper column for numbers greater than 1 and delete those rows. Microsoft’s own example for highlighting duplicates uses a similar COUNTIF formula.

If you use a current version of Microsoft 365 or Excel 2021 or later, the UNIQUE function can return a clean list in one step: =UNIQUE(A2:A100). The original data stays untouched.

Which method should you use?

Method Deletes data? Best for
Remove Duplicates button Yes, permanently Quick clean-up of a copy of your data
Conditional formatting No Reviewing duplicates before you decide
Advanced Filter No, if you copy to another location Building a clean list of unique records
COUNTIF or UNIQUE formula No Custom rules and lists that update themselves
Checklist-style illustration for How to Remove Duplicates in Excel: 4 Easy Methods

Common problems and how to fix them

Duplicates that Excel does not catch. Stray spaces are the usual culprit: “Smith” and “Smith ” look identical but are different values. In a spare column use =TRIM(A2), fill it down, copy the results and paste them back as values before running Remove Duplicates. Numbers stored as text can cause the same problem.

Capitalisation. The Remove Duplicates button is not case sensitive, so “ACME” and “acme” are treated as the same entry. If case matters, compare values with a formula that uses the EXACT function instead.

Too many columns selected. If Excel removes more than you expected, undo it and tick only the columns that define a duplicate, such as an email address or an invoice number.

Stop duplicates entering your sheet in future

Data validation can block repeat entries as they are typed. Select the column, go to Data > Data Validation, choose Custom under Allow and enter =COUNTIF($A:$A,A1)=1, adjusting the column letter to suit. Excel then rejects a value that already exists in that column. This is handy for invoice numbers, which should be unique on every UK invoice: see our guide to what to include on a UK invoice.

Clean data also makes bookkeeping for a small business far less painful, and if you want to work faster once your data is tidy, our guide to Excel keyboard shortcuts is a good next step. Repetitive clean-ups can even be automated, as explained in what a macro in Excel is.

Frequently asked questions

Does Excel keep the first or last duplicate?

The Remove Duplicates button keeps the first occurrence it finds and deletes the later ones, so sort your data first if the order matters.

Can I undo Remove Duplicates?

Yes, press Ctrl+Z immediately afterwards. Once the file is saved and closed you cannot get the rows back, which is why Microsoft recommends working on a copy.

How do I remove duplicates based on one column only?

Select your data, open Data > Remove Duplicates and untick every column except the one that defines a duplicate. Excel keeps one row for each unique value in that column and removes the rest.

Does Remove Duplicates work in Excel for the web?

Yes. Microsoft’s support pages state that you can remove duplicate values in Excel for the web, although some other features differ from the desktop app.

How do I find duplicates without deleting them?

Use conditional formatting to highlight them, or add a COUNTIF helper column. Neither method deletes anything.

What is the difference between filtering and removing duplicates?

Filtering for unique values hides duplicates temporarily, while removing duplicates permanently deletes them from the sheet.