Explanation
TheCOUNTIF functioncounts the number of times each value appears in the data range.
When the count is 1, the formula returns TRUE and triggers the rule.
Conditional formatting is evaluated for each cell that is applied to.
Highlight unique values
Excel contains many built-in "presets" for highlighting values with conditional formatting, including a preset to highlight unique values. However, if you want more flexibility, you can highlight unique values with your own formula, as explained in this article. To highlight cells that contain unique values in a set of data, you can use a formula that returns TRUE when a value appears just once. For example, if you have values in the cells A1:D10, and want to highlight cells with duplicate values, you can use this formula: =COUNTIF($A$1:$D$10,A1)=1 Note: with conditional formatting, the formula must be entered relative to the "active cell" in the selection, which is assumed to be A1 in this case.
