Use conditional formatting to add shading to rows or columns in an Excel worksheet

Shading can keep you from losing your bearings as you read or work with Excel data -- and it's easy to set up. Instead of manually applying shading, enter this simple formula in the Conditional Formatting dialog box.

With conditional formatting, you can quickly add shading to your worksheet, making it easier to read and enter data. For example, say you would like to have every third row shaded gray. Follow these steps:

  1. Open a blank worksheet.
  2. Click the Select All button to select the entire worksheet.
  3. Go to Format and then click Conditional Formatting. (In Excel 2007 click Conditional formatting in the Styles Group on the Home tab. Click New rule.)
  4. Select Formula Is and enter =MOD(ROW(),3)=0.(In Excel 2007, select Use A Formula To Determine Which Cells To Format in the Select A Rule Type box. Click in the Format Values Where The Condition is True box and type: =MOD(ROW(),3)=0 (Figure A).

    Figure A

    conditional formatting formula

    1. Click Format.
    2. In the Patterns tab, select gray and click OK twice. (In Excel 2007, Click the Format button and then click the Fill tab. Under Background color, click gray and then click OK twice.)

      As Figure B shows, every third row is now shaded.

      row shading

      Note that instead of rows, you can shade columns. In step 4, enter the formula =MOD(COLUMN(),3)=0.

      Miss an Excel tip?

      Check out the Microsoft Excel archive and catch up on other Excel tips.

      Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

      Editor's Picks