# Shade alternating groups of n rows in Excel

This tutorial shows how to Shade alternating groups of n rows in Excel using the example below;

## Formula

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

## Explanation

To highlight rows in groups of “n” (i.e. shade every 3 rows, every 5 rows, etc.) you can apply conditional formatting with a formula based on the ROW, CEILING and ISEVEN functions.

In the example shown, the formula used to highlight every 3 rows in the table is:

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

Where 3 is n (the number of rows to group) and 4 is an offset to normalize the first row to 1, as explained below.

### How this formula works

Working from the inside out, we first “normalize” row numbers to begin with 1 using the ROW function and an offset:

ROW()-offset

In this case, the first row of data is in row 5, so we use an offset of 4:

ROW()-4 // 1 in row 5 ROW()-4 // 2 in row 6 ROW()-4 // 3 in row 7 etc.

The result goes into the CEILING function, which rounds incoming values up to a given multiple of n. Essentially, the CEILING function counts by a given multiple of n:

This count is then divided by n to count by groups of n, starting with 1:

Finally, the ISEVEN function is used to force a TRUE result for all even row groups, which triggers the conditional formatting.

Odd row groups return FALSE so no conditional formatting is applied.

### Shade first group

To shade rows starting with the the first group of n rows, instead of the second, replace ISEVEN with ISODD:

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