Shading every other row in Excel

I sometimes wonder whether Microsoft spends so much on its programming budget it has little left over to spend on designers. This thought often crosses my mind when working with Office sample files, templates and styles.

Take, for example, the auto-formats supplied with pre-2007 versions of Excel. Who designed these things? Not only are they ugly, they also don’t address basic formatting needs such as coloring every other row in order to make a table more readable. Yes, there’s one auto-format option which gives you this – sort of – but it comes with unattractive headings and footer formatting and it’s inflexible.

The solution? Do the job yourself, using conditional formatting. Conditional formatting lets you apply formatting based on the value contained in a cell or based on the result of a formula.

Here’s how you can use it to apply shading to alternate rows:

1. Select the range of cells you wish to format.
2. Click Format -> Conditional Formatting.
3. In the drop-down box, select Formula Is.
4. In the box type:

=MOD(ROW(),2)=0

1. Click the Format button and then click the Patterns tab.
2. Select a color for the shaded rows and click OK twice.

There you have it: a table with banded colors. If you find you don’t like your chosen color, simply select the range once more, open the Conditional Formatting box and choose another color.

The real beauty of this method is that even if you delete a row in your table, the banding will be maintained automatically.

Conditional formatting explained

If you’re not familiar with conditional formatting, a little explanation is in order.

Conditional formatting is applied to a cell when the condition you specify is true. The condition may relate to the cell’s contents. For example, you could set all cells containing negative values to be displayed in a red font. Alternatively, you can use a logical expression or formula which evaluates as TRUE or FALSE, as we did in the example above. In Excel, a 0 value equates to FALSE, while any number greater than 0 equates to TRUE.

The MOD() and ROW() functions

The formula we used contains two functions, ROW(reference) and MOD(number,divisor). The ROW function returns the number of the row contained in reference or, if the reference is omitted (as in our example) it returns the number of the row containing the function. So, if you place the formula =ROW() in cell A9, the value returned is 9.

The MOD function returns the remainder of the number divided by the divisor. So, the formula =MOD(7,6) returns a value of 1, while =MOD(6,7) returns 6.

Evaluating Logical Expressions

Now take a look at the formula we used to create shading on every other row:

=MOD(ROW(),2)=0

In this case, ROW() provides the number while 2 is the divisor in our MOD() function. In English, we divide the current row number by 2. The remainder will either be 0 for even numbered rows or 1 for odd-numbered rows.

We then compare the result to 0. For even numbered rows, we end up with the expression 0=0, which is TRUE. Because the condition evaluates to TRUE, we apply the selected formatting. For odd-numbered rows, the result is 1=0, which is clearly FALSE, and thus the formatting is not applied and the row remains the default color.

Astute readers may be wondering, “Why do we need the =0 on the end of this expression, when we could simply evaluate =MOD(ROW(),2)?” It’s true, we could leave off the =0 and we’d still end up with an expression with alternating values of 0 and 1 and the same bands of color (although the shaded/non-shaded rows would be reversed). But by adding the =0 we make it very easy to extend this expression to coloring every third or fourth or fifth row, and so on. For example:

=MOD(ROW(),3)=0

will color every third row. This is easy to check by evaluating the expression for rows 1 through 6:

=MOD(1,3) = 1 1 = 0 is FALSE (not shaded)
=MOD(2,3) = 2 2 = 0 is FALSE (not shaded)
=MOD(3,3) = 0 0 = 0 is TRUE (shaded)
=MOD(4,3) = 1 1 = 0 is FALSE (not shaded)
=MOD(5,3) = 2 2 = 0 is FALSE (not shaded)
=MOD(6,3) = 0 0 = 0 is TRUE (shaded)

And so on.

So what happens if you leave the =0 off the expression? Instead of shading every nth row you’d color n-1 rows then leave the next row unshaded, something that might come in handy, too. In the case above, you’d shade rows 1 and 2, leave row 3 untouched, shade rows 4 and 5, and leave row 6 untouched.

Column and checkerboard shading

You can use a similar technique to create alternating shading on columns. Instead of using the ROW() function, you use the COLUMN() function:

=MOD(COLUMN(),2)=0

Note that the COLUMN() function returns the column number, with column A=1, column B=2 and so on.

Anyone for checkerboard shading? Try:

=MOD(ROW(),2) =MOD(COLUMN(),2)

You’ll end up with a checkerboard shading pattern.

Understanding the logic

If you’re content with this result and don’t need to know the nuts and bolts, class dismissed. If, on the other hand, you’d like to understand how we end up with a checkerboard pattern from this expression, stick with me. Unless you have some programming or a philosophy degree under your belt, trying to get your head around logical expressions can be a little confusing at first.

Note that to produce the checkerboard pattern, we’ve used two formulas one after the other; we’ve also dispensed with the final =0 we used in the earlier formulas.

Why is this so?

Well, instead of comparing the result of an expression to 0, we compare the result of the first expression to the result of the second expression, and determine whether they are equivalent. So, Excel evaluates the first formula and determines whether it is TRUE or FALSE. It then evaluates the second formula and determines whether it is TRUE or FALSE. Finally, it equates the two results and determines whether the end result is TRUE or FALSE.

In other words, you end up with four possible intermediate results:

1. Both expressions are TRUE.
2. The first is TRUE; the second is FALSE.
3. Both expressions are FALSE.
4. The first is FALSE, the second is TRUE.

Now, take the final step and evaluate these four intermediate results. The first and third evaluate to TRUE (that is, TRUE = TRUE is clearly TRUE, just as FALSE = FALSE is clearly TRUE); the second and fourth evaluate to FALSE (TRUE = FALSE is FALSE; FALSE = TRUE is FALSE).

So, if you apply the conditional expression =MOD(ROW(),2) =MOD(COLUMN(),2) to cell A1 (where both the row and column number are equal to 1) you get:

=MOD(1,2) =MOD(1,2)
1 = 1 which is TRUE

For cell A2:

=MOD(2,2) =MOD(1,2)
0 = 1 which is FALSE

For cell A3:

=MOD(3,2) =MOD(1,2)
1 = 1 which is TRUE

For cell A4:

=MOD(4,2) =MOD(1,2)
0 = 1 which is FALSE

For cell B1:

=MOD(1,2) =MOD(2,2)
1 = 0 which is FALSE

For B2:

=MOD(2,2) =MOD(2,2)
0 = 0 which is TRUE

For B3:

=MOD(3,2) =MOD(2,2)
1 = 0 which is FALSE

For B4:

=MOD(4,2) =MOD(2,2)
0 = 0 which is TRUE

And so on, with the alternating TRUE/FALSE/TRUE/FALSE in one column and FALSE/TRUE/FALSE/TRUE in the next building up the checkerboard shading.