Saturday, July 9, 2011

Excel Tutorials: How to Shade Rows with Conditional Formatting in Excel 2007

Shading Rows with Conditional Formatting in Excel


For more Excel tips and tricks go to my static page:  Excel Tutorials and Tricks Series.

Conditional formatting can be used for shade alternate rows in a selected range of data. In large worksheets, shading alternate rows makes it easier to read data from left to right across the worksheet .

Example: Shading Rows with Conditional Formatting in Excel 2007

Note: For help with this example, see the image above.

  1. Open an Excel worksheet - a blank worksheet will work for this tutorial.

  2. Drag select cells A1 to H10 to highlight them.

  3. Click on the Home tab.

  4. Click on the Conditional Formatting icon on the ribbon to open the drop down menu.

  5. Choose New Rule option to open the New Formatting Rule dialog box.

  6. Click on the Use a Formula to determine which cells to format option from the list at the top of the dialog box.

  7. 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) =0

  8. Click the Format button to open the Format Cells dialog box.

  9. Click the Fill tab to see the background color options.

  10. Select a color to use for shading the alternate rows of the selected range.

  11. Click OK twice to close the dialog box and return to the worksheet.

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

No comments:

Post a Comment