How to Remove Duplicates in Excel (4 Easy Ways)

Duplicate rows creep into almost every spreadsheet that has been copied, merged or exported more than once. A customer appears twice, a transaction is imported from two overlapping downloads, or someone pastes the same block of rows at the bottom of a list. Excel gives you several ways to deal with this, and each suits a different situation.

This guide walks through four methods, from the quickest one-click fix to a formula that keeps a live de-duplicated copy. It also explains why Excel sometimes leaves duplicates behind, which is the part most people get stuck on.

Before you start: decide what a duplicate is

Two rows can be “the same” in different ways. Settle this first, because every method below asks you, one way or another.

• Whole-row duplicates. Every column matches. This is the safest test for transactions and exports, where two genuine records can share a date and amount but differ in a reference number.
• Key-column duplicates. Only one or two columns need to match, such as Email or Customer ID. The rest of the row may differ, for example an older phone number.

Also make a backup. Copy the sheet (right-click the sheet tab, choose Move or Copy, tick Create a copy) so you can always go back to the original.

Method 1: The Remove Duplicates command

This is the fastest way and the one most people need.

1. Click any cell inside your data, or select the exact range you want to check. If your data is formatted as a table, clicking inside it is enough.
2. Go to Data › Data Tools › Remove Duplicates.
3. Leave My data has headers ticked if the first row holds column names, so the header row is not treated as data.
4. Tick the columns that must match. Keep them all ticked for whole-row duplicates, or click Unselect All and tick only your key columns, such as Email.
5. Click OK. Excel tells you how many duplicate values were removed and how many unique values remain.

A few things to know about how it behaves:

• It keeps the first occurrence and deletes the later copies. If you want to keep the newest record instead, sort your data so the newest rows are at the top before you run it.
• It deletes in place. Rows are gone from the sheet straight away. You can press Ctrl+Z to undo, but once you save and close the file, the deletion is permanent.
• It only looks at the selected range. If Excel guesses the wrong area, select the full range yourself before you start.
• It compares the underlying values, not the formatting. A cell showing 1 and another showing 1.00 hold the same number, so they match. Bold text, fill colours and number formats make no difference.
• It is not case-sensitive. “ACME Ltd” and “acme ltd” count as the same value.

Method 2: Highlight duplicates first, then delete

If you want to see what will go before anything is removed, highlight the duplicates and review them.

Highlight with conditional formatting

1. Select a single column, for example the Email column.
2. Go to Home › Conditional Formatting › Highlight Cells Rules › Duplicate Values.
3. Choose a format and click OK.

Every value that appears more than once is coloured, including the first copy. That makes it good for spotting problems but not for deciding which rows to delete, and it works one column at a time rather than on whole rows.

Mark only the extra copies with COUNTIF

A helper column gives you more control. Say your emails are in column A, starting in row 2. In an empty column, enter this in row 2 and fill it down:

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

The range grows as the formula goes down, so each row only counts the values above it and itself. The first copy shows FALSE and every later copy shows TRUE.

For duplicates across several columns, use COUNTIFS with one pair of arguments per column:

=COUNTIFS($A$2:A2,A2,$B$2:B2,B2)>1

Or build a key that joins the columns, such as =A2&"|"&B2 in a helper column, and run the COUNTIF formula on that key. The | separator stops “ab” + “c” matching “a” + “bc”.

Filter and delete the marked rows

1. Click inside your data and turn on filters with Data › Sort & Filter › Filter.
2. Filter the helper column to show only TRUE.
3. Select the visible rows, right-click and choose Delete Row.
4. Clear the filter and delete the helper column.

This takes longer than Method 1, but you look at every row before it is removed.

Method 3: Advanced Filter to a new location

Advanced Filter copies the unique rows somewhere else and leaves your original data exactly as it was. It works in every desktop version of Excel.

1. Click inside your data.
2. Go to Data › Sort & Filter › Advanced.
3. Choose Copy to another location.
4. Check that List range covers your data, including the header row.
5. In Copy to, click a cell where the result should start, such as an empty area to the right or a cell on another sheet.
6. Tick Unique records only and click OK.

Advanced Filter treats a row as a duplicate only when every selected column matches. To de-duplicate on a key column alone, select just that column as the list range, though you will then only get that column back. For key-column duplicates with full rows, Methods 1 or 2 are a better fit.

