Excel Alternating Row Color: Table Styles and Formula Methods

To get alternating row colors in Excel, select your data and use Home > Format as Table, which applies banded rows automatically. If you don’t want a table, add a conditional formatting rule with the formula =MOD(ROW(),2)=0 (or =ISEVEN(ROW())) and pick a fill color.

Both methods are documented by Microsoft in Apply shading to alternate rows or columns in a worksheet. Below is how each one works, when to choose it, and how to handle the common snags. Menu names reflect Microsoft’s documentation as of October 2026 and may shift slightly between Excel versions.

Which method should you use?

In short: use a table if your data is a list you’ll filter, sort and grow. Use a formula rule if you want full control over the colors or need to keep the data as an ordinary range.

Method 1: Banded rows with a table style

This is the quickest route and the one Microsoft recommends first, including in its Excel for Mac article.

1. Select the range you want to shade. The cells can be empty or contain data.
2. On Windows, go to Home > Format as Table and pick a style. On a Mac, Microsoft’s steps use Insert > Table, then a style from the Table tab.
3. If your data has a header row, tick My table has headers and click OK.

Banding is on by default. To control it, click inside the table and use Table Style Options on the Table Design tab (Microsoft notes this tab is simply called Table on Mac):

• Banded Rows toggles the alternating row shading.
• Banded Columns shades alternate columns instead of, or as well as, rows.

The big advantage: when you add rows or columns to the table, the alternate shading is applied automatically.

Keeping the colors but dropping the table

If you only wanted the stripes, not the filter arrows, you can convert the table back to a normal range. According to Microsoft’s Convert an Excel table to a range of data:

1. Click any cell in the table.
2. On the Table Design tab, in the Tools group, choose Convert to Range (or right-click the table, choose Table > Convert to Range).
3. Confirm with Yes.

The colored rows stay, but they’re now plain cell formatting. You lose the sort and filter arrows, structured references in formulas turn into regular cell references, and, most importantly for this topic, new rows you add later won’t be banded automatically. If you need stripes on a growing plain range, use Method 2 instead.

Method 2: Conditional formatting with a formula

This approach shades rows based on their row number, so it works on any range and doesn’t turn your data into a table. The steps below follow Microsoft’s article and How-To Geek’s walkthrough.

1. Select the cells you want to band. To cover the whole sheet, click the Select All triangle in the top-left corner of the grid.
2. Go to Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. In Format values where this formula is true, enter: =MOD(ROW(),2)=0
5. Click Format, open the Fill tab, choose a color, and click OK twice.

Every even-numbered row in your selection is now shaded.

How the formula works

ROW() returns the row number of each cell being evaluated. MOD(ROW(),2) divides that number by 2 and returns the remainder: 0 for even rows, 1 for odd rows. The rule formats a cell whenever the formula returns TRUE.

Exceljet offers a slightly more readable equivalent, =ISEVEN(ROW()). Both give the same result. Exceljet notes that ISEVEN and ISODD aren’t available in Excel versions before 2007, which is why the MOD version is still common.

Useful variations

Two colors instead of one. Shading only even rows leaves odd rows white. If you want two distinct colors, add a second rule for odd rows with a different fill, as How-To Geek describes.

Groups of rows. The group formula comes from Exceljet’s Shade alternating groups of n rows. The -4 offsets for data that starts in row 5, and the 3 is the group size. Adjust both for your sheet.

Managing the rule later

To change the color, widen the range, or delete the stripes, go to Home > Conditional Formatting > Manage Rules. Make sure the dropdown at the top shows the right sheet or selection, otherwise the rule may not appear in the list.

Common problems and fixes

The stripes start on the wrong row

The formula looks at the real sheet row number, not the position inside your selection. If your header is in row 1 and data starts in row 2, =ISEVEN(ROW()) shades the first data row. If you’d rather leave the first data row white, switch to the odd-row version. You may also want to exclude the header row from the rule’s range so it keeps its own formatting.

Sorting and inserting rows

Because the rule is recalculated from row numbers, Ablebits points out that conditional-formatting banding stays intact when you sort, insert or delete rows in the covered range. Manual fill colors, by contrast, travel with the cells and quickly get out of order.

Filtering is worth testing on your own data: since the formula reads sheet row numbers, hiding rows can leave two shaded rows next to each other in the visible list.

Excel for the web

Format as Table and its Banded Rows option are the dependable choice in the browser. Microsoft’s support article has stated that you can’t create custom conditional formatting rules for alternate shading in Excel for the web, while third-party formula guides describe the same New Rule steps without separating desktop and web. Because Excel for the web changes often, check whether Use a formula to determine which cells to format appears under Conditional Formatting > New Rule in your browser. If it doesn’t, use a table style.

Quick recap

• Fastest: Home > Format as Table, then toggle Banded Rows on the Table Design tab.
• Most flexible: Home > Conditional Formatting > New Rule > formula =MOD(ROW(),2)=0, then choose a fill.
• Columns: Banded Columns in a table, or =MOD(COLUMN(),2)=0 as a rule.
• Don’t convert to range if you need new rows to be striped automatically.