Color Banding With Conditional Formatting 

In Excel97 and later versions, you can use the Conditional Formatting tool to create color bands in your rows, so that it appears as accounting ledger sheets or computer "green bar" paper.  The advantage of using Conditional Formatting to change the colors, rather than manually shading the cells, is that the colors will not get moved when you sort the worksheet or when you insert or delete rows or when you sort rows.   For more information about Conditional Formatting, click here. 

 
         Odd And Even Banding 

The left image shows what is called "odd banding".  This means that the odd bands are colored and the even bands are left plain.  Here, bands 1, 3, and 5 are colored in light blue, and the even bands, 2, 4, and 6 are left plain.  

Similarly, the right image shows what is called "even banding".  This means that the even bands, 2, 4, and 6 are colored, while the odd bands, 1, 3, and 5, are left plain.  

Note that "odd" and "even" refer to the groups of rows, not the number of rows in each band, and not the the row numbers.   

 

 

 
Formulas For Color Banding 

The formulas for odd banding and even banding are very similar.  For odd banding, use the formula 

=MOD(ROW()-Rw,N*2)+1<=N

where Rw is the first row number that is to be formatted, and N is the number of rows in each color band.  In the example shown on the left above, Rw is 8, and N is 3.   

For even banding, use the formula 

=MOD(ROW()-Rw,N*2)+1>N

where Rw is the first row number that is to be formatted, and N is the number of rows in each color band.  In the example shown on the right above, Rw is 8, and N is 3.   

 

 
To use these formulas in Conditional Formatting, select the cells that you want to format.  Then, go to the Format menu, and choose Conditional Formatting.  In this dialog, change "Cell Value Is" to "Formula Is", and enter one of the formulas above.  

 


In both formulas, you can either put it literal values for Rw and N, or you can create defined names and store the value there.  An advantage to using defined names is that you can change to band height very easily.   To shade alternate rows, simply change N to 1.  To shade in bands of 5, change N to 5.  If you are storing the values of N and Rw in other worksheet cells, as is done in the example workbook, be SURE to use absolute, not relative, cell referencing to reference these values.

And because the Conditional Formatting formulas are not moved with the cells when you sort a range, the bands will remain intact after the sort.