Shade groups of rows with conditional formatting

This can be a nice way to make certain tables easier to read.

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.

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.

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.

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

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

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