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 —————————————————————————————————————————
