When you use Excel for dealing with a large amount of data, you may find it difficult to find duplicate values.Many people do not know how to find duplicates in columns in Excel. They ask questions about finding duplicates in columns.
You need to know that excel find duplicates in column cells of your large data. After reading the blog, you will know about finding duplicates in Excel.
How to Find Duplicates in Excel?
You can deal with the large amount of your data using excel. You can find and remove the duplicate using the excel. There are three ways to find the duplicates using the excel. The ways to find duplicates are explained in the below.
Find Duplicates in Excel Using Conditional Formatting
The first way to find the duplicate in excel is using conditional formatting features. you will find many features in excel but you need to use conditional formatting feature to find and highlight the duplicate. to find the duplicate, you need to follow the given below steps:
Step 1. First of all, you need to prepare the data in which duplicates are present. then select the cells you want to review for duplicates in the first step.
Step 2. The next step is to go to the Home tab and the Styles section of the ribbon. Then, you need to click Conditional Formatting, move to Highlight Cell Rules, and choose Duplicate Values in the pop-out menu.
Step 3. In the third step, when you see that the Duplicate Values window displays, you should immediately see your duplicates highlighted with the default formatting applied.
Step 4. In the final step, you must confirm that Duplicate displays in the first dropdown box. Then, click the second dropdown box to select a different format. Finally, click OK to apply the format to your duplicate data. So, this is the first method by which excel find duplicates in column cells of your complex data.
How to Find Duplicates in Excel Using COUNTIF?
The next method to find the duplicate in excel is using COUNTIF.In this method, you have to use the formula after preparing the data. To find the duplicate using COUNTIF, you have to follow some steps:
Step 1. To start with, you have to prepare the data containing the duplicates. Let's say that you have prepared the data below, in which you can see the duplicates. A goal is to count the times each name is present in the table.
Step 2. In the second step, you need to copy the data in Excel within the range of the cells from A1 to A11. Add another column called the Count column in cell B1. Then, you can apply the COUNTIF function under the count column to get the count of duplicates.
Step 3. In the third step, you need to count the duplicates in Excel using the COUNTIF function.
=COUNTIF(range, criteria)
=COUNTIF(A: A2, B1)
In the context of the above example, you need to apply the COUNTIF function under cell B1.
Where
A: A2 is the column where all values of your data will be stored
A2 is the column where you have your first value.
The COUNTIF function will count the times the name appears in your data. For example, Jon appears three times.
To apply the COUNTIF function across all names, you need to drag the function from cell B2 to cell B11. Then, you can see the number of times of each name in your Excel data. By this method excel find duplicates in column cells of your selected data.
How to Count Duplicates for Each Cell?
Using the excel, you can also count the duplicates for each cell. No matter how many duplicates are present in each cell, you can count duplicates using the excel.
To count the duplicates for each cell, you need to follow these steps:
Step1. In the first step, you must prepare your Data.
Step 2. Enter the ‘B2’ Cell formula in the second step. You need to enter =COUNTIF($A$2:$A$13,A2)
Step 3. In the next step, you must drag the formula to the end of the data range using the fill handle.
Step 4. In the final step, you can see that all your duplicate data have a corresponding cell that displays their number of occurrences.This is a method through which excel find duplicates in column cells of your data.
Tips and Notes on Finding Duplicates in a Column Excel Multiple Formulae
Excel find duplicates in column of your large data in many forms. You can duplicate the values in single and multiple columns.
Find and Highlight Duplicates in the Single Column.
Apart from that excel find duplicates in column cells, it can also highlight duplicates in a single column, you need to follow some simple steps, which are given below:
Step 1. Select the data you want to duplicate the values in the first step.
Step 2. in the second step, you have to go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values in your data.
Step 3. In the third step, open the Duplicate Values box, select Duplicate on the dropdown on the left, and specify the format to highlight the duplicate values.
Step 4. It will highlight the values you want to be duplicated.
Find and Highlight Duplicates in the Multiple Columns
If you have data with multiple columns and want to duplicate the values, the process is the same as the single column. The step you require to duplicate the values in the multiple columns is given below:
Select the data.
After the selection of the data, you have to go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
In the Duplicate Dialogue box, you need to select the Duplicate on the left of the box. And modify the format to highlight the duplicate values.
This will highlight the cells you want to duplicate the values.
You learned how to find and highlight duplicate values in the above section. In this section, you will learn how to remove the duplicates in Excel. You can remove the duplicates from single and multiple data columns.
Remove the Duplicates in the Single Column of the Data.
You know that excel find duplicates in column cells of your data. it can also help you remove the duplicates, here are some steps for removing them:
Select the data. Then, you have to go to Data> Data Tools > Remove Duplicates.
In the remove dialogue box, if your data has a header, you need to ensure that the My data has headers option checked. And also, make sure that the column is selected. Then click Ok.
Remove Duplicates in the Multiple Columns in Excel
If you have data in multiple columns and want to remove some entries from your data, the process is the same as the single column. Here are some steps to remove the duplicate values:
Select the data.
In the second step, you need to go to Data> Data Tools > Remove Duplicates.
In the Remove Duplicate Dialogue box, if your data has headers, you need to check the My data has headers option. And you have to make sure that the column is selected that you want to remove. Select all columns except the date column. Then, click Ok. This would remove two entries from your data.
A Good Choice that Can Help You
WPS Office
When working with large data, WPS Office can help you by providing simple steps to find and highlight duplicates. Here are some simple steps you can follow while using WPS to find duplicates and highlight them.
Step 1. In the first step, select the data area and then click the Data tab > Highlight Duplicates > Set.
Step 2. The table area is automatically selected by the dashed box. And, If you want to change the data area, drag to select the desired area.
Step 3. Finally, you have to click OK to preview the effect.
Step 4. After comparing the two columns, you need to clear the highlighted duplicates. For this purpose, select the data area and then click the Data tab > Highlight Duplicates > Clear.
Step 5. After comparing the two columns, you want to remove the highlighted duplicates, select the data area, and then click the Data tab > Highlight Duplicates > Remove Duplicates. You need to know the columns you want to remove from your data to remove the duplicates.
Step 6. In the final step, click Remove Duplicates to finish the overall process.
FAQs
How to Find How Many Times a Value is Repeated in a Column in Excel?
If you want to find how many times a value is repeated in a column in Excel, you need to use the formula equals to count. Then, it would be best to select the data range where you want to know the repeated values in your column.
The formula should be written in the form such as the = COUNTIFF(range, criteria). This way, you will find how many times a value is repeated in the coun of your data.
How Do I Get Unique Values from a Column with Repeated Values in Excel?
Along with that the excel find duplicates in column cells of your data, it also allows you to get the unique values with repeated values in Excel, follow some steps. The steps are given below:
In the first step, you need to select Data > Advanced to filter your data.
In the next step, you must check the Filter the list-in place and click on the Unique records only. Finally, click on Ok. After doing all these steps, you can get the unique record values in the column of your data.
How to Remove Duplicates in Excel?
You can remove the duplicate values from your data even if your data has both single and multiple columns.
Remove Duplicates in the Single Column in Excel.
To remove the duplicates in a single column of your data, you have to follow the steps:
Select the data. Then, you have to go to Data> Data Tools > Remove Duplicates from your data.
In the Remove Duplicate Dialogue box, you must ensure the My data has headers option checked. Select the columns. Then click Ok.
Remove Duplicates in Multiple Columns in Excel.
Removing the Duplicate in multiple Excel columns is similar to a single column. For removing the duplicates, you need to follow some simple steps:
Select the data. You should go to Data > Data Tools > Remove Duplicate.
In the Remove Duplicate Dialogue box, ensure the My data has headers option is checked. Select all columns except the date column. Then click Ok.
Find Duplicates in Column Using WPS Office
In this blog, you learned how to find and highlight duplicate values of your data in Excel. There are three methods used by excel find duplicates in column if you are dealing with a large amount of data.
These may be difficult to understand. In this case, you have an alternative to find and highlight the duplicate values in your data. If you are working with a large amount of data, you have the option of WPS Office, which provides the simple step to find the duplicate values in your data. Additionally, the WPS Office allows you to remove the duplicate values you want. The simple steps of the WPS Office ensure the protection of the user data, and your data will not be lost.
