## Color every second row using conditional formatting in excel

Here is how to conditional formatting a cell range making an excel sheet easier to read.

**Conditional formatting formula:**

This formula colors every second row if any cell in the row is populated. If you know you will add more records later to the list, select and conditional format a range larger than the list. Empty rows won´t be formatted.

**How to apply the conditional formatting formula in excel 2007:**

- Select the range, example cell range A:C
- Click "Home" tab on the ribbon
- Click "Conditional formatting"
- Click "New rule..."
- Click "Use a formula to determine which cells to format"
- Click "Format values where this formula is true" window.
- Type =ISEVEN(ROW())*OR($A2:$C2<>"")
- Click Format button
- Click "Fill" tab
- Select a color
- Click OK!
- Click OK!

### Explaining the contional formatting formula in cell A2

=ISEVEN(ROW())*OR($A2:$C2<>"")

**Step 1 - Check if row number is even**

=**ISEVEN(ROW())***OR($A2:$C2<>"")

ISEVEN(ROW())

becomes

ISEVEN(2)

returns TRUE in cell A2.

**Step 2 - Check if any of the cells in row are not empty**

=ISEVEN(ROW())***OR($A2:$C2<>"")**

OR($A2:$C2<>"")

becomes

OR({"Federer", " Roger ", 25}<>"")

becomes

OR({TRUE, TRUE, TRUE})

and returns TRUE

**Step 3 - Multiply functions (AND logic)**

Both functions need to return TRUE in order to return TRUE

=ISEVEN(ROW())*OR($A2:$C2<>"")

becomes

TRUE*TRUE

and returns TRUE. Row 2 is conditional formatted.

### 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**

**OR(**logical1, logical2, …**)**

Checks whether any of the arguments are TRUE and returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE

**ROW(**reference**)**

Returns the row number of a reference

**ISEVEN(**number**)**

Returns TRUE if the number is even

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### 6 Responses to “Color every second row using conditional formatting in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

This is helpful, thanks, but I've been looking for the formatting to take place AS I fill new cells.

So my scenario is: A clear-formatted excel sheet, and ONCE you insert data into each row, the color of the row changes, e.g. row 1 becomes light blue, row 2 becomes dark blue, BUT row 3 is still clear-formatted because I haven't inserted data in it yet.

Do you know how I can do that?

*Thanks!!!*

@Sawsan,

Select all the cells (starting from upper left corner of the range so the the upper left cell ends up the active cell) that you think you will ever need to put your alternating row colors in (I would refrain from selecting entire columns), then follow the blog's instructions to get to the Conditional Formatting dialog box and then use this formula...

=AND(ISEVEN(ROW()),COUNTIF($A1:$C1,"*")>0)

Change the range as indicated, but note that your formula should only specify the first row's cells range even though you will have many more rows selected.

@Sawsan,

I guess I missed your request for a second color. That will require a second condition for the second color... use the identical formula I gave you in my previous message for this condition, but change the ISEVEN function to ISODD.

hi,

i'm interested to know on the conditional formatting.

how do i set the condition to evaluate a dynamic range?

my example is this:

Rule: =MAX($G$5:$G$20)=$G5

Applied to: =$AG$5:$AG$20

based on the value in G5:G20, the rule will determine the max value and highlight it on AG5:AG20

If i add new rows of data to G21 onwards, the rules does not "extend" to the newly added rows.

david,

I got this working in excel 2007:

Rule: =MAX(OFFSET($G$5;0;0;COUNTA($G5:$G1000))=$G5

Applied to: =OFFSET($AG$5;0;0;COUNTA($G5:$G1000)

thanks oscar,

i was using a dynamic named range as the rule in conditional formatting.

will try ur solutions ;)

thanks!!!