Alternating row colors in Excel file (Tip)

[doni49]

I realize this isn't an Acad issue.  But over the twenty years that I've been doing CAD, I've used Excel for a LOT of my CAD work.  During that time, I've on MANY occasions needed to shade every other row (to make it easier to read a list of points for example).  But I've never found a way to do this other than picking every other row and changing the fill color.

I had an epiphany yesterday and figured out how to do it and thought I'd share in case anyone else needs to do similar.

It requires "Condtional Formatting" and combining two formula functions.  After you click "New Rule", click "Use a Forumla......" at the bottom of the list.

Then use the following formula (make note of the dollar sign -- and only the one).  This works because the ROW function tells you the row number of the specified cell.  By not putting a dollar sign before the 1, it will change to A2 when it's on row 2 and A3 when on row 3 etc.  The MOD function will divide the first number (which is the row number) by the second number and return the REMAINDER.  So if you divide the row number by 2 and the remainder is 0 then the row number is an even number.  If the remainder (return value from the mod function) is greater than zero), then the row number is ODD.

I've attached an example xls file.

=mod(row($a1),2)=0

[Joe-Bouza]

Thanks. You may also be interested in "format as Table" that option has some added benifits

[doni49]

Thanks Joe.  That looks like another good option.  Cool!

[KMercier_C3D]

I've always found it quicker to just define two of the rows as I want them and select them both, then click the paintbrush "Format Painter" to copy the formatting and then selecting all the rest of the cells that I want to be alternating. Quick. Easy.

[KMercier_C3D]

I've always just done two rows worth and then used the paintbrush to copy the formatting all the way down.