Method 4: The UNIQUE function

In Excel for Microsoft 365 and Excel 2021 or later, the UNIQUE function returns a de-duplicated copy of a range as a formula.

1. Click an empty cell with plenty of room below and to the right.

2. Enter a formula such as:

   =UNIQUE(A2:D100)
   

3. Press Enter. The unique rows “spill” into the cells below and across.

Because it is a formula, the result updates automatically when the source data changes. That is useful for a list you keep adding to, such as a running sign-up sheet.

A few details worth knowing:

• =UNIQUE(A2:D100,,TRUE) does something different: the third argument, exactly_once, returns only the rows that appear exactly once. Any value that was ever duplicated disappears completely, including its first copy. It is handy for finding one-off entries, but it is not a way to remove duplicates.
• If something is in the way of the spill area, you will see a #SPILL! error. Clear the blocking cells and it fills in.
• To keep the result as plain values, select it, copy, then use Home › Paste › Paste Values. You can then sort, edit or delete the source without affecting it.
• Like Remove Duplicates, UNIQUE is not case-sensitive.

Which method should you use?

If you are not sure, start with a copy of the sheet and Method 1. If you need to check rows first, use Method 2.

Why didn’t Excel remove my duplicates?

You can see two rows that look identical, but Excel kept both. Almost always, the cells are not truly the same. Here are the usual causes.

Extra or trailing spaces. “Priya Shah” and “Priya Shah “ with a space at the end are different values to Excel, and the space is invisible in the cell. Double spaces between words cause the same problem. The TRIM function removes them in a helper column, or the remove extra spaces tool cleans a whole file at once.

Non-breaking spaces from web copies. Text copied from a website or email often contains non-breaking spaces (character 160). They look like normal spaces, but TRIM does not remove them. Use Home › Find & Select › Replace, type Ctrl+Shift+Space or Alt+0160 on the numeric keypad in the Find box, and replace with a normal space or nothing.

Different capitalisation. This is usually not the problem, because Excel’s Remove Duplicates ignores case. But if you need rows to look consistent afterwards, or another system you import into is case-sensitive, tidy the text with the change case tool or the PROPER, UPPER and LOWER functions.

Numbers stored as text. An ID typed as 1024 and the same ID imported as the text “1024” may not match. Text numbers are often left-aligned and show a small green triangle. Convert them all to the same type first, for example with the convert text to numbers tool. Be careful with codes such as 000123, where the leading zeros matter and the value should stay as text.

Hidden characters. Line breaks inside a cell, tabs or invisible control characters from exported systems make values differ. The CLEAN function removes many of them, and the remove special characters tool handles the rest.

Dates with a time. Two cells can both display 10/03/2026 while one holds midnight and the other 14:35. Excel compares the full value, so they are different. Widen the number format to show the time, or strip it with =INT(A2) in a helper column before you compare.

The range was too small. If Excel selected only part of your data, rows outside it were never checked. Select the full range yourself and run the command again.

Or remove duplicates online without Excel

If you do not have Excel, or you want more control than the built-in command, the free remove duplicates tool on SheetTidy works on .xlsx, .xls and .csv files directly in your browser. The file is processed on your own device and is never uploaded.

What it does:

• Compares whole rows or the columns you choose, such as Email or Customer ID.
• Ignores case and extra spaces by default, so the trailing-space problem above does not stop a match. You can turn either setting off if capitals or spacing matter in your data.
• Keeps the first or the last copy. Keeping the last copy is useful when newer records are added at the bottom.
• Can mark instead of delete. It adds a Duplicate column with notes such as “Duplicate of row 4”, so you can review the rows in Excel.
• Never treats empty rows as duplicates. If the compared cells are all empty, the row is kept, because two missing email addresses are not the same customer.
• Shows a preview first with the number of rows that will be removed, and lets you download the removed rows as a separate file for your records.
• Keeps your format. A CSV comes back as CSV, and Excel files come back as .xlsx.

For the best results, clean the text before you de-duplicate. Run the file through remove extra spaces and change case first, then remove duplicates. Afterwards, compare the two Excel files to see exactly which rows changed between the original and the cleaned version.