Shading Alternate Rows/Columns Using a Formula and Conditional Formatting

By combining a formula using the MOD and ROW functions with conditional formatting, alternate rows of data can be shaded automatically. As an added bonus, if you add or remove rows, the row shading updates automatically

Steps:

1.      Open new worksheet

2.      Drat select rows upto A10 to highlight them.

3.      Click on the Conditional Formatting icon to open the drop down menu. Choose New Rule… option to open the New Formatting Rule dialog box.

4.    Select “Use a formula to determine which cells to format”, and enter the following formula in to the box below the Format values where this value is trueoption in the bottom half of the dialog box.

=MOD( ROW( ), 2) =0

5.      Click the Format button to open the Format Cells dialog box, now elick the Fill tab to see the background color options. Select a color to use for shading the alternate rows of the selected range. Click OK twice to close the dialog box and return to the worksheet.

6.      Alternate rows in the selected range should now be shaded with the chosen background fill color.

Similarly if you want to shade alternate columns change the ROW formula with COLUMN formula like =MOD(COLUMN(),2)=0

Tip: If you want to highlight the rows of your choice like 3, 6, 9 …. Or any other sequence, you can. For example if you want to highlight every 3^rd  row than change the formula like =MOD(ROW(), 3) = 0