Conditional Formatting - Formula to highlight alternate rows or columns

Here's an example of using conditional formatting formulas.

It is recommended to first read and understand the post Conditional Formatting - formulas for selecting cells

For this example we will use a normal table like this.

As can be clearly seen the table is for Moe's Tavern, and it shows a record of the daily sales.

1. Highlighting alternate rows

To highlight alternate rows the formula to use should be:

=MOD(ROW(),2)>0 for odd rows or

=MOD(ROW(),2)=0 for even rows

To understand this formula I suggest going to the links at the bottom of the page.

And by clicking OK the following result is obtained:

We could also highlight every 3 rows, we would just need to change the formula to:

=MOD(ROW(),3)=0

2. Highlighting alternate columns

To highlight alternate columns the formula to use now would be:

=MOD(COLUMN(),2)>0 for odd columns or

=MOD(COLUMN(),2)=0 for even columns

If the formula for the even columns was introduced the result would be:

Well, we have seen how to highlight alternate rows or columns, or how to highlight every X number of rows or columns using a formula in conditional formatting. This is an useful trick for improving the impact of your visualisations.

Related links

MOD function

ROW function

COLUMN function

Download an example file

To download an example file with all the examples that we are covering here click on the following link:

Alternate rows and columns

To see more of conditional formatting click here.

Subscribe and get all entries via email

You can do it by providing your email into "Join the newsletter" box in the right sidebar.

Or follow me via Social Media

Facebook, Twitter and Google+