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 worksheet to make it easier to read that information.
While shading alternate rows is a relatively easy job to do manually for a small amount of data, in large worksheets it can be an onerous task.
A better way of shading alternate rows is to use conditional formatting.
Conditional formatting allows you to apply chosen format options only when certain conditions are met.
Tutorial Steps:
For help on these steps, refer to 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 into 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 A9 to F9 and A11 to F11 should now be shaded with a light green background color.
Return to Index page
