Shade alternating groups of n rows

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.

April 14, 2025 · 1 min · 99 words · Michael Clark

Table of Contents

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

For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet.

When no reference is provided, ROW returns the row number of the cell which contains the formula.

Counting rows by multiples of n

CEILING Function

The Excel CEILING function rounds a given numberupto the nearest specified multiple.

CEILING works like theMROUND function, but CEILINGalways rounds up.

ISEVEN will return the #VALUE error if a value is not numeric.

Counting rows by multiples of n

ISODD will return the #VALUE error if a value is not numeric.

How to create zebra stripes with conditional formatting

Counting rows by multiples of n

Counting rows by multiples of n

Counting rows by multiples of n

Counting rows in groups of n

Excel formula: Highlight every other row

Excel formula: Highlight entire rows

Excel ROW function

Excel CEILING function

Excel ISEVEN function

Excel ISODD function

Counting rows by multiples of n

Counting rows in groups of n