Open In App
Related Articles

How to Remove Duplicates in Excel (Different Ways)

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Report issue
Report

When it comes to Business analysis or data analysis, having organized and meaningful data plays an important role. Redundancy in data makes the job more difficult however it can be helpful sometimes to have duplicate data but removing duplicates from the data leads to more effective analysis and accurate results. 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.

How to Find Duplicates in Excel

If you just want to check the duplicate values in your data. You can also 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 drf op down options select the option of Duplicate values.

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.

Step 4: Preview the Highlighted Duplicate Values

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

How to Remove Duplicates in Excel

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

  1. Remove Duplicates Using the Data Tab
  2. Remove Duplicates Using the Advanced Filter
  3. Remove Duplicates Using Formulas
  4. Remove Duplicates Using the Power Query Tool

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 andunder the sort & 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, or 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 combine 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 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 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 Duplicate option

excel remove duplicates formula

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

excel remove duplicates

Another pop-up window will appear, click on ok

shortcut to remove duplicates in excel

And all duplicates are 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 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 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.

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 > A > 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



Last Updated : 18 Oct, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads