Excel’s options for shading alternate rows are limited and sometimes ugly. Here’s how to take full control and design the way you like
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
5. Click the Format button and then click the Patterns tab.
6. 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.
FORMATTING IN EXCEL 2007
If you’re using Excel 2007, you have a whole range of new formatting tools at your disposal, including the alternate row shading offered through the Format As Table options. I’ll be writing more about these options in the future.
Nevertheless, you can still roll your own conditional formats if you choose: from the Home tab, click Conditional Formatting -> New Rule -> Use A Formula To Determine Which Cells To Format and then use the same formula described in Step 4 above to create banding.
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.
