Tables are often used to layout and summarize data. But have you been in a situation where the table got so big that it is not easy to read? The table is so big that it is hard to tell which rows and columns a certain cells belong. For example, in the table below, it is hard to tell which row the “Y”‘s are starting from the 3rd row.
The Excel file with the examples used in this post: Conditional Formatting
Alternate Row Colors
To make things more intuitive, you can use alternate shades on each row or column on the table using conditional formatting.
Below is a formula that returns true for each odd row.
The conditional formatting rule shades the background of each odd row, leaving the even rows with no color. And gets something like this:
Alternate Column Colors
The same thing could be done for columns. The following line returns true for all odd columns
In this example, data is laid out in a long table, making the 3 to 7’s hard to read. The alternating column colors make reading the table much more intuitive.
Coloring every other column makes the table much more intuitive to read.
A checker pattern is a combination of alternating row and column colors, with a white cell for “intersecting” cells. Below is the conditional formatting rules window used to make the checker pattern. Note the order, the white cell format must go first:
Formulas for each corresponding conditional formatting rule:
=MOD(ROW(),2)=0,MOD(COLUMN(),2)=0) <- selects the intersecting cell =MOD(ROW(),2)=1 <- selects odd row =MOD(COLUMN(),2)=1 <- selects odd columns
Instead of shading the intersecting cell white, a 3rd color can be used instead. In this case, blue.
2 thoughts on “Excel: Alternate Row Colors / Color Banding, Column Colors and Checker Patterns”
You should see technique 2 in http://office.microsoft.com/en-us/excel-help/apply-shading-to-alternate-rows-in-a-worksheet-HA010251644.aspx
The checker pattern dosen’t work????