In a previous post I extended the conditional formatting area automatically when a new row is entered. This gave me an idea about creating the same thing to make an excel sheet easier to read.

I have done a couple of posts before about coloring every second row (Increase sheet readability in excel and How to create excel macro to color every second row) but never used conditonal formatting dynamically.

So here is the conditional formatting formula:

=ISEVEN(ROW())

and here is the formula to dynamically increase the conditional formatting area:

=OFFSET($A$2,0,0,COUNTA($A:$A)-1,3)

These formulas are customized to this example, you need to change cell references to make it work. COUNTA is used in this example because column a contains text values. If you have numbers use COUNT instead.

OFFSET(The starting cell of your list,0,0,COUNTA(column in your list)-Starting row of your list-1,List width). Compare the above formula with the picture below.

Here is the result:

How to apply the conditional formatting formula in excel 2007:

1. Select the range
2. Click "Home" tab on the ribbon
3. Click "Conditional formatting"
4. Click "New rule..."
5. Click "Use a formula to determine which cells to format"
6. Click "Format values where this formula is true" window.
7. Type =ISEVEN(ROW())
8. Click Format button
9. Click "Fill" tab
10. Select a color
11. Click OK!
12. Click OK!

How to apply the dynamic conditional formatting area formula in excel 2007:

1. Select the range
2. Click "Home" tab on the ribbon
3. Click "Conditional formatting"
4. Click "Manage rules.."
5. Click in the window "Apllies to:"
6. Type =OFFSET($A$2,0,0,COUNTA($A:$A)-1,3)
7. Click ok!

Download excel sample file for this tutorial.
color-every-second-row-using-dynamic-conditional-formatting.xls
(Excel 97-2003 Workbook *.xls)

Functions used in this article

OFFSET(reference,rows,cols, [height],[width])
Returns a reference to a range that is a given number of rows and columns from a given reference

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

COUNTA(value1,[value2],)
Counts the number of cells in a range that are not empty

COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers

Related posts:

1. Highlight odd/even months using conditional formatting in excel 2007
2. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part 2