Lesson#103: 4 Ways to find duplicate values in a worksheet

Often we need to find the duplicate values in an Excel worksheet. Here in this post, I will discuss 4 Ways to find duplicate values in a worksheet.

1st Method: Function MODE and MODE.MULT

The Excel MODE function returns the most frequently occurring value of a number set. It helps you to find a duplicate value in an array. Syntax of this function is MODE(number1, [number2], …) where number1 is a number or cell reference that refers to numeric values and number2  [optional] is A number or cell reference that refers to numeric values.

Example:

=MODE(23,21,21,56,78,99)=21

In this above example MODE returned the most frequently occurring value 45. The limitation is it only returns a single value.

Excel MODE.MULT function returns the most frequently occurring numbers in a numeric data set with a vertical array.

Syntax of this function is MODE.MULT(number1, [number2], …) where number1 is a number or cell reference that refers to numeric values and number2  [optional] is a number or cell reference that refers to numeric values.

Example:

Selected a vertical space in the worksheet then I entered the formula by pressing Ctrl Shift Enter

The limitation of these functions is they return the most occurring values.

What about the duplicate values which occur lesser times?

2nd Method: Conditional Formatting

Conditional Formatting allows you to find out the duplicate or unique values in a worksheet with a very easy method.

You have to first select the array or the area of the worksheet then go to Conditional Formatting>Highlight cells rules>Duplicate values

There you can select both duplicate and unique values to highlight.

3rd Method: PivotTable

Here I am showing you an example of how to find duplicates by PivotTable.

Select the data array and insert a PivotTable. Put the value field in Rows and again put the same field in Values and change the field settings to Count. See there is a nice way to find out.

4th Method: COUNTIF

You can use COUNTIF and put it in a column beside the array to show how many times it occurs in the complete array.

See the above example I have put the COUNTIF function to count the number of times it occurring in an array.

So there are more many ways to find out duplicates. You have to choose or use the way which suits you best for your work.