Excel – Highlight a row where a value changes relative to previous row (Conditional Formatting?)

conditional formattingmicrosoft excelmicrosoft-excel-2010

I have a spreadsheet with lots of data. The data is grouped so that all the rows with a certain value in one column (say C) are grouped together. I want to highlight the "boundaries" – i.e., the first row where the value in column C is different from its immediate predecessor.

For example:

   A       B      C
1 Type   Val    Category
2 Cat    1.77     1
3 Newt   1.937    1
4 Toad   1.03     3
5 Newt   0.78     3
6 Dog    0.78     3
7 Worm   0.78     9

In this example, I want Excel to automatically find and highlight rows 2, 4, and 7, since those are where the value in C changes from the row before.

I tried using a Conditional Formatting rule with the formula $C1<>$C2 – hoping that Excel would increment through the rows finding & highlighting the transition rows – but it didn't work. Any ideas how to get the results I'm looking for?

Best Answer

What you suggest should work in combination with the right "applies to" range. For example if your data is in A1:C7 (with headers in A1:C1) then :

  • select the range without headers, i.e. A2:C7

  • use that formula (with preceding =), i.e.

    =$C1<>$C2

  • apply required format

That should format rows 2, 4 and 7 as expected

...but I note your comments about deleting rows so this version will combat that while not "hardcoding" the column letter

=INDEX($C:$C,ROW())<>INDEX($C:$C,ROW()-1)

Related Question