Logical & Conditional
Highlight Every Other Row & N-Row Zebra Bands (ISEVEN, ROW & MOD)
Generate TRUE/FALSE zebra-striping flags for alternating single rows using ISEVEN(ROW(A2)) or header-anchored MOD(ROW(A2) - ROW($A$2), 2) = 1, and group N-row visual blocks via MOD(INT((ROW(A2) - ROW($A$2)) / N), 2) = 1.
Build dynamic zebra striping and multi-row block bands that survive inserted rows!
Manually painting alternate ledger rows gray breaks the instant someone inserts, deletes, or re-sorts a row—leaving clunky double-gray or double-white streaks across financial statements!
Conditional Formatting formula rules evaluate a TRUE/FALSE expression dynamically from each cell’s live worksheet coordinate ROW(): ISEVEN(ROW(A2)) shades every even worksheet row (rows 2, 4, 6...), while anchoring relative to the first data row with MOD(ROW(A2) - ROW($A$2), 2) = 1 guarantees Data Row #1 is always unshaded (0) and Data Row #2 is shaded (1) regardless of how many title rows sit above the table! Need 3-row visual blocks for quarterly schedules? Divide the 0-based offset by N = 3 inside INT: MOD(INT((ROW(A2) - ROW($A$2)) / 3), 2) = 1!
| Data Row Position (Starting at A2) | Alternating Formula MOD(ROW()-ROW($A$2),2)=1 | 3-Row Band Formula (N = 3) | Visual Shading State |
|---|---|---|---|
| Rows 2, 3, 4 (Offsets 0, 1, 2) | FALSE, TRUE, FALSE | FALSE, FALSE, FALSE (Band 0) | White 3-row group |
| Rows 5, 6, 7 (Offsets 3, 4, 5) | TRUE, FALSE, TRUE | TRUE, TRUE, TRUE (Band 1) | Shaded 3-row group |
| Even Worksheet Row Test ISEVEN(ROW(A2)) | TRUE on Row 2, FALSE on Row 3 | 1-Row Parity Check | Classic even-row stripe |
Header-Anchored Alternating Row Rule: MOD(ROW(A3) - ROW($A$2), 2) = 1
Returns FALSE on the first data row (A2) and TRUE on every alternating second data row (A3, A5, A7...).
=MOD(ROW(A3) - ROW($A$2), 2) = 1Multi-Row N-Line Banded Block Rule: MOD(INT((ROW - Start) / N), 2) = 1
Shades blocks of N consecutive rows (cell B2 = 3) together so 3-line journal entries or quarterly sets stay visually grouped.
=IF(B2 < 1, "Invalid Band Size", MOD(INT((ROW(A5) - ROW($A$2)) / B2), 2) = 1)Try the example
Use the original values or change them to test your understanding.