How to Remove Duplicates in Excel: 5 Easy Ways

By MagicTextTools Team · November 22, 2026

What Counts as a Duplicate

Learning how to remove duplicates in excel starts with knowing what Excel counts as a duplicate. A duplicate is a row where every value matches every value in another row. Excel compares the value shown in the cell, not the value stored behind it. A date shown as 3/8/2006 in one row and Mar 8, 2006 in another counts as two different values, even though both dates are the same day. If two cells hold different formulas but show the same result, they still count as duplicates.

The columns you pick decide what counts as a duplicate. Tick every column and only full rows that match exactly are duplicates. Tick just the email column and every row with that email counts, even if the names or dates differ.

Before you delete anything, copy your data to another sheet or workbook. Removing duplicates deletes rows for good. You can undo with Ctrl+Z right after, but once you close the file there is no way back.

Look First: Highlight Duplicates

Microsoft suggests you look at the duplicates before you delete them. Highlighting changes nothing, so it is a safe first step.

  1. Select the cells you want to check.
  2. Go to the Home tab, open Conditional Formatting in the Styles group.
  3. Point to Highlight Cells Rules, then click Duplicate Values.
  4. Pick a color and click OK.

Every value that appears more than once lights up. Scan the list. If rows you expected to keep are lit up, check the range first, then delete.

Method 1: The Remove Duplicates Command

This is the fastest way, and the one most people need. It deletes the repeat rows and keeps the first copy of each value.

  1. Click any cell inside your data, or select the range yourself.
  2. Go to the Data tab and click Remove Duplicates in the Data Tools group.
  3. In the dialog, tick the columns Excel should compare. Select All ticks every column, and Unselect All clears them.
  4. Click OK. Excel tells you how many duplicate values it removed and how many unique values remain. Click OK again to close the message.

The first occurrence stays and every later identical row is deleted. Only the cells in the range you picked are affected. Cells outside the range do not move or change.

Two limits are worth knowing. You cannot run this command on data that is outlined or that has subtotals. Remove the outline and the subtotals first. Also, the count Excel reports can include empty cells and spaces, so scan the result after.

Method 2: The UNIQUE Function

If you want a clean list but you want to keep the original data, use a formula. The UNIQUE function builds a list of unique values in new cells and leaves the source alone.

Type this in an empty cell:

=UNIQUE(A2:A100)

Excel spills the unique values down from that cell, and the list updates when the source changes. Wrap it in SORT to get the list in order:

=SORT(UNIQUE(A2:A100))

UNIQUE works in Excel for Microsoft 365, Excel 2021 and later, and Excel for the web. Older versions do not have it, so use the command or Advanced Filter instead.

The function has two extra settings: one compares columns instead of rows, and one set to TRUE returns only values that appear exactly once. For most lists, the plain version is enough.

Method 3: Advanced Filter

Advanced Filter gives you a clean copy and leaves the original untouched. It can also hide duplicates in place without deleting anything.

  1. Select your range, including the heading row.
  2. Go to Data, then click Advanced in the Sort and Filter group.
  3. Pick Filter the list, in-place to hide duplicates where they are, or Copy to another location to put the clean list in new cells. If you copy, type the first cell of the new location in the Copy to box.
  4. Tick Unique records only and click OK.

Filtering only hides duplicates for now. Nothing is deleted, so you can clear the filter and get every row back. The copy option is handy when you need a clean list next to the original.

Method 4: Power Query

Power Query suits lists you clean again and again, like a weekly export. Set the steps once, and every refresh repeats them.

  1. Select your data and go to Data, then From Table/Range. Click OK to open the Power Query Editor.
  2. Select the column or columns that define a duplicate.
  3. Go to Home, then Remove Rows, then Remove Duplicates.
  4. Click Close and Load to put the clean table back in your sheet.

When the source data changes, refresh the query and the same cleanup runs again. This is the best choice for reports you rebuild on a schedule.

Method 5: A COUNTIF Helper Column

A helper column flags duplicates so you can review them before you delete anything. It works in every version.

  1. In the first empty column next to your data, enter this formula in row 2 (change A to your column letter):

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

  1. Fill the formula down to the last row.
  2. The first time a value appears you get FALSE. Every later copy gets TRUE.
  3. Filter the helper column for TRUE, review those rows, and delete the ones that really are duplicates.

This method is slower than the command, but it shows you exactly which rows will go. Use it when a wrong delete would cost you.

Which Method Should You Pick

  • Need it gone fast and you have a copy saved: the Remove Duplicates command.
  • Need the original untouched and a list that updates: the UNIQUE function.
  • Need a clean copy next to the original: Advanced Filter.
  • Clean the same export every week: Power Query.
  • Need to check each duplicate by eye first: the COUNTIF helper column.

If you remember one rule, make it this: look first, delete second. Highlight or flag the duplicates, check the result, and only then remove them.

Common Mistakes

Deleting without a copy. The command deletes rows for good. Save a copy on another sheet first.

Ticking the wrong columns. Tick every column and only exact full rows go. Tick one column and rows that share that value go too, even if the rest of the row differs.

Trusting the display. Excel compares the value shown in the cell. A date shown two ways, or a number with extra spaces, can count as different values.

Using UNIQUE in an old version. The function needs Microsoft 365, Excel 2021 or later, or Excel for the web. In Excel 2019 or 2016 it does not exist.

Forgetting the filter. Advanced Filter hides duplicates, it does not delete them. If you need them gone, use the command or delete the flagged rows by hand.

Frequently Asked Questions

Can I undo removing duplicates?

Yes, press Ctrl+Z right after, or click Undo. Once you close the file, the deletion is final, so keep a copy of the original data.

Does the command keep the first or the last copy?

It keeps the first occurrence and deletes every later identical row. To keep the latest row instead, sort by date from newest to oldest first, then run the command.

Why does Excel say some duplicates are still there?

Excel compares the value shown in the cell. Extra spaces, different date formats, or numbers stored as text can make two cells look the same to you but different to Excel. Clean them up first.

Does Advanced Filter delete duplicates?

No. It hides them in place or copies a clean list to a new location. Your original data stays as it is.

Can I check just one column for duplicates?

Yes. In the Remove Duplicates dialog, tick only that column. Every row that shares a value in that column counts as a duplicate, even if the other columns differ.

Faster Alternative: Clean Up Text Lists Online

If your list is plain text and not a spreadsheet, you do not need Excel at all. Our free online Remove Duplicate Lines tool deletes repeat lines from pasted text while keeping the first occurrence in order. Paste the list, pick case sensitive or case insensitive matching, and copy the clean result. There is no signup and nothing is sent to a server.

Related tools

← Back to blog