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) 