Excel Blog Series

Removing duplicates in Excel is crucial for ensuring the accuracy and reliability of your data. Excel offers built-in features that make identifying and eliminating repetitive entries easy, allowing you to maintain clean datasets and enhance your analysis. Below are multiple methods to find and remove duplicates effectively.

Method 1: Using the Built-In Remove Duplicates Feature

How to remove duplicate values in Excel? Follow these steps and make your job easy:

1. Open Your Excel Workbook

Start by opening the Excel file containing the data you want to clean.

2. Select Your Data Range

Select the range of cells containing your data by clicking and dragging. If your data is in a table, you can simply select any single cell within the table. Include headers in your selection if applicable.

3. Access the Remove Duplicates Feature

Navigate to the Data tab on the Ribbon.
In the Data Tools group, click on Remove Duplicates.

4. Choose Columns for Duplicate Criteria

A dialog box will appear listing all the columns in your data range.
If your data includes headers, check the "My data has headers" box.
To remove duplicates based on all columns, ensure all columns are checked. If you only want to consider specific columns, uncheck the irrelevant ones.

5. Remove Duplicates

After selecting your criteria, click OK.
Excel will process the data and display a message indicating how many duplicates were removed and how many unique values remain.

6. Review Your Data

Once duplicates are removed, review the cleaned data to ensure correctness.

Using the Built-In Remove Duplicates Feature in Excel

Method 2: Highlight Duplicates Using Conditional Formatting

Before removing duplicates, you can visually highlight them to confirm what will be deleted:

1. Select Your Data

Highlight the range of cells where you want to find duplicates.

2. Apply Conditional Formatting

Go to the Home tab on the Ribbon.
In the Styles group, click on Conditional Formatting.
Select Highlight Cells Rules, then choose Duplicate Values.

3. Review Highlighted Duplicates

Excel will highlight duplicate values, allowing you to review them before removing duplicates. This method is particularly useful for visual confirmation before deletion.

Highlight Duplicates Using Conditional Formatting in Excel

Method 3: Removing Duplicates Using a Formula

If you prefer using formulas, the following approaches can be helpful in identifying duplicates:

1. Use the COUNTIF Function

To flag duplicates in a column, use this formula:
=COUNTIF($A$1:$A$100,A1)>1
This formula will return TRUE for duplicates and FALSE for unique values. You can then filter and remove the TRUE values.

2. Use the UNIQUE Function (Excel 365 and Excel 2021)

For versions supporting dynamic arrays, the UNIQUE function can create a list of unique values:
=UNIQUE(A1:A100)
This function outputs a list of unique entries, eliminating duplicates automatically.

Additional Tips for Removing Duplicates

Backup Your Data: Always make a copy of your original data before removing duplicates to avoid accidental data loss.
Understand Excel's Duplicate Logic: Excel considers a row a duplicate if all selected columns match another row exactly. Partial matches will not be flagged as duplicates.
Check for Case Sensitivity: By default, Excel is not case-sensitive when identifying duplicates (e.g., "Apple" and "apple" are considered the same).

By using these methods, you can efficiently remove duplicates, improving the quality of your datasets and enhancing the accuracy of your data analysis.

Quiz Time!!!

Click here to view quiz

Frequently Asked Questions

Q1. How can I find duplicates in Excel?

To find duplicates in Excel, select your data range, go to the "Data" tab, and click "Remove Duplicates." This tool highlights duplicate entries, allowing for easy identification.

Q2. What is the quickest way to remove duplicate values?

The quickest way is to use the "Remove Duplicates" feature under the "Data" tab. Select your data range and click this option. Excel will automatically delete duplicate entries.

Q3. Can I filter unique values instead of removing duplicates?

Yes, you can filter unique values by selecting your data range, then going to "Data," clicking on "Advanced," and choosing "Unique records only." This will display only unique entries without altering the original data.

Q4. What should I do if Excel doesn't remove duplicates as expected?

If duplicates are not being removed, ensure that there are no leading or trailing spaces in your data. Use the TRIM function to clean up your entries before attempting to remove duplicates again.

Q5. Is it possible to keep one instance of each duplicate?

Yes, when using the "Remove Duplicates" feature, Excel retains one instance of each duplicate while deleting others. You can specify which columns to check for duplicates during this process.

Q6. Does removing duplicates affect formulas linked to my data?

Removing duplicates will not directly affect formulas unless they reference the deleted cells. Review any dependent formulas after removing duplicates to ensure accuracy.

Q7. Can I undo the removal of duplicates in Excel?

Yes, you can undo the removal of duplicates immediately by pressing Ctrl + Z. However, if you save your file afterward, the action cannot be reversed. Always consider creating a backup before making changes.

Suggested Reads:

Edited by

Shreeya Thakur

Sr. Associate Content Writer at Unstop

I am a biotechnologist-turned-content writer and try to add an element of science in my writings wherever possible. Apart from writing, I like to cook, read and travel.