User Rating: 5 / 5     Shade Alternate Rows using Conditional Formatting

---------------------------------------------------------------------------------------------------------------------------- Shade Alternate Rows, including blank/unused range

Select worksheet range you wish to format

Home  ->  Conditional Formatting -> New Rule -> select "Use a formula to determine which cells to format" option (Excel 2007)

Format -> Conditional Formatting  ->  "Formula Is"  (earlier versions of Excel)

Enter formula  ->  =MOD(ROW(),2)=0  {Refer Image 1}
- the MOD function will return the Remainder of the row number when divided by 2
- the ROW function  =ROW()  will return the row number
- the formula will color even rows, wherein the remainder is 0, and will leave out odd rows wherein remainder is 1

Format -> select "Fill" tab  ->  choose "Background color"  ->  Click OK twice  (Excel 2007)

Format -> select "Patterns" tab  ->  choose color  ->  Click OK twice  (earlier versions of Excel)

To clear formatting: Home  ->  Conditional Formatting -> Clear Rules -> Clear Rules from Selected Cells. (Excel 2007)

Formula Variations:

to color odd rows  ->  Enter formula  ->  =MOD(ROW(), 2)=1  or  =MOD(ROW(), 2)

to color every third even row  ->  Enter formula  ->  =MOD(ROW(), 3)=0  ->  to color every n th row use formula  =MOD(ROW(), n)=0

=MOD(ROW(), 4)=3 will color every 4th row, starting at row 3  ->  =MOD(ROW(), 5)=3 will color every 5th row, starting at row 3  ->  =MOD(ROW(), 5)=4 will color every 5th row, starting at row 4,  and so on

to color rows in say, bands of 3  ->  Enter formula  ->  =MOD(ROW()-1, 6)<3  Note: "ROW()-1" is used to make the first band equal to 3 rows to color rows in say, bands of 4  ->  Enter formula  ->  =MOD(ROW()-1, 8)<4  ->  =MOD(ROW()-1, 2*n)<n is the Formula where bands = n

--------------------------------------------------------------------------------------------------------------------------- Shade Alternate Rows, ONLY used range

Select worksheet range A1:C50, starting from A1

Conditional Formatting -> New Rule -> select "Use a formula to determine which cells to format" option (Excel 2007)

Format -> Conditional Formatting  ->  "Formula Is"  (earlier versions of Excel)

Enter formula  ->  =AND(MOD(ROW(), 2), COUNTA(\$A1:\$C1))  {Refer Image 2}
- Note that the column reference is absolute while row reference is relative (\$A1:\$C1)
- Every odd row wherein you enter data (in column A to C), will be colored, within the table range A1:C50
- to color even rows  ->  Enter formula  ->  =AND(MOD(ROW(), 2)=0, COUNTA(\$A1:\$C1))

Format -> select "Fill" tab  ->  choose "Background color"  ->  Click OK twice  (Excel 2007)

Format -> select "Patterns" tab  ->  choose color  ->  Click OK twice  (earlier versions of Excel)

To clear formatting: Home  ->  Conditional Formatting -> Clear Rules -> Clear Rules from Selected Cells. (Excel 2007)