I am trying to apply conditional formatting in excel in which each first occurrence in a column has a highlight on the entire row. The desired result is as follows:

I currently have the formula "A3<>A2", but that highlight the last occurrence instead of the first. I don't know how to apply the highlight to all cells on the same row.

UPDATE: Apparently excel behaves differently for text and numeric values. My data looks like this:

CodePudding user response：

=$A2<>$A1

Take a look at $ symbols. If you don't use it it will highlight only 1 cell, because it will compare A2 to A1, B2 to B1 and so on.

Result:

CodePudding user response：

Conditional Formatting - Entire Row For First Occurrence in Column

• The issue you are facing is that you have selected e.g. the range 2:10 (focus on 2) and e.g. you are applying the corrected ($) formula =$A3<>$A2 which does what is expected: it highlights the last occurrence in a group i.e. if the value of the next row (3) is different than the value of the current row (2) then highlight row 2.
• To highlight the first occurrence in a group, you need =$A2<>$A1 as correctly posted by user11222393 since the first row you selected is row 2 i.e. if the value of the previous row (1) is different than the value of the current row (2) then highlight row 2.
• My solution will work similarly if the data is sorted. It will not highlight the first row of repeating groups though, as illustrated in the screenshot below.
• You will notice the difference between the solutions best by sorting the data in another column. Mine should have fewer highlighted rows.

Usage

• Select the entire rows of the range and goto Home -> Conditional Formatting -> New Rule -> Use formula... (you know the drill) and e.g. use

  =COUNTIF($A$2:$A2,$A2)=1
  

  for the first row being row 2.