Microsoft

Shade Excel worksheet rows, columns, and sometimes both

Expand a simple technique for shading rows to columns and create a number of interesting patterns.
Shading every nth row in a worksheet is a common format. In fact, Mary Ann Richardson shared a helpful technique for doing so last month. You can expand this same technique to shade both rows and columns to create some interesting, albeit odd, shading patterns. Before we take a look at columns, let's review Mary Ann's original technique, which includes the following  conditional expression: =MOD(ROW(),interval)=0 where interval is the number of rows in the pattern. For example, if you want to shade every other row, interval is 2; to shade every third row, interval is 3, and so on. By substituting the ROW() function with the COLUMN() function, you can shade columns in the same way, as follows:

  1. Select the range you want to format. If you want to format the entire worksheet, click the Select All button (the blank cell that intersects the row and column header rows) or press [Ctrl]+A.
  2. Choose Conditional Formatting from the Format menu.
  3. Click Condition 1's drop-down arrow and choose Formula Is.
  4. Enter the following expression in the formula control: =MOD(COLUMN(),2)=0
  5. Click the Format button, click the Patterns tab, choose a color, and click OK. You've entered the conditional expression and specified a format.

  1. Click OK to return to the shaded worksheet.

With a little experimentation, you can create a number of shading patterns, but let me get you started. First, to create a checkerboard pattern, use the above instructions, but enter the following expression in step 4: =MOD(COLUMN(),2) =MOD(ROW(),2). Frankly, I've never known a use for a checkerboard pattern, but don't let that stop you from experimenting. Remember, for more variety, interval doesn't have to be the same for both rows and columns. By entering the formulas separately, you can shade alternating rows and columns. Follow the previous instructions, but in steps 3 and 4, create two conditions. By changing intervals and colors, you can create any number of interesting patterns. Remember, neither the interval nor the color has to be the same for both columns and rows, but the first conditional expression takes precedent. Don't go nuts with this technique or you'll end up with a mess. The truth is, you'll not find a lot of use for alternating shade patterns, but knowing you can expand on the technique might come in handy some day.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

7 comments
rich_odwazny
rich_odwazny

You can eliminate the problem noted in step 7. Apply the NOT function to the formula given in step 4 after changing the interval as desired. For example, to shade every fourth row: =NOT(MOD(SUBTOTAL(3,$A$1:$A2),4))

OCF100
OCF100

Anybody know how to do two rows shaded, two rows blank, two rows shaded etc? I am trying to group pairs of data more easily by shading but can't work out if I can put this is in the formula. Thanks

CharlieSpencer
CharlieSpencer

Unfortunately, if you sort the colors remain with the data, not with the cell. I've found that to be the case in both 2003 and 2007.

umusbkraz
umusbkraz

Checkerboards are nice, but something that comes up all the time is shading the headers, labels, and totals of a table. Since tables come in different sizes is there a way to format the headers labels and columns no matter what size the table is?

Rick_from_BC
Rick_from_BC

Your old friend Format Painter will do this. Shade the two rows in your favorite puce color. Now, select two unshaded rows and two shaded rows, and click on Format Painter (double-click to LOCK it ON). Drag your mouse over the rows to be affected, then release the mouse. Not automatic, so I do this at the very end. Also, the shading DOES indeed move about if you sort the sheet after you format the shading. I seem to remember this forum had a similar hint about 2 years ago, which I used, but keep returning to Format Painter. One other hint: if you use the shaded rows or columns as data separators (i.e., just fill with no data in the cells) don't forget to make the rows or columns much smaller than the data rows/columns. And use a very pale or faint fill color to avoid chopping the visual flow too much.

RayG314
RayG314

to Palmetto, The only alternative I've found is to shade LAST, just before saving and/or printing.

Editor's Picks