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 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 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
