How to Shade Every Other Row in Excel (Two Ways)

The quickest way to shade every other row in Excel is to turn the range into a table. Select your data, press Ctrl+T, click OK, and Excel applies a banded style straight away. If you would rather keep the plain range, use Home > Conditional Formatting > New Rule, pick the formula option, and enter =MOD(ROW(),2)=0.

Both routes produce the same striped look, but they behave very differently once you start sorting, filtering or adding rows. Picking the wrong one is why banding sometimes stops lining up a week later.

One thing to know before you start: converting a range to a table is not purely cosmetic. It adds filter buttons, changes how formulas refer to the columns, and brings a Table Design tab into the ribbon. That is usually welcome, but it is a real change to the sheet.

Two ways to shade every other row in Excel

A table ties the stripes to the data. Excel re-lays the banding as rows are added or deleted, so the pattern looks after itself.

A conditional formatting rule ties the stripes to the worksheet's row numbers instead. Row 2 is shaded because it is row 2, not because of what sits in it. That is more predictable in some ways and more brittle in others, as the sections below explain.

Route 1: Format as Table

1. Select the range you want banded, including the header row.

2. On the Home tab, in the Styles group, click Format as Table. Pick any style from the Light, Medium or Dark galleries. Most of them band the rows by default.

3. In the Format as Table dialog, confirm the range and tick the header box if your selection has one, then click OK.

4. Click any cell inside the new table. A Table Design tab appears in the ribbon — on a Mac it is simply called Table.

5. In the Table Style Options group, check that Banded Rows is ticked. It is on by default. Clearing it removes the stripes and leaves the table intact.

Ctrl+T does the same job in one keystroke: it creates the table with banded rows already on, and you can change the style afterwards from the gallery on the Table Design tab.

If the list is long, this is also the moment to freeze the header row so the column names stay visible as you scroll through the stripes.

Route 2: A conditional formatting rule

Use this when you want the shading without the rest of the table machinery.

1. Select the cells to format, or click Select All to cover the whole sheet.

2. On the Home tab, in the Styles group, open the arrow next to Conditional Formatting and choose New Rule.

3. Under Select a Rule Type, choose Use a formula to determine which cells to format.

4. In the formula box, enter Microsoft's formula: =MOD(ROW(),2)=0. That shades the even-numbered rows. Swap it for =MOD(ROW(),2)=1 if you want the odd ones instead, and =MOD(COLUMN(),2)=0 if you meant columns. =ISEVEN(ROW()) does the same thing as the first formula and reads more plainly.

5. Click Format, open the Fill tab, choose a colour, and click OK.

6. Check the preview, then click OK to apply the rule. To change it later, go to Home > Conditional Formatting > Manage Rules.

Keep the fill light. A conditional format sits behind the cell text, and a saturated colour makes half your rows harder to read than the ones you left alone.

What this does not do

Neither method shades rows based on what is in them. If you want rows highlighted by value — overdue dates, negative numbers — that is a different conditional formatting rule, not banding.

The formula rule does not follow your data when it moves. Because it reads the worksheet row number, sorting shuffles the records underneath stripes that stay exactly where they were. A table's banding does follow the rows, which is the main practical argument for Route 1.

The formula rule also only covers the range you applied it to. Rows typed below that range stay unshaded until you widen the rule's Applies to box. A table extends itself.

Filtering is where sources disagree. A plain =MOD(ROW(),2)=0 rule counts hidden rows, so filtered results can show two shaded rows side by side — that much follows directly from the formula. Some guides say table banding re-stripes the visible rows cleanly after a filter; others report it looking just as inconsistent. Microsoft's own documentation does not address it. Apply a filter and look, rather than assuming.

And a manual fill colour beats a conditional format. If some rows were coloured by hand earlier, clear that fill first or the rule will appear to skip them.

Common questions

Can I keep the stripes but lose the filter buttons? Yes — on the Table Design tab, clear the Filter Button checkbox in Table Style Options. The table and its banding stay.

How do I get rid of the table but keep the look? Click inside it, then on the Table Design tab choose Convert to Range in the Tools group. The current shading stays as static fill, but it stops updating as you add rows. Removing a table style, by contrast, does not remove the table.

Can I shade two rows at a time? Use a wider modulus in the rule — =MOD(ROW()-2,4)<2 gives bands two rows deep. Adjust the 2 and the 4 for other band heights.

If the option isn't there

Check which Excel you are in. The ribbon group names above are the desktop app's. Excel for the web carries both Format as Table and conditional formatting, but the dialogs are laid out differently and some style galleries are shorter.

If Format as Table is greyed out, the sheet is probably protected, or the selection spans merged cells. Unmerge them and try again — tables will not accept merged cells at all.

If the formula rule applies to nothing, check that you entered it with the leading equals sign and that Applies to covers the range you selected. It is also worth tidying the data before you format it; splitting a full-name column, for instance, is much easier before a table has taken hold of the column references.