Excel: Alternate Row Shading

Adding colors and shading to Excel spreadsheets, especially those with a lot of data in them, can make reading them much easier. Here's how you can set up every other column or row to display shading.

1. Select the group of cells (aka the range) that you want to shade.
   
2. Go to Format > Conditional Formatting
3. In that Conditional Formatting dialog box, change the drop down to display  Formula Is and type this formula in the field:
   
   To shade rows enter this formula:
     =mod(row(),2)=0
   To shade columns enter this one:
     =mod(column(),2=0
   To shade checkerboard style enter:
    =mod(row(),2) =mod(column(),2)
4. Click the Format button and in the Format Cells dialog box, click the Patterns tab.
5. Select a color and click OK.

 

Comments

[dhbtoo]

I'd like to add to the challenge: I use a similar routine, but I'd like to have the rows use a different color scheme starting at, for example, row 30. Even if I insert rows before 30 I'd like the routine make the change at 30.

db

[LawTechPartners]

Ron ~ thanks for the sub-tip. Thats a really good thing to mention. I might also add thoughts like: save the shading for last; think about how your spreadsheet will be received (PDF, part of a PowerPoint, on paper, on screen, etc.) and although I shouldn't have to say it, I just will...the color wont do you any good if you are printing to a b/w printer.
~Adriana~

[Ron Friedmann]

This is a cool formatting tip and I love the clever use of the modulus function. (Math geeks know that MOD is a functon to find the remainder after a division.)

But as far as using colors go, a warning.... Alternating row colors to improve readability is great. But shading individual rows to highlight them is not a good practice because you cannot easily sort the shaded rows. If you're doing analysis, be sure you can sort rows easily.

[Rob Robinson]

Adriana - love your site and your recommendations! Will be making it part of my regular reading.

infogovernance.blogspot.com
coolfools.blogspot.com