How to shade alternate rows in Excel

Method 1: Format as Table (the easy, sturdy way)

You have a list of nine sales reps and their revenue, and your manager wants it easier to read across. Banded rows help the eye follow a row from the name to the number.

A table bands the rows for you, and the banding is tied to position, not to the data. Sort by Revenue, filter to one region, or add a rep at the bottom, and the stripes stay alternating.

1. Click any cell in the data.
2. Press Ctrl+T (or Home > Format as Table and pick a style).
3. Check that "My table has headers" is ticked, then click OK.
4. To change the look, go to Table Design and pick another style. The Banded Rows box on the same tab turns the stripes on or off; Banded Columns stripes the columns instead.

Method 2: A conditional formatting rule with MOD and ROW

If you don't want a table (shared templates, merged headers, or a sheet that feeds other formulas), use a rule. ROW() returns the row number, and MOD(ROW(),2) is 0 on even rows and 1 on odd rows. So =MOD(ROW(),2)=0 is TRUE on rows 2, 4, 6 and so on.

Here it's shown with a fixed reference so you can see the test: Hannah's row 2 is even, so the rule is TRUE and that row gets the fill. Row 3 returns FALSE and stays white. Use =MOD(ROW(),2)=1 instead to shade the odd rows.

Because the rule looks at the row number, the stripes stay perfect after sorting. Hand-colored rows don't: the fill moves with each rep, and your neat stripes turn into a random pattern.

1. Select the data rows, A2:D10 (leave the header out if it has its own style).
2. Home > Conditional Formatting > New Rule.
3. Choose "Use a formula to determine which cells to format".
4. Enter =MOD(ROW(),2)=0, click Format, pick a light fill on the Fill tab, and click OK twice.

Keep the stripes right after filtering

The MOD(ROW()) rule has one weakness. Filter the list to East and West, and the hidden rows break the pattern: two shaded rows can end up next to each other. A table re-stripes visible rows, but a rule needs a different test.

SUBTOTAL(103,range) counts only the visible non-empty cells. =SUBTOTAL(103,$A$2:$A2) counts the visible rows from the top down to the current one, so shading when that count is even always alternates on screen. The $A$2 start is locked and the end $A2 grows as the rule moves down.

With nothing filtered, row 5 is the 4th visible row, so it's shaded. Filter out some rows and the count changes to match what you see.

Band by group: shade each region in turn

Sometimes you want the stripes to follow a value instead: all East rows white, all North rows gray, South white again. That makes groups pop on a sorted list.

Count how many times the region has changed from the top down to the current row. $A$2:$A5<>$A$1:$A4 compares each region with the one above it; the header in A1 counts as a change, so East starts the count at 1. Shade when the count is even. At row 5 the count is 2 (East, then North), so the North rows are shaded. South makes it 3 and West 4.

The data must be sorted by the group column first. Use the rule =MOD(SUMPRODUCT(--($A$2:$A2<>$A$1:$A1)),2)=0 on A2:D10.

Every third row, or bands of three

For long lists people sometimes prefer a line every few rows. Subtract the first data row so the counting starts at 0: =MOD(ROW()-ROW($A$2),3)=0 shades rows 2, 5 and 8, the start of each block of three. Row 5 returns TRUE.

For thick bands (three white rows, three shaded rows), divide first: =MOD(INT((ROW()-2)/3),2)=0 is TRUE for rows 2-4 and FALSE for rows 5-7. Change the 3 to any band size.

If a rule doesn't seem to do anything, check that you entered it with the first selected row in mind, and that a table style or manual fill isn't covering it. Cellwise Fix (cellwise.app/excel-formula-helper/) can check a rule formula against your own rows.