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

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?

yosh m

Posted 2012-01-04T13:38:57.183

Reputation: 2 048

Answers

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)

barry houdini

Posted 2012-01-04T13:38:57.183

Reputation: 10 434

Good point about specifying the correct Applies To range. I actually had selected the range of the entire spreadsheet. Out of curiosity I checked if limiting it to starting with the second row (as you suggested) also worked - and it did. Thanks. – yosh m – 2012-01-04T14:02:44.170

Just to clarify - I did try using exactly what you said - i.e., my original attempt with =$C1<>$C2 without INDIRECT - and it did not work for me. So I tried it again, now, and - it worked this time! What did I forget the first time? I'm not sure... But you were right that INDIRECT was unnecessary. Thanks. – yosh m – 2012-01-04T14:08:36.383

1Ooops - a problem with this answer: I'm manually cleaning up the data and when I delete a highlighted row (which I do all the time - the first data points are almost always bogus) the new transition row is NOT highlighted with the simple rule. However with the INDIRECT rule it IS highlighted (which is in fact the behavior I need). I have gone back to doing it with the INDIRECT for now. However, I'd be happy to know of a less obtuse way to specify it. Thanks. – yosh m – 2012-01-04T14:14:29.993

1I did a little more experimenting and it seems to me there is a bug in Excel. When I use your approach (i.e., my original attempt) and later delete a row - Excel for some reasons spawns one or two new CF rules and the new rules cover different ranges and at least one I saw is now invalid (#REF). Using the INDIRECT function this doesn't happen. I don't know why. It seems to me to be a bug in Excel. – yosh m – 2012-01-04T14:26:43.097

1Yes, I agree with you yosh m, that you will get problems if rows are deleted with the CF ranges split - IMO this worked better in Excel 2003 where that wouldn't happen.....INDIRECT is still problematic, though, if you were to delete, move or insert COLUMNS then because "C" is "hardcoded" the CF wouldn't adjust - you can get round that by using INDEX, instead, whereby that adjustment would take place if required - i.e. formula would be =INDEX($C:$C,ROW())<>INDEX($C:$C,ROW()-1) – barry houdini – 2012-01-04T15:31:25.830

I believe if you define a named range, and base the CF rule on that range, the CF rule split will not happen. – TheBlastOne – 2012-01-04T17:03:58.747

Barry - Good! The INDEX function indeed looks like the best way to go. Thank you very much! Yosh – yosh m – 2012-01-04T20:42:40.190

Barry - please edit your answer to include the INDEX solution and I will mark it as the Accepted Answer. Thanks! yosh – yosh m – 2012-01-04T22:08:08.973

Aha! Found it. This formula does the trick in conditional formatting:

 =INDIRECT("C"&(ROW()-1))<>INDIRECT("C"&(ROW()))

If there are other or better ways, I'd be interested to hear, but I've solved my immediate problem with this. Thanks.

yosh m

Posted 2012-01-04T13:38:57.183

Reputation: 2 048

that's a nice workaround but barry's solution should work – JMax – 2012-01-04T13:54:53.110

it's almost always unnecessary to use INDIRECT in conditional formatting (and probably not a good idea). Your original approach was almost right - see my answer here – barry houdini – 2012-01-04T13:55:22.773

1See my third comment to your answer. Turns out the INDIRECT approach has an advantage over the "direct" approach. Thanks. – yosh m – 2012-01-04T14:16:39.680

I wanted something that survived copy & paste, moving, etc. Here's what I came up with:

=INDIRECT(ADDRESS(ROW(), COLUMN()))=INDIRECT(ADDRESS(ROW()-1, COLUMN()))

Tom

Posted 2012-01-04T13:38:57.183

Reputation: 11

You could use the following to invoke the conditional format.

Screen Shot

CharlieRB

Posted 2012-01-04T13:38:57.183

Reputation: 21 303

Thanks, Charlie, but I wanted a single rule to apply to the whole spreadsheet so that Excel does the work for me. Meanwhile I found a solution (see my answer to myself). Thanks. – yosh m – 2012-01-04T14:33:47.610