Here we have provided the step by step process on how to find duplicates in excel. When you are working on a large number of data then you will encounter few duplicate entries. Sometimes these duplicate values are very useful but many times you want to remove the duplicate data. If you have 1000 of 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.
Table of Contents
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 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 save your time. Here you can check three different processes.
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.
This method will delete all the duplicate data permanently. If you want the duplicate data again, then copy the data on another worksheet.
Another way to detect duplicates is via COUNTIF function. If you want to apply the COUNTIF function then check here how to find duplicates in Excel using Excel COUNT function.
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 candidate name is in column A and Fruits is in column B. Now we want to find duplicate values having 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”,” “)
If you want to know the total number of duplicate values then you need to use count function. For counting duplicate values you need to use CountIf formula.
=Countif($A$2:$A$8, $A2)
Another method to find duplicates in excel is to filter data to get the duplicate values.
To remove duplicates, you need to follow the below-mentioned process
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.
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 learner. 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 remove duplicates tab in data tools. Click on Remove Duplicates and you will find another dialogue 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
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 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 Sort tab in excel. Sorting a data set is a quite easy process. Follow the process mentioned below and sort your data completely.
Q4. How to compare two columns in excel?
This question was mainly asked by the interviewer at the time of an interview. There are many ways to compare the two columns. To get 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 Filter icon. Click on the icon and filter your data. For more details Click Here.
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 member will be back with the answer.
NGINX Server Nginx, a popular open-source web server, excels at handling high traffic websites efficiently.… Read More
In the realm of web hosting, choosing the right web server is paramount. It acts… Read More
Are indispensable for ensuring smooth, precise linear motion in many industrial applications. Whether in robotics,… Read More
Cyber attacks are becoming more frequent, complex, and damaging. They can disrupt critical operations and… Read More
With the rise of new threats and the increasing complexity of IT environments, organizations need… Read More
1. Introduction In software design, managing complex systems can be challenging. The Facade Design Pattern… Read More