Monday, July 27, 2009

Green Bar Accounting Paper

I have to admit that I am old enough to remember using the old-fashioned Green Bar Accounting Paper (and not on a computer, mind you). One of my favorite variations had every third row highlighted in light green. A quick search of the internet shows that it is still around today, since it makes reading long rows of data much easier.

Wouldn’t it be great if there was an Easy Way to simulate this in Excel? Yes, indeed, and it is so simple, you will laugh! Here is how you do it:

1. Fill the third row in your database a light green

2. Select the first three rows (two with no color and the third in light green), and using the fill handle, drag to the end of the database

3. Important: Click on the Auto Fill Options box in the lower right-hand corner of your database and select Fill Formatting Only.

Voila! An Easy-to-read Green Bar Excel worksheet!


excel-diva said...

Bob- I like your blog. You can also do this using Conditional Formatting in Excel 2007/
In Conditional Formatting, click New Rule.
Click on Use a Formula and type in =mod(row(),2)=0 and then pick a green fill format. Click OK.
=mod(row(),3)= would mean that every 3rd row is shaded.

Unknown said...

An alternative formula for conditional formatting is "=TRUNC(ROW()/3/2)=TRUNC(ROW()/3)/2", where 3 is how many alternating lines you want to color. Notice the difference between the two sides of the equation is whether you truncate before or after dividing by two.