Shade groups of rows with conditional formatting

In this video, we'll look at how to use conditional formatting to shade alternating groups of rows. For example, you can use this approach to shade groups of 3 rows, groups of 4 rows, and so on. This can be a nice way to make certain tables easier to read.

Here we have a table with 3 rows of data for each client, for the months April, May, and June.

Let's use conditional formatting to highlight these rows to match the data.

We can do this with a formula that groups rows by three's.

As usual, I'll set up dummy formulas to figure out a working formula.

Then I'll create a conditional formatting rule in the final step.

To start off, I want to get a row number for each row, starting with 1.

I can use the ROW function for this, but, by itself, row returns the current row number, so numbers begin at 5.

We can fix this with a simple offset, and subtract 4.

Next, we need a way to group rows by multiples of 3. The CEILING function works nicely in this case. CEILING rounds numbers up to a given multiple, so if we give CEILING the row number, and specify 3 for significance, we'll get rows grouped in multiples of 3.

Next, I can divide that result by 3.

This gives us the same groups, but starting with the number 1.

Now we have what we need to easily force a TRUE result for rows we'd like to shade. All we need to do is wrap this formula in the ISEVEN function.

The result is TRUE for each even-numbered group, and FALSE for odd groups - exactly what we need for our conditional formatting rule.

To create the rule, start by copying the formula in the upper left cell.

Then create a new conditional formatting rule, paste the formula into the dialog, and set the desired format.

Now rows are shaded to match the data, and the table is much easier to read.

If you want to shade rows starting with the first three, you can use the ISODD function instead of the ISEVEN function.

To change the number of rows shaded, just adjust the variable "n" in the formula.

What's nice about this formula is it's generic, and doesn't depend on the data in the table.