Open In App

How to Remove Duplicates in Excel? 4 Quick & Easy Methods

Improve
Improve
Like Article
Like
Save
Share
Report

When it comes to Business analysis or data analysis, having organized and meaningful data plays an important role. But when you have a lot of data, you might end up with duplicate entries. These extra copies can make your data messy and hard to use. Microsoft Excel is a widely used tool for performing data-related queries. If you are someone who keeps struggling with duplicate values in the data then this article is for you.

In this article, we will learn different ways to remove duplicates in Excel. We can remove them using four easy methods that are explained below ranging from the easy “Remove Duplicates” button to using Excel codes, known as VBA.

How to Find Duplicates in Excel

If you just want to check the duplicate values in your data. You can do that by just highlighting the values and can later decide if you wish to keep the values or delete those duplicates.

To find duplicate values follow the below-mentioned steps:

Step 1: Select the Data

Open the Excel spreadsheet from which you want to check the duplicate values and Select the data.

remove duplicates in excel

Step 2: Go to Home Tab and Select Conditional Formatting and Click on Duplicate values

Go to the Data tab and select the option of Conditional Formatting. A drop down menu will appear, from the given choices in the menu select click on Highlight Cell Rules. Further drop down will appear. From that drop down options select the option of Duplicate values.

remove duplicates in excel

Step 3: Click on Format Cells that Contain and Select the Color Scheme 

As you select the Duplicate Values option, a conditional formatting window will appear on the screen. In the window, go to the Format cells that contain option, click on given values with and a drop down will appear. From the drop down list select the colour scheme you wish to highlight and press Done.

remove duplicates in excel

Step 4: Preview the Highlighted Duplicate Values

Now you can check that the duplicate values in the data are highlighted with the selected colour.

remove duplicates in excel

How to Remove Duplicates in Excel

Those values that repeat in the data are termed Duplicate values which are basically extra copies of the data. To remove extra copies of the values you can follow any of the given below approach.

How to Remove Duplicates Using the Data Tab 

To remove duplicate entries from our data table using the Remove Duplicates option on the Data tab, we have to follow some steps which are as follows:

Step 1: Select the Data

Open the Excel spreadsheet from which you want to remove the duplicate values and Select the data having duplicates.

remove duplicates in excel

Step 2: Go to Data Tab and Select Remove Duplicates

Go to Data Tab given at the toolbar and Select the Remove Duplicates option.

remove duplicates in excel formula

Step 3: Select Column

In the displayed pop-up window, select the columns from which we want to remove duplicates and then click on Ok Button. Here, we have selected columns Name and Marks.

remove duplicates option in excelduplicates in excel

Step 4: Preview the Removed Duplicates

All the duplicates will be removed. You can Check in the sheet.

excel duplicate remove

How to Remove Duplicates 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 steps:

Step 1: Select the Data

Open the excel sheet and Select all the data from which duplicates needs to be removed.

advanced filter excel

Step 2: Go to Data Tab and Select Advanced Filter

Access the Data Tab and under the sort and filter section select the Advanced filter option.

advanced filter excel duplicates

Step 3: Apply Check on Unique Records Only

A pop-up window will appear on the screen, and we have to tick mark or check on Unique records only option given and press Ok Button.

how to remove duplicates in excel

Step 4: Preview the Removed Duplicates

All the duplicates are removed.

how to remove duplicates in excel shortcut

How to Remove Duplicates Using Formulas

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

Step 1: Use the Combine Operator

To combine all the columns, we use the combine operator &

=A2 & B2 

remove duplicates in excel formula

Step 2: Make Another Column and Use Countif Function

Make another column named COUNT to count the number 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 duplicates and C2 is the cell that duplicates we want to count

=COUNTIFS(C$2:C6,C2)

formula to remove duplicates in excel

Step 3: Remove Duplicates Using Data Tab

Now we have the number of duplicate values, so we can apply above discussed approach to remove duplicates. We are removing Duplicates using Remove Duplicates Option on Data Tab .

Go to the Data Tab and click on the Remove Duplicates option.

excel remove duplicates formula

A pop-up window will appear. Click on Remove Duplicates

excel remove duplicates

Another pop-up window will appear, click on OK.

shortcut to remove duplicates in excel

All the duplicates will be removed.

output dataset without duplicates

How to Remove Duplicates Using the Power Query Tool

 To remove duplicate entries from our data table using Power Query Tool we apply the following steps:

Step 1: Access the Data Tab and Select From Table

Go to Data tab given at the top and under Get & Transform section select From Table option.

power query tool in excel

Step 2: Check My table Has headers

A pop-up window will appear on the screen. In the window, tick mark the check on My table has headers and click on Ok button.

creating table

Step 3: Power Query Editor Displayed

Now, the Power Query Editor opens on the screen.

Power Query Editor

Step 4: Go to the Home tab and Select Remove Duplicates

Go to the Home tab and click on Remove Rows and in this option click on Remove Duplicates.

removing duplicates

Step 5: Preview the Removed Duplicate Values

Check all the duplicated values are deleted.

output with deleted duplicate values

Conclusion

From the above article we can easily conclude that removing duplicates using different ways is very helpful to reduce time consumption and improve results quality. You can easily find and then remove duplicates by different ways discussed above like using formulas, using conditional formatting, etc.

How to Remove Duplicates in Excel – FAQs

Can Excel automatically find duplicates?

In Excel, there is a predefined rule for highlighting duplicate cells. To apply this rule, follow the below-mentioned steps:

  • Select the data you want to check for duplicates.
  • On the Home tab, in the Styles group, click Conditional Formatting > Highlight Cells Rules > Duplicate Value

What is the shortcut for remove duplicates?

Shortcut : To access the Remove Duplicates command on the Data tab on the Ribbon:

Press Alt > then press A > then press M .

What is the shortcut for duplicate formula in Excel?

To duplicate your formula into each cell you can use the shortcut keys: CTRL+D .

How can I delete duplicate rows in Excel but keep one?

Use the “Remove Duplicates” feature in Excel: Go to the Data tab, click “Remove Duplicates“, select the columns to check, and click OK. This keeps one instance of each duplicate row and removes the rest.

Why am I not able to remove duplicates in Excel?

Common reasons include:

  • not selecting the correct range
  • the data has merged cells
  • the column headers are missing/duplicated

Make sure your selection is correct, unmerge the cells, and check headers.



Last Updated : 27 Mar, 2024
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads