Comprehensive guide to remove duplicates in Excel

Duplicates can quickly pollute your Excel files, distort your calculations and complicate data analysis. Whether in a contact database, a sales table or a product list, detecting and deleting duplicates is essential to keep your files clean, reliable and usable.

In this guide, you’ll learn all the methods for removing duplicates in Excel, from the simplest to the most advanced, without losing important data. You’ll also discover practical tips for automating the process and avoiding common errors.

Why delete duplicates in Excel?

Duplicates can :

Artificially inflate your results (sales, volumes, contacts)
Create inconsistencies in your pivot tables
Generate errors in formulas such as SUM.IF or COUNTIF
slow down your file if the data is voluminous

By removing them, you’ll improve the quality of your analyses and gain in efficiency.

Method 1: Use the native “Delete duplicates” function

Excel offers a built-in tool for deleting duplicates in just a few clicks :

Steps to follow:

Select the range containing your data (e.g. A1:C100)
Go to the “Data” tab > click on “Delete duplicates”.
Tick the columns to be analyzed (one or more)
Click on OK: Excel deletes all identical rows in the selected columns.

Example:

Name	Email
Alice	alice@gmail.com
Alice	alice@gmail.com
Bob	bob@gmail.com

After deletion, only one line per duplicate remains.

Tip: remember to make a copy of your data before deleting.

Method 2: Remove duplicates with advanced filters

This method is useful if you want to extract a single list without deleting the original source.

Steps:

Select your column
Go to the “Data” tab > “Advanced” (in “Filter”)
Check “Extract to another location”.
Check the “Unique records only” box
Choose a target cell to display the result

This method keeps the basic data intact.

Method 3: Use a formula to identify duplicates

With a formula, you can mark duplicates without immediately deleting them.

Example with `NB.SI`:

=NB.SI(A:A; A2) > 1

This formula checks how many times the A2 value appears in the entire A column.
If the result is > 1, it’s a duplicate.

Add a “Duplicate?” column to your table and then filter the rows concerned.

Learn more: COUNTIF in Excel with examples

Method 4: Delete duplicates in a structured table

If your data is in an Excel table (Ctrl + T), you can also :

Click on a table cell
Go to Table Tools > Design
Then select “Delete duplicates”.

Excel automatically recognizes headers and guides you in the choice of columns to compare.

Delete partial duplicates (on a single column)

Sometimes you want to remove duplicates from a single column (e.g.: email), while keeping the rest of the data.

Here’s the safest way:

Sort the data (by name, date, etc.)
Use the formula NB.SI in a column to identify duplicates
Filter this column to keep only one occurrence
Manually delete identified duplicates

Practical example: Cleaning up a customer list

You have a table with the names and emails of your customers. Some of them are registered several times.

Objective:

Keep a single entry per email address.

How to avoid duplicates in the future

Here are a few best practices:

Use drop-down lists with data validation to limit errors:
-> How to create a drop-down list in Excel (step-by-step)
Apply conditional formulas to detect duplicates in real time
Centralize your data to avoid multiple copy-paste operations

Useful links for further reading

Conclusion

Deleting duplicates in Excel is an essential step in ensuring the quality of your data. Thanks to the various methods described in this guide – native tools, formulas, filters, tables – you can now adapt the solution to your needs.

Clean data is the basis of reliable analysis. By applying these techniques, you will not only improve the readability of your files, but also the performance of your tables and graphs.

Comprehensive guide to remove duplicates in Excel

Why delete duplicates in Excel?