Logical
Shade alternating groups of N rows (ISEVEN, QUOTIENT & ROW)
Calculate alternating fixed-height row-band flags and block numbers, with a positive integer band height.
Group-level zebra striping: toggle TRUE / FALSE every N rows cleanly!
Subtract the first data-row number from the current row number, divide by the band height, and use integer quotient parity to alternate bands. With a start at row 2 and height 3, rows 2–4 have TRUE flags, rows 5–7 FALSE, and rows 8–10 TRUE.
These are positional blocks, not groups based on matching values. Sorting changes which records occupy each band, and hidden or filtered-out rows still count in the physical row offset.
A Boolean formula alone does not paint cells. Use it as the predicate of a formula-based conditional-formatting rule over the desired range, where supported, with a chosen fill. The interactive example calculates and verifies flags and block numbers; it does not automatically install that formatting rule.
| Worksheet Row | Zero-Based Offset | QUOTIENT(Offset, 3) | ISEVEN Shade Flag |
|---|---|---|---|
| Rows 2, 3, 4 (Block 1) | 0, 1, 2 | 0 (Even) | TRUE (Shaded Band) |
| Rows 5, 6, 7 (Block 2) | 3, 4, 5 | 1 (Odd) | FALSE (White Band) |
| Row 8 (Block 3 start) | 6 | 2 (Even) | TRUE (Shaded Band) |
Alternating N-row band boolean rule (TRUE for Band 1, 3, 5...)
Use a positive whole-number band height in D2. For a rule beginning at A2, retain A2 as the relative row reference and lock the starting row and height references. Invalid heights return FALSE so they do not enable the rule.
=IF(NOT(ISNUMBER($D$2)),FALSE,IF(OR($D$2<1,$D$2<>INT($D$2)),FALSE,ISEVEN(QUOTIENT(ROW(A2) - ROW($A$2), $D$2))))1-based Block / Group Number every N rows
Adding +1 to QUOTIENT assigns Group 1, 1, 1, 2, 2, 2, 3... for pivot or SUMIFS grouping.
=IF(NOT(ISNUMBER($D$2)),"Invalid inputs",IF(OR($D$2<1,$D$2<>INT($D$2)),"Invalid inputs",QUOTIENT(ROW(A5) - ROW($A$2), $D$2) + 1))Try the example
Use the original values or change them to test your understanding.