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 comes 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. Prevent duplicates using dynamic conditional formatting in excel
2. Create a dynamic border to your list using excel conditional formatting
3. Highlight duplicates using conditional formatting in excel
4. Highlight the second or more duplicates in two lists using conditional formatting in excel
5. Highlight unique values and unique distinct values in a range using conditional formatting in excel
6. Highlight dates within a date range using conditional formatting
7. Highlight duplicate values in a range using conditional formatting in excel
8. How to create excel macro to color every second row
9. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel
10. Highlight smallest duplicate value in a column using conditional formatting in excel