Alternate, Alternate Row Banding

Colouring alternate rows (or row banding) in Excel makes looking at data a lot easier on your eyes. There's so many ways to do it.

Back in the good old days, walking 20 kms to school in a blizzard, we used the below formulas with a suitable conditional format,

For odd rows use,

=ODD(ROW())=ROW()
or

=MOD(ROW(),2)<>0
For even rows use,

=EVEN(ROW())=ROW()
or

=MOD(ROW(),2)=0
Or even better, when using Autofilters,

=MOD(SUBTOTAL(3,A$1:A1),2)=0


(Last week's shopping was rather expensive)

But then along came Tables in 2007, with their automatic row banding making things easier. Gah!

Anyway, different colours for alternate rows is good but wouldn't it be even better if the colours change when values change? And still show different colours for alternate rows?

Here's one way to do it.

You'll need 2 helper columns. Let's say Columns A and B are available and values to decide the colouring starting in cell C1.

In cells A1 and B1, enter 1.

Then in cell A2, enter this,

=IF(C2<>C1,A1+1,A1)


and this in cell B2,

=IF(ISODD(A2),IF(B1=1,2,1),IF(B1=3,4,3))


and drag on down to the bottom of the list.



Now we enter some conditional formatting like this,



And we're finished. Here's a sample workbook.

See you next time.