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.


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.

Editor's Picks