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.
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.
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 which are basically extra copies of the data. To remove extra copies of the values you can follow any of the given below approach.
Table of Content
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.
Step 2: Go to Data Tab and Select Remove Duplicates
Go to Data Tab given at the toolbar and Select the Remove Duplicates option.
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.
Step 4: Preview the Removed Duplicates
All the duplicates will be removed. You can Check in the sheet.
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.
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.
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.
Step 4: Preview the Removed Duplicates
All the duplicates are removed.
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 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)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.
A pop-up window will appear. Click on Remove Duplicates
Another pop-up window will appear, click on OK.
All the duplicates will be removed.
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.
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.
Step 3: Power Query Editor Displayed
Now, the Power Query Editor opens on the screen.
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.
Step 5: Preview the Removed Duplicate Values
Check all the duplicated values are deleted.
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.