Excel: Alternate Row Colors / Color Banding, Column Colors and Checker Patterns

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

As a table gets bigger, it is easier to confuse which row or column each cell belongs to. Alternating bands of colors can make a table more intuitive and easy to read. The “Y”‘s closer to the middle is especially hard to tell which row it belongs to.

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.

=MOD(ROW(),2)=1

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

=MOD(COLUMN(),2)=1

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.

When a table has many rows, it’s hard to figure out which column the data is in, especially ones in the middle of the table.

Coloring every other column makes the table much more intuitive to read.

Shaded Columns with Conditional Formatting

For a long table with a lot of columns, always make it easier for the reader by using conditional formatting on the columns.

 

Checker Patterns

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>