7

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?

Improve this question
6

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)

Improve this answer
8
  • 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 Jan 4 '12 at 14:02
  • 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 Jan 4 '12 at 14:08
  • 1
    Ooops - 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 Jan 4 '12 at 14:14
  • 1
    I 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 Jan 4 '12 at 14:26
  • 1
    Yes, 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 Jan 4 '12 at 15:31
1

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.

Improve this answer
3
  • that's a nice workaround but barry's solution should work – JMax Jan 4 '12 at 13:54
  • 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 Jan 4 '12 at 13:55
  • 1
    See my third comment to your answer. Turns out the INDIRECT approach has an advantage over the "direct" approach. Thanks. – yosh m Jan 4 '12 at 14:16
0

You could use the following to invoke the conditional format.

Screen Shot

Improve this answer
1
  • 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 Jan 4 '12 at 14:33
0

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()))
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.