Shade every 3rd row (or 5th, or 10th) for readability with a MOD rule on the row number — a clean band pattern that survives sorting and filtering.
ROW() gives each cell’s row number; MOD(…, 3)=0 is TRUE on every 3rd row.
The example
Every third row shaded.
| A | B | |
|---|---|---|
| 1 | Row | Shaded? |
| 2 | 3 | yes |
| 3 | 6 | yes |
The formula
The conditional-formatting rule:
How it works
MOD on the row number creates the cycle:
ROW()returns the current cell’s row number.MOD(number, 3)gives the remainder when divided by 3 — it cycles 1, 2, 0, 1, 2, 0…= 0picks every 3rd row; change 3 to band at a different interval.- Offset the start by subtracting:
MOD(ROW()-1, 3)=0shifts which rows light up.
Banding by group, not row? See the shade-alternating-groups recipe — this MOD rule bands by position, which is what you want for fixed stripes but not for variable-size groups.
Try it: interactive demo
Shade every Nth row.
Variations
Offset the start
Shift which rows:
Every other row
Classic stripes:
Every Nth column
Use COLUMN():
Pitfalls & errors
ROW() is absolute. It returns the sheet row, so the pattern is tied to position, not to your data’s first row — offset with -1/-startRow if needed.
Banding moves with rows. Insert a row and the stripes re-flow — expected, since it’s based on row number.
For a Table, use built-in banding. Excel Tables have banded rows as a style option — simpler than a MOD rule there.
Practice workbook
Frequently asked questions
How do I shade every Nth row in Excel?
How do I shift which rows are shaded?
How do I band columns instead?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class