how to remove duplicates in excel is a frequent question when you’re cleaning up a spreadsheet that has repeated rows or values. The fastest way is to use Excel’s built‑in Remove Duplicates command, but you also have formula‑based, filter‑based, and Power Query options if you need more control.

Key Takeaways

When should I use the Remove Duplicates command?

The command works best when you have a flat table and you want to delete exact duplicate rows. Select the range, go to Data → Remove Duplicates, and choose the columns that define a duplicate. Excel will keep the first instance and erase the rest.

How do I highlight duplicates before deleting them?

Conditional Formatting helps you review duplicates before they disappear. Select the range, then choose Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Pick a fill color, click OK, and Excel will color every duplicate entry. This visual cue lets you verify that you’re not removing needed data.

Step‑by‑step: Remove duplicates from a single column

  1. Click any cell in the column you want to clean.
  2. Go to Data → Remove Duplicates.
  3. In the dialog, uncheck all columns except the one you selected.
  4. Click OK. Excel reports how many duplicates were removed.

Tip: If you only need a list of unique values, consider the UNIQUE() function instead of deleting rows.

Step‑by‑step: Remove duplicates across multiple columns

  1. Select the entire table (or the range covering all relevant columns).
  2. Open Data → Remove Duplicates.
  3. Check the boxes for each column that must match for a row to be considered a duplicate.
  4. Confirm with OK. Excel keeps the first row where the combination appears.

This method is ideal for records like “Employee ID + Name” where a duplicate in one column alone isn’t enough to delete the row.

How to remove duplicate rows from an Excel Table

If your data is already formatted as an Excel Table (Ctrl+T), the Remove Duplicates command works the same way, but the Table feature automatically expands the range if you add new rows later. This keeps the cleanup rule dynamic.

How to remove duplicate rows based on multiple columns

Sometimes you need to consider a subset of columns, such as “First Name” and “Email”. After selecting the range, open the Remove Duplicates dialog and tick only those columns. Excel will treat rows as duplicates only when both values match.

Using the UNIQUE function to extract a list without duplicates

The UNIQUE() function (available in Excel 365 and later) returns an array of distinct values without altering the source data. Example:

 =UNIQUE(A2:A100) 

Place this formula in a blank column, and Excel spills the unique list. This is a safe alternative when you want to preserve the original rows.

How to keep the first instance and delete the rest with a formula

If you prefer a formula‑driven approach, combine COUNTIF with a filter. In a helper column, enter:

 =IF(COUNTIF($A$2:A2, A2)=1, "Keep", "Delete") 

Copy down, then filter on “Delete” and delete those rows. This method works in older Excel versions that lack UNIQUE().

Removing duplicates with Filter and Advanced Filter

Advanced Filter lets you copy unique records to another location. Select your data, go to Data → Advanced, choose “Copy to another location”, check “Unique records only”, and specify the destination. The original data stays untouched.

Using Power Query to remove duplicates

Power Query (Get & Transform) is ideal for large datasets or recurring cleaning tasks. Click Data → Get Data → From Table/Range, then in the Power Query editor choose Home → Remove Rows → Remove Duplicates. When you close & load, the cleaned table appears in a new sheet, and you can refresh it whenever the source changes.

Common mistakes and how to avoid them

What to do next

1. Back up the workbook you’re about to edit.
2. Try the simple Remove Duplicates command on a test range.
3. If you need a repeatable process, set up a Power Query step and save the query for future use.

For more tips on streamlining spreadsheet workflows, check out the Excel efficiency resources on our site.

How to Remove Duplicates in Excel: 4 Easy Steps for Data

How to handle duplicates when your data spans multiple sheets

Most tutorials stop at a single worksheet, but real-world messy data often lives across tabs. If you have customer lists in Sheet1 and Sheet2, running Remove Duplicates on each sheet individually won’t catch cross-sheet duplicates. You need to consolidate first. The safest way is to copy all data into a new, blank worksheet.

Select the ranges on each source sheet, copy them, and paste as values into the new sheet. Then run the standard Remove Duplicates command on this consolidated range. This ensures you are comparing every record against every other record, not just within isolated groups.

A common mistake here is pasting with formulas intact. If your source sheets use formulas that reference other cells, pasting them into a new sheet might break the links or carry over hidden dependencies. Always use Paste Special > Values to flatten the data before deduplication. This also prevents issues where a formula returns a blank string, which Excel might treat differently than a true empty cell during the duplicate check.

Why your duplicate removal keeps leaving ‘ghost’ rows

If you remove duplicates and still see identical-looking rows, the problem is usually invisible characters or data type mismatches. Excel treats "Apple" and "Apple " (with a trailing space) as two different values. It also distinguishes between the number 100 and the text "100". These ‘ghost’ duplicates are the most common reason users think the tool is broken.

Fixing trailing spaces and hidden characters

After cleaning, re-run the duplicate removal. If the row count drops significantly more than expected, you’ve found the hidden mismatches. This step is critical for data merged from different sources, such as CSV exports from different systems.

Choosing between in-place removal and new-column extraction

You have two main goals when dealing with duplicates: cleaning your existing dataset or creating a clean reference list. These require different approaches. In-place removal (the Remove Duplicates command) permanently deletes rows. It is irreversible without an undo step, so always back up your data or work on a copy. This method suits when your dataset is the source of truth and you need it slimmed down for analysis or import.

Extraction methods, like the UNIQUE() function or Power Query’s ‘Remove Duplicates’ step, leave your original data untouched. They generate a new range or query output. This is safer for exploratory work or when you need to validate the duplicate logic before committing to deletion.

If you are unsure which rows will be flagged, use extraction first. Review the output list. If it looks correct, you can then apply the in-place removal or use the extracted list to filter your main data.

When to use which method

Don’t mix these approaches carelessly. If you extract a unique list and then try to use it as a lookup table for your original data, ensure the data types match exactly. A mismatch between text and numbers in the lookup key will result in #N/A errors or missed matches, even if the values look identical.

FAQ

Can I undo a Remove Duplicates action?

Yes. Press Ctrl+Z immediately after the operation to revert the changes, but only if you haven’t saved the workbook yet.

Does Remove Duplicates work on filtered data?

It operates on the visible cells only, so hidden rows stay untouched. Clear filters first if you want to clean the entire dataset.

Is there a way to flag duplicates without deleting them?

Use Conditional Formatting > Highlight Cells Rules > Duplicate Values. This colors duplicates while leaving the data intact.

Further reading