How to highlight duplicates in Excel

Conventional equation

=COUNTIF(data,A1)>1

 

Explanation

Note: Excel contains many implicit "presets" for featuring values with restrictive arranging, this incorporates to feature copy esteems moreover. In any case, in the event that you need greater adaptability, you can feature copies with your own equation, as clarified here.

If you need to feature cells that contain copies in a lot of information, you can utilize a straightforward strategy that profits TRUE when an estimation of information shows up more than once.

For instance, if you need to feature copies in the range B3:G10, you can utilize this equation:

=($B$3:$G$10,B3)>1

 

Note: with restrictive designing, it's significant that the recipe be entered comparative with the "dynamic cell" in the choice, which is thought to be B3 right now.

How this equation functions

COUNTIF essentially checks the occasions each an incentive in extend. At the point when the tally is multiple, the recipe returns TRUE and animates the standard.

 

 

At the point when you utilize an equation to apply restrictive organizing, the recipe is assessed to the dynamic cell in the choice, when the standard is made. On the off chance that the range we are utilizing in COUNTIF is bolted with a flat out location, yet B4 is completely relative. In this way, the standard is assessed for every cell in the range.