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) 