User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 
 
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)