How to Remove Duplicates in Excel: 3 Quick Methods for Clean and Accurate Data

Hello Data Lunatics, if you have data containing duplicate values in Excel and want to know how quickly to remove duplicate values then you have landed at the right post. You will find different methods of removing duplicates from your Excel data. This is a step-by-step tutorial which can definitely help you to clean the data. You can apply any of these methods to eliminate duplicate data. So, let’s see those 3 Quick Methods together.

How to Use the ‘Remove Duplicates’ Tool in Excel

We have taken an example as shown below, where we have duplicate entries.

We can see we have duplicate entries in the above table, I have highlighted it in a box to spot the values for us and now we want to delete the duplicate entries. Let’s follow the below steps:

First, Select the data. You can select all the tables or only a specific range from which you want to remove the duplicates. In our case, I have selected all the data.
Now Go to the “Data” tab and Click on the “Remove Duplicates” option.

Once you clicked, the dialog box will appear. By default, all columns are checked but you can uncheck specific column names if you want to remove duplicates from specific columns only.
I have kept the dialog box as it is as I want to remove duplicates from all the columns.

Now click “OK”, you can now see the duplicate entries are removed from the data.

How to Highlight and Remove Duplicates with Conditional Formatting in Excel

Here we are in the second method which is Conditional Formatting. This method can help to highlight the duplicate values in your Excel data. Let’s see how this method can help you to remove the duplicates. This method is only useful when you have small data and want to delete the entries carefully.

Select the data. You can select a specific column/range to highlight the data. I have selected all the data.
Now, go to the “Home” menu, and click on the “Conditional Formatting” option. Then click on the very first option which is “Highlight Cells Rules” and then click on “Duplicate Values” as shown below.

Now you will see the duplicate values are highlighted.

After identifying the duplicate values in the data, we can now delete the duplicate values manually.

Using the COUNTIF Formula to Identify and Remove Duplicates in Excel

Now, let’s explore this third method which is an alternative method to remove duplicates from Excel data. We have taken the below data as an example where I have already done conditional formatting to highlight the duplicate values.

Now, from the above data, you want to delete city names that are duplicates. We will add another helper column to count duplicate cities and enter the Countif() function to count the repetition of the value as shown below. Please ensure to freeze the first cell from the Range criteria.

Now. Apply the filter to the Helper column and select a count of more than 1.

Now delete all the entries which are more than 1 and this will delete all the duplicate values. Clear the filter and you will find that duplicate values from Excel data are deleted.

Conclusion:

I Personally use the “Remove Duplicates” tool in excel more convenient than the other two but it’s always good to know all the possible options, Did these methods help you remove duplicates in Excel? Let us know in the comments, and share your favorite Excel tips! Thanks.