Software

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.

      4 comments
      evelynyguo
      evelynyguo

      I have a situation as following, can somebody help me? if cell A1= AA, then the whole row change color to red, if cell A1=AB then the whole row change color to yellow. Thanks

      c.fisher
      c.fisher

      I sometimes like to highlight every other row as a way to make the page easier to read. The formula =mod(row(),2)=0 technique works OK until you filter or hide rows/columns. For example if you set up the formatting to shade every other row, then hide one row that was not highlighted, then you will have two highlighted rows together in the displayed view). The following is an alternate formatting equation that (as long as there is one row/column that has no blank cells) will ignore hidden rows/columns in the worksheet. My example is for every other row assuming the first column has no blank cells: MOD(SUBTOTAL(103,OFFSET($A$1,0,0,ROW(),1)),2)=0 Cam

      awright
      awright

      Especially useful when new rows are inserted or rows are frequently sorted. Alex

      bhkolts
      bhkolts

      I have often wondered how to highlight every second or third row. I've been doing it the hard way, formatting each row one at a time. I played around with this and discovered I don't have to format the entire sheet but can highlight a certain range also. Thanks Susan, Barry

      Editor's Picks