Microsoft Excel alternating color rows / color banding
Let's say you have a table of data Starting in A1 and ending in D6. All you need to do is select the range A1:D6, Starting from A1, then go to Format>Conditional Formatting and choose "Formula is:" and then in the box to the right, type the formula as shown below;
=MOD(ROW(),2)
The MOD formula/function is used to return the remainder of a number (ROW()) after dividing it by a specified number, two (2) in this case.
The ROW formula/function will return the row number of the cell that houses it.
So the formula =MOD(ROW(),2) will only ever return 0 (zero) or 1. If you do not know already, 0 (zero) is equal to FALSE, while any number greater than 0 (zero) will equate to TRUE. When we use the "Formula is:" option of Conditional Formatting we must have a formula that returns only TRUE or FALSE. When TRUE, the format specified is applied. When FALSE, the format specified is not applied. With this in mind, we will return TRUE to all all rows where the row number divided by 2 equates to TRUE. Or, put another way, every second row.
Now click the "Format" button and choose your desired cell shading under "Patterns". Then "Ok" and "Ok" again.
Automatically Expand/Contract Alternate Row Colors/Color Banding
The simple method shown above is fine for a static table, but it will apply the format to all odd row numbers that do not yet have data. For example, we use the range A1:D6 but could use A1:D100 so that as our table has more data added the new row of data will be color coded automatically, while all unused rows will remain blank.
1) Select the A1:D100, Starting from A1.
2) Go to Format>Conditional Formatting and choose "Formula is:"
3) In the box to the right, type the formula as shown below;
=AND(MOD(ROW(),2),COUNTA($A1:$D1))
Wednesday, November 05, 2008 3:30:04 PM