You are facing a long Excel table full of contacts or product references that repeat, and you want to clean it all up in a few clicks? We’ve all experienced this moment: you download a list, import it, and bam, duplicates! In this article, I detail several ways to remove these rows in a flash, whether you are using Excel 2010, 2016, or 365. You will see that by combining native functions, formulas, and even Power Query, you can save a ton of time and avoid manual errors.
1. The Built-in « Remove Duplicates » Feature
This is probably the fastest way: from the Data tab, click on Remove Duplicates. You select the column(s) to check, confirm, and Excel automatically removes the rows where all values match.
When to use it?
If you are working with a small volume (a few hundred or a few thousand rows) and you do not need to keep a record of the deleted values, this is ideal. However, be careful with case sensitivity (uppercase/lowercase) and invisible spaces: « Dupont » and « dupont » will be considered different.
Detailed steps
- Select your table (or a single column if needed).
- Data tab → Remove Duplicates.
- Check the columns to consider.
- Confirm and then observe the confirmation message (number of duplicates removed).
2. The Advanced Filter to Extract a Unique List
The Advanced Filter can be useful if you want to keep both the complete list and the cleaned list side by side, or if you prefer not to modify your original table directly.
How to set it up?
1) In the Data tab, choose Filter > Advanced Filter.
2) Select your data range as the list to filter.
3) Check « Copy to another location » and specify a destination cell.
4) Check « Unique records only » and confirm.
Advantages and use cases
You get a second, clean list without touching the source. It even works on non-contiguous columns and preserves the original order if you want. However, you will need to repeat the procedure manually each time the table is updated.
3. Formulas to Identify and Extract Unique Values
In Excel 365, the UNIQUE function is a real asset: it creates a dynamic list without duplicates in real time. If you don’t have this version, you can mark duplicates with formulas based on COUNTIF and COUNTIFS then filter manually.
Example with UNIQUE
In an empty cell, type =UNIQUE(A2:A100) and confirm. Excel immediately returns a list free of repetitions. If you add a new entry in A2:A100, the UNIQUE list updates itself automatically.
Marking duplicates otherwise
Without UNIQUE, you can use:
=IF(COUNTIF($A$2:A2, A2)>1, “Duplicate”, “Unique”)
Then, simply filter on « Duplicate » and delete the filtered rows.
4. Power Query: the solution for large volumes
When dealing with tens of thousands of rows, Power Query becomes essential. This toolset integrated into Excel (since 2016) very easily handles data cleaning and transformation.
Import the table into Power Query
1) Select your table or range.
2) Data tab → Get Data → From Table/Range.
3) The Power Query window opens, displays your data, and offers a “Remove Duplicates” field in the Home tab.
Apply duplicate removal
Click on Remove Duplicates: you can choose one or more columns to consider. Finally, close and apply to send the cleaned table back to Excel. The query can be refreshed at any time if the source changes.
5. Additional tips and best practices
- Check and clean invisible spaces with the TRIM function or by combining text functions (LEFT, RIGHT, MID).
- Use conditional formatting to highlight duplicates before deleting them, to visually verify.
- Consider case sensitivity: converting to lowercase (LOWER) or uppercase (UPPER) standardizes your data and avoids phantom duplicates.
- To cross-reference multiple tables and detect repetitions across different sheets, the VLOOKUP function can be useful.
- If you are comfortable with code, a VBA macro can fully automate the removal in a few lines (perfect to save even more time).
| Method | Advantages | Limitations |
|---|---|---|
| Remove Duplicates | Fast, no formula | Sensitive to spaces and case |
| Advanced Filter | Copy without touching source | Manual procedure at each update |
| UNIQUE (formula) | Dynamic, auto update | Excel 365 only |
| Power Query | Scalable, massive processing | Longer learning curve |
Conclusion
In the end, the choice of method depends on the volume, your Excel version, and your automation needs. For a few rows, the Remove Duplicates command is enough. As data becomes more complex, turn to Power Query or dynamic formulas. And don’t forget: a little preliminary cleaning of spaces and case will save you many surprises.
FAQ
- How to undo duplicate removal if I made a mistake?
- Simply use the Ctrl+Z shortcut immediately after the operation, or restore the previous version via the file history.
- Does the UNIQUE function exist in all Excel versions?
- No, UNIQUE is part of the dynamic array functions, available starting with Excel 365 and Excel 2021.
- Can I combine Advanced Filter and Power Query?
- Rather than filtering manually, import your filtered table into Power Query to automate it in the future.
- Are duplicates always identified the same way?
- Be careful: Excel does not handle case and spaces the same way depending on the method. Don’t hesitate to standardize your cells with LOWER, TRIM, or UPPER.