Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
Many blog posts and educational sites tell us to use =countif($A$2:A2,A2)=1 in the selected range.
However, they don't explain it. And for me it doesn't work and since I can't understand it I can't debug it.
My understanding about Sheets is that $ is for absolute reference. Thus $A$2 means an absolute reference to the A2 cell. For me the formula becomes A2:A2 which is weird.
Can anyone please explain how this formula prevents the duplicates and what is the logic behind it? Thank you.
The range reference $A$2:A2 is using a mixed address mode where $A$2 is absolute and A2 is relative. When the formula is "dragged down" by copy-pasting cells or applying it to a range in a conditional formatting custom formula rule, the first part stays put and the second part adjusts, like this:
=countif($A$2:A2, A2) = 1
=countif($A$2:A3, A3) = 1
=countif($A$2:A4, A4) = 1
...and so on.
countif() will thus get the number of values that match the value in the current row, looking at the range that starts in A2 and ends in the current row of column A. If that count is 1, the formula gets true. If there are more than one copy of the value in the rows above and including the current row, the formula gets false.
