Excel Alternate Row Shading Using Conditional Formatting
In addition to shading titles and other rows containing important data, it is common to add shading to alternate rows of data in a table to make it easier to read that information.
While shading alternate rows is a relatively easy job to do manually for a small table, in large ones it can be an onerous task.
A better way of shading alternate rows in large tables is to use conditional formatting.
Conditional formatting allows you to apply chosen format options only when certain conditions are met.
For help with this example, see the image above.
- Drag select cells A8 to F11 to highlight them
- Click on the Home tab
- Click on the Conditional Formatting icon on the ribbon to open the drop down menu
- Choose New Rule option to open the New Formatting Rule dialog box
- Click on the Use a Formula to determine which cells to format option from the list at the top of the dialog box
- Enter the following formula in to the box below the Format values where this value is true option in the bottom half of the dialog box
=MOD( ROW( ), 2) =1
- Click the Format button to open the Format Cells dialog box
- Click the Fill tab to see the background color options
- Select the green shaded box that is second from the top in the color samples
- Click OK twice to close the dialog box and return to the worksheet
- Rows 9 and 11 should now be shaded with a light green background color