Following are 3 different methods to find the duplicates in a dataset.
Table of Contents
FILTER Function to find Duplicates
If you want to remove the duplicates from duplicates, wrap the above formula with the UNIQUE function
IF and COUNTIF Function to spot the duplicates
Like what we did in the above formula, here also we will use the COUNTIF function to find out how many times each value is repeated in the dataset. Then the IF function is used to check whether the ‘count’ is greater than 1 or not. Greater than 1 means, ‘Duplicate’.
Conditional Formatting to highlight duplicates
Conditional formatting can be used to highlight the Duplicate values as well as the Unique values in a dataset. To highlight the duplicate values, Select the cells containing data > in the Home tab of the Excel Ribbon > Conditional Formatting > Highlight Cells Rules > Duplicate Values…
A dialogue called Duplicate Values will be activated, Click on OK and all those Text and Number data with multiple instances will get highlighted.