Zebra striping — shading every other row — makes wide tables far easier to read. A conditional-formatting rule with ISEVEN(ROW()) does it dynamically, so the stripes stay correct even when you insert or delete rows.
ROW() gives the current row number; ISEVEN is TRUE on even rows, so they get shaded — and the pattern auto-corrects when rows shift.
How it works
Even rows are shaded. The helper column shows the rule’s result.
| A | B | C | |
|---|---|---|---|
| 1 | Item | Qty | Even row? |
| 2 | Apple | 10 | TRUE |
| 3 | Banana | 8 | FALSE |
| 4 | Cherry | 12 | TRUE |
| 5 | Date | 5 | FALSE |
The formula
The banding rule (applied to the data range):
How it works
The rule keys off the row number, so it’s self-maintaining:
ROW()returns the row number of each cell.ISEVEN(…)is TRUE on even rows (2, 4, 6…), FALSE on odd ones.- Conditional formatting shades the TRUE rows, producing alternating stripes.
- Because it’s based on the live row number, inserting or deleting a row re-stripes automatically — unlike manual fill colors.
Even simpler: a Table. Select your data and press Ctrl+T — Excel Tables come with banded rows built in (toggle it in Table Design). Use the formula rule when you need banding without a Table, or custom stripe logic.
Try it: interactive demo
Choose a banding pattern.
Variations
Bands of two (or N) rows
Group rows into stripes of N:
Banded columns
Stripe vertically instead:
Color only data rows
Combine with a non-blank test:
Pitfalls & errors
The first data row’s parity depends on where it starts. If your table begins on row 2, even rows are shaded; starting on row 3 flips it. Adjust with ROW()+1 if needed.
Manual fill colors override the rule. If some cells were colored by hand, clear that fill so the conditional format shows.
A Table is usually easier. For plain banding, Ctrl+T beats a rule. Reach for the formula when you need custom stripe widths or conditions.
Practice workbook
Frequently asked questions
How do I shade every other row in Excel with a formula?
How do I make bands of two or more rows?
Isn't there an easier way to get banded rows?
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