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
- Use the Remove Duplicates command for quick, one‑click cleanup.
- Apply Conditional Formatting first to see what will be deleted.
- Formulas like
UNIQUE()let you keep the original data intact. - Power Query offers a repeatable workflow for large datasets.
- Always back up your data before permanently deleting rows.
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
- Click any cell in the column you want to clean.
- Go to Data → Remove Duplicates.
- In the dialog, uncheck all columns except the one you selected.
- 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
- Select the entire table (or the range covering all relevant columns).
- Open Data → Remove Duplicates.
- Check the boxes for each column that must match for a row to be considered a duplicate.
- 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
- Deleting without a backup: The Remove Duplicates feature permanently erases rows. Copy the sheet first.
- Choosing the wrong columns: If you omit a column that differentiates records, you may lose unique data.
- Not checking for hidden rows: Filters can hide rows that still count as duplicates. Clear filters before cleaning.
- Using the command on a PivotTable values area: Excel cannot highlight duplicates there; extract the source data first.
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 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
- Trim whitespace: Use the
TRIM()function on your text columns. Create a helper column with=TRIM(A2), copy the results, and paste them back over the original data as values. This removes leading, trailing, and multiple internal spaces. - Check for non-breaking spaces: Sometimes copy-pasting from web pages introduces non-breaking spaces (ASCII 160) that
TRIMdoesn’t remove. IfTRIMfails, useSUBSTITUTE(A2, CHAR(160), " ")to replace them with standard spaces, then trim again. - Standardize data types: If one column has numbers stored as text and another has true numbers, convert them. You can use the
VALUE()function to force text to numbers, or multiply the cell by 1 in a helper column. Ensure all cells in the duplicate-check column share the same underlying type.
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
- Use in-place removal when: Your data is final, you need to reduce file size, or you are preparing data for a system that rejects duplicate keys (like a database import).
- Use extraction when: You are analyzing patterns (e.g., ‘how many duplicates are there?’), you need to keep the original audit trail, or you are working with dynamic data that refreshes automatically.
- Use Power Query when: You need to repeat this process monthly on new files. Power Query lets you save the steps, so next month you just hit ‘Refresh’ and the duplicates are removed automatically without re-running manual steps.
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.
