A couple of years ago I came up with a formula to apply irregular banding for repeated values in a table or a pivot table. It uses conditional formatting and this SUMPRODUCT formula:
This array-type formula basically says: Count the number of times the value changes from one cell to the next up to the row where I am (assuming for a moment that I’m a cell). Divide that count by two, and check whether the remainder is 0. This True/False result can then be used to apply the conditional formatting.
The formatting looks like this, where the banding is based on changes in the Animal column:
If the above looks familiar, you may be thinking of this DDOE chestnut:
f
The difference, aside from my more subdued color scheme, is that Dick’s only starts a new band for the first hamster or what-have-you. It assumes (I assume) that each animal only has one group. Mine assumes bands of hamsters all over the place, and applies a new stripe with every change.
The bad news is my formula doesn’t do well in a long list if you try to delete large numbers of rows. For example, with 20,000 rows if I try to delete all but one, Excel goes into “Not Responding” mode longer than my patience will tolerate (roughly 35 seconds). I don’t know exactly why, but I bet if I re-read this Charles Williams post I would.
The other problem is, as John Walkenbach mentions in Dick’s post, the banding fails if you insert a line before the first row. I tried solving this by using INDEX (and OFFSET, I think) and learned you can’t use it in conditional formatting. Bummer.
The good news is neither of these problems affects its use in pivot tables.
So what to do if you’ve got a 20,000 rows of data you want to band irregularly? The answer, as Tushar Mehta pointed out, is a helper column:
Here’s the formula, starting in A2:
It uses the volatile OFFSET function. If you’re going to add or delete rows, OFFSET keeps you from getting #REF! errors or having gaps in the logic.
Then your conditional formatting formula is just: =MOD($A2,2)=0
One cool thing about the helper column banding formula above: It utilizes the fact that SUM ignores any text in the range you’re summing. If you get rid of the SUM and change it to =IF(B2=OFFSET(B2,-1,0),OFFSET(A2,-1,0),OFFSET(A2,-1,0)+1), you’ll get #VALUE! errors because of the text in A1.
Oh wow, look at the time. I’ve got to go restripe my parking lot. With these handy formulas it should go quickly though. See you soon!
Hey, 2006 was a crazy time. We were all using day-glo green back then. 🙂
Funny that someone recently saw this was done on a ListObject and asked how it was done. I looked high and low through Excel’s table formatting options, but no dice. It turns out it was conditional formatting all along. Sure would be nice to have this built in as a table formatting option.
Pingback: Excel Roundup 20140616 « Contextures Blog
“I tried solving this by using INDEX (and OFFSET, I think) and learned you can’t use it in conditional formatting. Bummer.”
I’m not sure that statement is true or under what circumstances it is.
I often use OFFSET(xxx, -1) in my conditional formats to avoid the row insertion problems.
I never actually used INDEX before but it seemed to work when I tried it.
Seb
Seb, thanks for the comment. I’ve done a little more research and it might be a restriction that started in Excel 2010. At any rate, if I change the formula to
=MOD(SUMPRODUCT((INDEX(A:A,1):INDEX(A:A,ROW()-1)<>INDEX(A:A,2):INDEX(A:A,ROW()))*1),2)=0, which works fine in a worksheet, I get this error:“You may not use reference operators (such as unions, intersections, and ranges) or array constants for conditional formatting criteria.”
The trouble with INDEX is that you can’t always reuse it directly inside another formula. I forget the specifics, but that’s what’s going on here. It returns the correct result to the worksheet, but just doesn’t work as a named range or as part of another formula.
Well, if anybody has any ideas how to make an array-ish and first-row-of-data-insert-proof formula such as this, please let us know. I’ll write a short poem in your honor or give you an autographed copy of MenuRighter, and generally sing your praises.