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.
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.
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.