Exceljet

Quick, clean, and to the point

This video is part of our Excel video training library.

Shade groups of rows with conditional formatting

Summary 
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.
Video Transcript 

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.

ROW()

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

ROW()-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 and specify 3 for significance, we'll get rows grouped in multiples of 3.

CEILING(ROW()-4,3)

Next, I can divide that result by 3.

CEILING(ROW()-4,3)/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.

=ISEVEN(CEILING(ROW()-4,3)/3)

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.

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

=ISEVEN(CEILING(ROW()-offset,n)/n)

Note: if you want to shade rows starting with the first groupin, you can use the ISODD function instead of ISEVEN function. To change the number of rows shaded, just adjust the variable "n" in the formula.

Author 
Dave Bruns

Related shortcuts

Your videos are the best one I have seen so far. I use excel in my advanced acc. techniques course and may be coming back to you for some tailored chosen set of videos. - Joanna
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course