Skip to content

# How to Remove Duplicates in Excel?

• Last Updated : 13 Apr, 2021

In this article, we will learn how to remove duplicates in Excel. Duplicates are set of values that repeat in our data. We can remove them using a couple of ways which are following:

1. Using Remove Duplicates Option on Data Tab
2. Using the Advanced Filter Option
3. Using Formulas
4. Using the Power Query Tool to Remove Duplicates in Excel

### 1. Using Remove Duplicates Option on Data Tab:

To remove duplicate entries from our data table using the remove duplicates option on the Data tab we have to follow some step which is following:

• Select all the data:

• Go to Data tab and click on the Remove Duplicates option:

• Then a pop-up window will open and select the columns in which we want to remove duplicates and then click on OK:

• And all the duplicate are removed:

### 2. Using the Advanced Filter Option:

To remove duplicate entries from our data table using the Advanced Filter Option on the Data tab we have to follow some step which is following:

• Select all the data:

• Go to the Data tab and click on the Advanced filter option:

• A pop-up window will appear on the window and we have to check on Unique records only and click on OK:

• And all the duplicates are removed:

### 3.Using Formulas to Remove Duplicates in Excel:

To remove duplicate entries from our data table using formulas we have to first make a new column name combine to combine all the columns of our data. And then we apply the following steps:

• To combine all the columns we use the combine operator &
`=A2 & B2`

• Make another column name count to count the no of duplicates of this entry using the COUNTIF function that takes the criteria and the cell that duplicates we want to count here C\$2:C6 shows the range of the data in which we want to find duplicate and C2 is the cell that duplicates we want to count:
`=COUNTIFS(C\$2:C6,C2)`

• Now we have the number of duplicate values, so we can apply Method 2 Using Remove Duplicates Option on Data Tab and remove duplicates. Follow the below steps:
• Go to the Data tab and click on the Remove Duplicate option:

• A pop-up window will appear. Click on remove duplicate:

• Another pop-up window will appear click on ok:

• And all duplicates are removed:

### 4. Using the Power Query Tool:

To remove duplicate entries from our data table using Power Query Tool we have to follow some step which is following: And then we apply the following steps:

• Go to Data tab and click on From Table option:

• A pop-up window will appear then check on My table has headers and click on OK:

• Then the Power Query Editor opens:

• Go to the Home tab and click on Remove rows and in this option click on Remove Duplicates:

• And all the duplicated values are deleted:

My Personal Notes arrow_drop_up