Create 'ledger paper' in Excel

Sep 02, '04 11:10:00AM

Contributed by: robg

If you work in Excel, here's an easy way to create alternating colored and clear rows. With alternating patterns, wide reports are much easier to read, as your eye has a visible 'line' to follow across the page. Normally, you might create these patterns by selecting a row in your report, giving it a pattern, and then copying that pattern to every other row. This works great until you add or delete rows, then you have to do it all over again.

But if you use Excel's conditional formatting, you can create auto-updating alternating rows. Here's how it works. Start by selecting the entire rows for the data range you'd like to color -- click on the row number itself and you'll highlight everything. Now select Format -> Conditional Formatting. Conditional formats are formats which vary based on either a value or the result of a formula.

In this case, let's assume you want a format that creates a colored pattern on the even numbered rows, with no pattern on the odd-numbered rows. Click the pop-up menu at the left of the dialog and choose "Formula Is." Set the formula to =MOD(ROW(),2)=0. It should look like this when you're done. The MOD(ROW(),2) portion of the function gives the remainder of the current row number divided by two. If the row is even, there's no remainder; if it's odd, the remainder is 1. The =0 tells the conditional formatting to take action only when the remainder is zero, in other words, on the even rows.

Now click the Format... button and set the pattern you'd like to use -- I find that a 50% light green looks nice when printed on grayscale printers. Click OK and marvel at your now instantly-ruled report. And thanks to the formula in the conditional formatting box, the rules will remain correct, even as you add and delete rows.

Comments (28)


Mac OS X Hints
http://hints.macworld.com/article.php?story=2004090208104647