Excel find duplicates column google sheet

( Remember that C1 effectively refers to the current row being checked to see if it should be highlighted). The first term to be evaluated (the COUNTIF(C:C, C1) > 1) is the exact same as in the first example it's TRUE only if whatever is in C1 has a duplicate. Highlight the first duplicate only: =AND(COUNTIF(C:C, C1) > 1, COUNTIF(C$1:C1, C1) = 1)Д®xplanation: This only highlights if both of the COUNTIFs are TRUE (they appear inside an AND()). the value has duplicates) then: apply the highlight ( because the formula, overall, evaluates to TRUE). So this says, count up occurences of whatever is in C1 (the current cell being evaluated) that are in the whole of column C and if there is more than 1 of them (i.e. ( And yes, if you use C2 then you asking the rule to check the status of the row immediately below the one currently being evaluated.) Essentially, when evaluating a conditional format formula, anything which refers to row 1 is evaluated against the row that the formula is being run against. ( So it's more like INDIRECT(C &ROW()), if that means anything to you!). Because this formula is evaluated by a conditional format rule, instead, when the formula is checked to see if it applies, the C1 effectively refers to whichever row is currently being evaluated to see if the highlight should be applied. Highlight duplicates (in column C): =COUNTIF(C:C, C1) > 1Д®xplanation: The C1 here doesn't refer to the first row in C.

Excel find duplicates column google sheet