When you are working on a large number of data then you will encounter a few duplicate entries.
Sometimes these duplicate values are very useful but many times you want to remove the duplicate data. If you have 1000 data then it becomes quite difficult to find and remove the duplicate data.
Here we have provided the step-by-step process on how to find duplicates in excel.
Take a look and follow the process.
How to Find Duplicates in Excel
There are many ways to find duplicate items and values in excel. You might be thinking as to why should I apply any formula or method to find duplicate values as it is easy.
But it’s not about few data, you can apply a formula or method when you have lots of data.
The method or formula to find and remove the duplicate items make the process easier and saves your time. Here you can check three different processes.
1. Find Duplicates in Excel using Conditional Formatting
To find duplicate values in Excel, you can use conditional formatting excel formula, Vlookup, and Countif formula. After finding out the duplicate values, you can remove them if you want by using different methods that are described below.
- Choose the cells from where you want to find duplicate values.
- Then go to the home tab or ribbon.
- Find the conditional formatting icon available on the ribbon.
- Click on the conditional formatting drop-down arrow.
- Click on the “Highlight Cells Rules”.
- After this find another option that is Duplicate Values.
- Choose duplicate values.
- Another window will get open that comes with customization options.
- Choose a duplicate option from the drop-down list.
- Choose the color contrast as per your preference.
- Click OK.
- This will highlight all the duplicate values.
- Make sure that your data of duplicate values gets highlighted.
Remove duplicate values
- Select all the duplicate cells or highlighted cells
- Delete the values by pressing the Delete button.
- After deleting the values, go to the conditional formatting.
- Choose Clear rules.
- And then choose clear rules from an entire sheet.
Another way to remove the duplicate value
This method will delete all the duplicate data permanently. If you want the duplicate data again, then copy the data on another worksheet.
- Select the cells on which you want to find and remove the duplicate values.
- Go to the ribbon and find the data option.
- In the data tab, you will find the remove duplicates option.
- Now check or uncheck the column on which you want to apply this constraint.
- Then click OK.
2. Find Duplicates in One Column using COUNTIF
Another way to detect duplicates is via the COUNTIF function. If you want to apply the COUNTIF function then check here how to find duplicates in Excel using the Excel COUNT function.
- If you have the list of items from where you want to find duplicates, then you need to apply the COUNTIF formula to it.
- The syntax for COUNTIF: “=COUNTIF(B:B, B2)>1”.
- In column A, you have the Buyer’s name and in column B, you have the fruits name that he or she likes. Now you want to find the duplicate items.
- Put this COUNTIF formula in column C.
- For all duplicate fields, it shows TRUE whereas, for non-duplicate fields, it shows FALSE.
- Now by following the above step-by-step process, you can delete all the duplicate items that you do not need.
Find Duplicates in Two Columns in Excel
Above we have seen how to find duplicate values in one column, now we will see here how to find duplicates in two columns in excel.
In this example, we have taken a table where the candidate name is in column A and Fruits is in column B. Now we want to find duplicate values having the same name and fruits.
The formula to find duplicate values in two columns is
=If (Countifs($A$2:$A$8, $A2, $B$2:$B$8, $B2)>1, "Duplicate Values"," ")
How to Count Duplicates in Excel
If you want to know the total number of duplicate values then you need to use the Count function. For counting duplicate values you need to use the countif formula.
3. Filter Duplicates in Excel
Another method to find duplicates in excel is to filter data to get duplicate values.
- Get a data set
- Select your table
- Go to the Data tab and then click on a filter icon located in the Sort & Filter section.
- If you want automatic filtering then convert the data available in the table into a completely functional excel table for which you need to press Ctrl+T.
- After clicking on the filter icon, you need to click on the arrow placed in the header of the column duplicate.
- Click on the drop-down of Duplicate value.
- To show the duplicate, you need to check the Duplicate Values option whereas if you want to filter the values then select blank and unselect “Duplicate values” to get the unique value.
Remove Duplicates Online
To remove duplicates, you need to follow the below-mentioned process
- Select the table by dragging the mouse.
- Right-click on the table.
- Delete the row by choosing the Delete Row option.
Highlight Duplicates in Excel
In order to highlight the duplicate values, you can either use a conditional formula to find duplicates in excel or follows the below-mentioned process.
- Select the duplicate values.
- Go to the Fill color option and select the color.
- This will highlight the duplicate values in excel.
Apart from these, there are other processes available by which you can find the duplicate items available in your data set. After finding out the duplicate values, you can remove them easily. Stay connected with our website to know other Excel features.
Here we have listed a few queries that are asked by Excel learners. If you are also facing this kind of problem then you can check it here.
Q1. How to delete duplicate e-mail addresses if you have 10,000 emails in your data set?
Ans. If you have all email addresses in a single column then select the column and go to the “Data” ribbon. You will find the ‘Remove Duplicates’ tab in data tools.
Click on Remove Duplicates and you will find another dialog box where you need to make a selection. Press Ok and you receive the unique email addresses.
Q2. How to find the repeated teacher’s name in Column A? The data is
- B1= Maths/English Jasmine, Eon
- B2= Physics Johana
- B3= History Poole
- B4= English/Physics Sam, Jasmine
- B5= Chemistry Romeo
Ans. To solve this problem, create three separate columns. In the first column, mention the class name. In the second column, put the teacher’s name, and in the third mention another teacher’s name. Now in the fourth column, put the formula =COUNTIF(B:B,B1)>1
Using this formula, you will find “TRUE” if the value is duplicate.
Q3. How to sort data before applying a filter?
Ans. To sort data you need to choose the Sort tab in excel. Sorting a data set is quite an easy process. Follow the process mentioned below and sort your data completely.
- Select the data set.
- Go to the Data ribbon.
- In the Sort & Filter section, you will find the Sort icon. Click on the icon and sort your data.
Q4. How to compare two columns in excel?
This question was mainly asked by the interviewer at the time of the interview. There are many ways to compare the two columns:
- =A2=B2 This is the simple formula used to compare two columns.
- Conditional Formatting: In this, you need to follow the above process, you can check the conditional formatting excel link to get detailed information.
- Vlookup: You can also use the Vlookup formula to compare two columns using Vlookup for research, ISNA for performance, and If to customize the result.
- Excel If Statement: This is a quite useful function in excel and it can be used in many ways. To get the detailed information Click Here.
Q5. How to filter data in excel?
Filtering in Excel is an easy process. In the sort & filter section, you will find the Filter icon. Click on the icon and filter your data.
In this tutorial, we have covered all the excel topics that are best for beginners and experts. We always try to solve all the problems that people face while working on excel. If you have any other query or want to ask something then you can mention in the comment box below. One of our team members will be back with the answer.
You may also like the following Excel tutorials:
- How to Remove Formulas in Excel (and keep the data)
- How to Fill Blank Cells with Value above in Excel
- How to Generate Random Numbers in Excel (Without Duplicates)
- How to Find Merged Cells in Excel
- Highlight Cell If Value Exists in Another Column in Excel
- Using Conditional Formatting with OR Criteria in Excel
- How to Remove Duplicate Rows based on one Column in Excel?
- Get Unique Values from a Column in Excel