How to Remove Duplicates in Excel (3 Ways)

To remove duplicates in Excel, select your data and use Data > Remove Duplicates: Excel deletes repeated rows and keeps the first copy of each. If you'd rather not delete anything, the UNIQUE function or Advanced Filter builds a clean copy instead. This guide shows all three ways on one customer list, and how to choose which columns count as a duplicate.

What counts as a duplicate?#

A duplicate is a row whose values match another row in the columns you choose. That choice matters. Look at this list in A1:C9:

• Checking all three columns, rows 4 and 8 are duplicates. They match rows 2 and 5 exactly.
• Checking Customer only, rows 4, 6, 8 and 9 are duplicates, because Acme, Birch and Cedar already appeared.

Before deleting, it can help to see the repeats. The guide to highlighting duplicates colours them with one conditional formatting rule.

Way 1: The Remove Duplicates button#

This is the fastest way, and it changes the data in place. It works in every version of Excel on Windows and Mac.

1. Make a copy of the sheet first: right-click the sheet tab and choose Move or Copy, then tick Create a copy.
2. Click any cell inside the data.
3. Go to Data > Remove Duplicates (Windows ribbon keys: Alt, A, M).
4. Make sure My data has headers is ticked.
5. Tick the columns that must match. Leave all ticked to compare whole rows.
6. Click OK.

With all columns ticked, Excel reports that it found and removed 2 duplicate values and that 6 unique values remain. With only Customer ticked (click Unselect All, then tick Customer), it removes 4 rows and keeps one row each for Acme, Birch, Cedar and Delta.

Excel keeps the first row of each set, counting from the top. If you want to keep the newest record instead, sort the data newest first before removing duplicates. Press Ctrl+Z (Mac: ⌘Z) right away if the result is wrong.

Way 2: The UNIQUE function#

UNIQUE returns the list without repeats in a new place and leaves your data alone. It needs Microsoft 365, Excel 2021 or Excel 2024.

=UNIQUE(A2:C9)

Type it in E2 and it spills six rows: the same result as Remove Duplicates on all columns. Because it's a formula, it updates when the data changes. Add another "Delta, York, Basic" row and extend the range, and the result still shows Delta once.

UNIQUE has two optional arguments:

The last one is different from removing duplicates: it drops every copy of a repeated row, so Acme-Leeds-Basic and Cedar-Bath-Pro disappear completely. The UNIQUE function guide explains each argument. To refer to the whole result later, use the spill range operator, as in =ROWS(E2#); see how the # spill reference works.

Way 3: Advanced Filter with Unique records only#

Advanced Filter copies the unique rows to a new spot, and it works in every version, including Excel 2019 and older.

1. Click inside the data and go to Data > Advanced (in the Sort & Filter group).
2. Choose Copy to another location.
3. Check that List range is $A$1:$C$9.
4. In Copy to, click an empty cell, such as E1.
5. Tick Unique records only and click OK.

Excel copies the headers and the six unique rows to E1:G7. The result is fixed values, so run it again if the data changes. To check one column only, set the List range to that column, such as $A$1:$A$9.

Which way should you use?#

Pick by whether you want to keep the original and whether the data will change:

The side-by-side comparison of UNIQUE vs Remove Duplicates goes deeper. A UNIQUE list also makes a good source for dynamic drop-down lists, and it's the first step in summarizing data without a pivot table.

Why aren't my duplicates removed?#

Excel only removes values that match exactly. If duplicates survive, check for:

• Extra spaces. "Acme" and "Acme " (with a trailing space) are different. Clean the column with TRIM first.
• Numbers stored as text. 1001 as a number and "1001" as text don't match. Convert the text with Data > Text to Columns or VALUE.
• Different formats. Remove Duplicates compares what the cells display. The same date shown as 1/3/2024 in one cell and 1-Mar-2024 in another is not treated as a duplicate.
• Only one column selected. If you selected a single column and chose Continue with the current selection, Excel compares and changes only that column, which can push its values out of line with the rest of each row. Undo, click one cell and choose Expand the selection instead.
• Merged cells or subtotals. Remove them before you start.

Capital letters don't matter: Remove Duplicates and UNIQUE both treat "ACME" and "Acme" as the same value.

Practise it#

Cleaning duplicate rows is a standard task in data-entry and admin tests. Practise UNIQUE in a live spreadsheet: you type the formula and it checks it instantly. Then practise filtering a table to find and check repeats before you delete anything. Start with the exercise on this page.