How to Shade Alternate Row Colors in Excel

To add alternate row colors in Excel, press Ctrl+T (Mac: ⌘T) to turn your data into a table, which shades every other row for you. If you'd rather keep a normal range, add a conditional formatting rule with the formula =MOD(ROW(),2)=0. Both stay correct when you sort or add rows. This guide covers both methods, plus bands of several rows, columns, filtered lists and how to remove the shading.

Why shade alternate rows?#

Banded rows, also called zebra stripes, help the eye follow a row across a wide table. They make it easier to read the right number in long lists, printed reports and screen shares.

Avoid painting the stripes by hand with the Fill Color button. Manual colors belong to the cells, so when you sort, filter or insert a row, the pattern breaks. Both methods below calculate the stripes, so they always line up.

Method 1: Format as a table (the fastest way)#

A table adds banded rows in one step.

1. Click any cell inside your data.
2. Press Ctrl+T (Mac: ⌘T), or go to Home > Format as Table and pick a style.
3. Check that My table has headers is ticked and click OK.

Excel shades every other row. To change the look, click inside the table and use the Table Design tab:

• Table Styles: pick another color scheme.
• Banded Rows: tick or untick the stripes.
• Banded Columns: shade every other column instead, or as well.

Tables also grow when you add rows at the bottom, and the stripes follow. The guide to Excel tables and Ctrl+T covers the other benefits, such as formulas that fill down by themselves.

If you need a plain range again, use Table Design > Convert to Range. The colors stay, but they become fixed formatting, so they won't adjust when you sort or insert rows.

Method 2: Conditional formatting with MOD and ROW#

A conditional formatting rule shades rows on any range, without making a table. It's the method to use when a table isn't allowed or when you need a custom pattern.

1. Select the data rows, for example A2:E50 (leave out the header).
2. Go to Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. Type this formula:

=MOD(ROW(),2)=0

5. Click Format, open the Fill tab, pick a light color and click OK twice.

Every even row is now shaded: rows 2, 4, 6 and so on.

How it works: ROW() returns the row number of each cell, and MOD(number,2) returns the remainder after dividing by 2. Even rows have a remainder of 0, so the rule is TRUE and Excel applies the fill. For row 4, MOD(4,2) is 0; for row 5, it's 1. The MOD and INT guide explains MOD in more detail.

To shade the odd rows instead, use =MOD(ROW(),2)=1. The shorter =ISEVEN(ROW()) and =ISODD(ROW()) work too.

Shade bands of several rows or every nth row#

You can change the pattern by changing the maths.

The bands formula works in three steps. ROW()-2 makes the first data row count as 0. Dividing by 3 and taking the INT gives the same number for each group of three rows: 0 for rows 2 to 4, 1 for rows 5 to 7. Then MOD checks whether that group number is even. Change both 2s if your data starts on a different row, and the 3 for a different band size.

Keep the stripes when you filter#

The ROW-based rule uses real row numbers, so when a filter hides rows, two shaded or two plain rows can end up next to each other. To stripe only the visible rows, use SUBTOTAL:

=MOD(SUBTOTAL(3,$A$2:$A2),2)=0

• SUBTOTAL(3,…) is COUNTA that ignores rows hidden by a filter.
• $A$2:$A2 grows one row at a time as the rule moves down. The first $A$2 is locked and the second $A2 has an unlocked row, so in row 10 it reads $A$2:$A10.
• The count goes 1, 2, 3 … down the visible rows, and MOD shades every second one.

Column A must have a value in every row for the count to be right. Use an ID or name column that is never blank.

Shade rows by group instead of by count#

Sometimes you want the color to switch when a value changes, such as each new date or customer. A helper column makes this easy. With the group in column A and data from row 2:

1. In F2, type 1.
2. In F3, type =IF(A3=A2,F2,1-F2) and fill down.
3. Add a conditional formatting rule on A2:E50 with =$F2=1.

The helper flips between 1 and 0 each time column A changes, and the rule shades every row where it's 1. The data must be sorted by the group column. The $ before F keeps every cell in the row looking at column F, the same trick used to highlight a whole row based on one cell.

Edit or remove alternate row shading#

To change a conditional formatting rule, select a cell in the range and go to Home > Conditional Formatting > Manage Rules. There you can edit the formula, the color or the Applies to range.

To remove the shading:

• Rule: select the range and choose Conditional Formatting > Clear Rules > Clear Rules from Selected Cells, or from Entire Sheet.
• Table: untick Banded Rows on the Table Design tab, or pick the None style at the top-left of the style gallery.

If your rule doesn't work, check three things: the formula starts with =, the Applies to range starts on the row you expect, and no other rule with a different fill is above it in the list. The guides to conditional formatting with formulas and conditional formatting basics cover rule order and more examples.

Practise it#

Banding is one formula rule, and the same skill colors whole rows by status, date or value. Practise conditional formatting in a live spreadsheet: you highlight values, duplicates and the top 3, then color whole rows with a formula rule. Then practise more formatting to tidy number and date styles. Try the exercise on this page to see exactly what the banding rule returns for a row.