Banded Rows

Occasionally there is a need when formatting a worksheet to use banded rows (i.e. cells in the odd rows color filled and the cells in the even rows not filled).  If the worksheet has only a few rows you can simply select the cells in the appropriate rows and format them with the desired color.  However, that's not practical when the sheet contains dozens or hundreds of rows.  When you have too many rows to do manually you can use conditional formatting.


Select the range of cells to which the formatting is to be applied.  For this example I've used the range A1:E10.

For Excel 2007 from the Home tab select Conditional Formatting > New Rule > "Use a formula to determine which cells to format".  Type the following formula in the "Format values where this formula is true" box:


=MOD(row(),2)


Click the Format button and select the Fill tab and then select the fill color.

Click OK

Click OK and your worksheet range now has banded rows.

In the future we'll look at how to make the rows dynamic so when you add data to rows after row 10 the conditional formatting will expand to the newly populated rows.


As always if you have any comments or questions, please comment here or email me at the office.