Excel: How to Highlight Every Other Row (Two Reliable Methods)

To highlight every other row in Excel, either format the range as a table (Home > Format as Table, then pick a style with banded rows) or add a conditional formatting rule with the formula =MOD(ROW(),2)=0. The table route takes a few clicks and keeps banding as the data grows; the formula route works on any plain range and gives you full control over colors and patterns.

Both methods come straight from Microsoft’s own support article. Below are the steps for each, plus fixes for the cases where simple striping breaks: header rows, filtered data and Excel for the web. Menu names below match current Microsoft documentation as of November 2026, but Microsoft does rename ribbon tabs from time to time.

Which method should you use?

If you just want readable stripes on a data list, use a table. If you need the range to stay a normal range, or you want a non-standard pattern, use conditional formatting.

Method 1: Format the range as a table

1. Select the cells you want to stripe, including the header row.
2. Go to Home > Format as Table.
3. Pick a table style that shows alternating row shading.
4. Confirm the range in the dialog. Tick My table has headers if the first row holds column names.

According to Microsoft, the Banded Rows box in the Table Style Options group is selected by default, so the stripes appear immediately. The banding continues automatically if you add or delete rows in the table.

Switch to banded columns

Select any cell in the table, open the Table Design tab, clear Banded Rows and select Banded Columns.

Keep the stripes but drop the table

If you don’t want table features such as filter buttons and structured references, right-click the table, choose Table, then Convert to Range. The shading stays, but Microsoft notes that banding will no longer extend automatically as you add rows or columns.

Method 2: Use a conditional formatting formula

1. Select the range you want to stripe, for example A2:F200.
2. Go to Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. Enter =MOD(ROW(),2)=0.
5. Click Format, open the Fill tab, pick a color and click OK twice.

How the formula works

ROW() returns the row number of each cell, and MOD(number,2) returns the remainder after dividing by 2. That remainder alternates between 0 and 1 from row to row, so =MOD(ROW(),2)=0 matches even-numbered rows. To shade odd rows instead, use =MOD(ROW(),2)=1.

Exceljet also points out that =ISEVEN(ROW()) (or =ISODD(ROW())) does the same job and reads a little more plainly.

Start the pattern from your first data row

Because ROW() uses the worksheet row number, the stripes depend on where your data happens to start. If you want the first data row to always be shaded (or always unshaded) no matter where the range begins, anchor the count to that row. For a range starting at A2:

=MOD(ROW()-ROW($A$2)+1,2)=0

This makes the first data row count as row 1, the next as row 2, and so on.

Other patterns

• Every third row: =MOD(ROW(),3)=0. Change 3 to any number for every nth row.
• Every other column: =MOD(COLUMN(),2)=0, as given in Microsoft’s article.
• Two colors instead of one color plus white: Add a second rule with =MOD(ROW(),2)=1 and a different fill.

Fixing stripes on filtered data

A plain MOD(ROW()) rule breaks when you filter or hide rows. Hidden rows keep their row numbers, so after filtering you can see two shaded or two unshaded rows next to each other.

The common fix, discussed in long-running MrExcel forum threads, is to count only the visible rows with SUBTOTAL. For a range starting in row 2, with column A always filled in:

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

How it works:

• SUBTOTAL(103, ...) counts non-blank cells but ignores rows that are hidden or filtered out.
• The range $A$2:$A2 has a locked start and a relative end, so it grows by one row as the rule moves down. That gives a running count of visible rows.
• MOD(...,2)=0 then shades every second visible row.

Some forum posts use function number 3 instead of 103. Function 3 also counts non-blank cells; the difference is how manually hidden rows are treated, so 103 is the safer choice if you hide rows by hand as well as filter. One caveat reported by forum users: the count column must not contain blanks, or the stripes will skip. Point the formula at a column that is always populated, such as an ID or date column.

If you don’t need a plain range, converting the data to a table (Method 1) avoids this problem altogether, because table banding re-applies to the visible rows.

Excel for the web

Microsoft’s support article states that you can’t create custom conditional formatting rules to shade alternate rows or columns in Excel for the web, but that table banding works there. That means the reliable browser option is Method 1: format the range as a table, then use the Table Design tab’s style options to switch between banded rows and banded columns. Excel for the web gets feature updates regularly, so if the formula option appears in your version, Method 2 should behave the same way it does in the desktop app.

Troubleshooting

• Nothing changes after adding the rule. Check Home > Conditional Formatting > Manage Rules and make sure the rule’s Applies to range covers your data.
• Stripes start on the wrong row. Swap =0 for =1, or use the anchored formula above.
• Existing fill colors hide the stripes. Conditional formatting overrides manual fill where the rule is true, but your manual fill still shows on the other rows. Clear manual fill first (Home > Fill Color > No Fill) for a clean pattern.
• New rows aren’t striped. Extend the rule’s Applies to range, or use a table so banding grows with the data.
• Table stripes vanished. Select a table cell and confirm Banded Rows is still ticked on the Table Design tab.

Quick reference