Find duplicate value in excel through Countif
In latest versions of MS Excel finding duplicate values is
very easy by selecting desire column/ range and go to conditional formatting
and select duplicate values.
But today I am going to tell you other way to find duplicate
values, also it will tell you number of repetition of a value.
If value is single it will display “1” if value is repeat
two times it show “2” and number will increased as repetition increased.
It performed with a very simple formula, lets demonstrate
it.
Syntax of
“countif” formula is
Countif(range,
criteria)
Range: range
is a specific row or column/columns, which need to be check for count
Criteria: Criteria
is what to be count.
Now I am going to use same formula to find all duplicate
values.
Here I have a sheet with numbers which have multiple values.
In “A2” write formula
=countif($b$2:b2,b2)
Explaination:
“b2:b2” as range and make first value absolute by adding “$”
like “$b$2”.
second will not as absolute in range.
Criteria is also “b2”
By doing this it will search in range b2 to b2 and count b2
it will give one.
By copying formula in entire column it will change as
=countif($B$2:B3,B3) and so on.
It means it will search range b2 to b3 and count b3 value in
range.
you can also add a simple conditional formatting for values
more than one “1” will highlighted and it will ease to find duplicates.
and values greater than 1 will be highlighted red, which means value repeat in 2, 3,4 etc. times.
