It’s often a good idea to highlight duplicate values in a data set to help easily identify the outliers. Here, you can find out how you can do this dynamically using Excel formulas.

Syntax

=COUNTIF(absolute data range reference, relative cell reference)

Steps

1. Begin by choosing the data range (i.e. your table)
2. Open the Conditional Formatting window by going to HOME > Conditional Formatting > Add New Rule
3. Select Use a formula to determine which cells to format
4. Enter the formula COUNTIF and the logic (i.e. =COUNTIF($B$2:$G$7,B2))
5. Click the Format button to edit formatting settings
6. Click OK to continue and apply your settings

How to highlight duplicate values in a data set

The conditional formatting feature applies selected formatting options to a cell, when a given condition is met. If this condition is provided by a formula, Excel will check whether the formula returns TRUE before applying formatting options. Therefore, we need a formula that will return TRUE when the value in a cell exists more than once within the selected range.

The COUNTIF function returns the number of cells that meets specified criteria. So, the idea is to check the cell count to see whether there are more than 1 cell. If it is, we want it highlighted. The COUNTIF function takes two arguments which, range of values, and the criteria, to specify which values to return. For example,

=COUNTIF($B$2:$G$7,B2)>1

Fortunately, we do not need to add conditional formatting to each cell one-by-one. Excel can handle this by looking at absolute and relative references. All you need to do is to make an absolute range reference (i.e. $B$2:$G$7) because this range shouldn’t change and leave the original cell (B2) relative. This way, the range can be updated for other cells added later.

If you’d rather highlight top values, see How to highlight the top values in a data set dynamically.