How To Alternate Row Colors in Excel

Do your eyes ever get lost while navigating rows of data in Excel? Alternating row colors, also called banded rows or zebra striping, fix that in about ten seconds.

Answer: To alternate row colors in Excel, select your data and press Ctrl + T to turn it into a Table, which bands the rows automatically. For banding without a Table, go to Home, Conditional Formatting, New Rule, Use a formula, and enter =MOD(ROW(),2)=0, then pick a fill color.

Last updated: August 19, 2026

What this guide covers:

• Which method should you use, a table style or conditional formatting?
• Method 1: alternating row colors with a built-in table style
• Method 2: alternating row colors with conditional formatting
• How do you shade every other row without shading the header?
• How do you keep the banding correct when you sort or filter?
• How do you shade in blocks of two, three, or more rows?
• How do you change the color when the value in a column changes?
• How do you create a custom table style in Excel?
• How do you alternate column colors instead of rows?
• How do you remove alternating row colors?
• Why did my banded rows disappear?

Which method should you use, a table style or conditional formatting?

Both give you striped rows. They behave completely differently the moment your data starts moving, which is what actually decides it.

Use a table style unless something about your sheet makes a real Table unworkable. It is the only option that keeps banding correct for free as data is added, sorted, and filtered.

Reach for conditional formatting when you cannot convert to a Table, when you need a specific brand color, or when you want stripes wider than one row.

Method 1: Alternating row colors by applying a built-in table style

The built-in table styles are the fastest route to banded rows and the one Microsoft treats as the default in its guide to formatting an Excel table.

1. Select the range of cells you want to format, including the header row.

2. Go to the "Home" tab on the Ribbon.

3. Click "Format as Table" and choose a style with alternating row colors from the gallery. Confirm that "My table has headers" is ticked so your first row does not become data.

4. Your selected range now has banded rows.

Once the Table exists, a Table Design tab appears whenever a cell inside it is selected. The Banded Rows checkbox in the Table Style Options group is the on and off switch for the stripes, and Banded Columns next to it does the same job vertically.

Every new row you type at the bottom of a Table joins the Table and gets the correct stripe on its own. That is the whole reason this method wins.

Method 2: Alternating row colors with conditional formatting

With a conditional formatting rule you can band a plain range without converting anything, and you can use any color you like.

1. Select the range of cells you want to format. Start at the first data row, not the header.

2. Go to the "Home" tab on the Ribbon and click "Conditional Formatting."

3. Select "New Rule" and choose "Use a formula to determine which cells to format."

4. Enter =MOD(ROW(),2)=0 to shade every even-numbered row.

5. Click "Format" to choose your color from the "Fill" tab, then click OK twice.

Reading the formula out loud makes it obvious. ROW() returns the row number of the cell being tested, MOD divides that by 2 and hands back the remainder, and the remainder is 0 on even rows. Swap =0 for =1 to shade the odd rows instead.

The rule is evaluated once per cell, so it applies to whatever range you selected and nothing outside it. Extending the stripes later means editing the rule's Applies to range, not writing a new rule.

Microsoft covers both routes in its note on applying shading to alternate rows or columns.

How do you shade every other row without shading the header?

This is the most common complaint about the MOD method, and it is usually caused at selection time rather than by the formula.

Two fixes, depending on what you want:

• Leave the header out of the range. If your header is row 1 and data starts in row 2, apply the rule to A2:F500 rather than A1:F500. The header keeps whatever formatting you gave it.
• Start the stripes on your first data row regardless of where it sits. If data starts in row 5, =MOD(ROW(),2)=0 will shade rows 6, 8, 10 and leave your first row unshaded. Use =MOD(ROW()-5,2)=0 instead, subtracting your first data row number, and the striping starts exactly where the data does.

Subtracting the first data row number from ROW() is the general fix for "my stripes start on the wrong row". It re-bases the count so row one of your data is row zero of the pattern.

How do you keep the banding correct when you sort or filter?

Here is the trap nobody mentions until it bites. =MOD(ROW(),2)=0 is tied to the sheet's absolute row numbers, not to your data.

Sort the range and the stripes stay exactly where they were while the data slides underneath them. That is usually fine, since the point is readability rather than labelling. Filtering is the real problem. Hidden rows still count, so if rows 4, 5, and 6 get filtered out you can end up with two shaded rows sitting directly next to each other and the stripe pattern falls apart.

The fix is to count only the rows you can actually see, which is what SUBTOTAL does. For data starting in row 2:

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

The 103 tells SUBTOTAL to run COUNTA while ignoring hidden rows. The reference $A$2:$A2 is deliberately half-locked, so as the rule walks down the range it counts a growing block of visible cells and returns 1, 2, 3 and so on for the visible rows only. Filter anything you like and the stripes re-lay themselves cleanly.

If your data will ever be filtered, use the SUBTOTAL version or use a real Table. The plain MOD(ROW()) rule looks broken the first time somebody applies a filter.

Column A in that formula just needs to be a column that is never blank within your data. If column A has gaps, point it at an ID or date column that does not.

How do you shade in blocks of two, three, or more rows?

Single-row stripes get busy on dense sheets. Shading in pairs or triples reads better and takes one change to the formula.

The block versions follow one shape: =MOD(ROW()-firstRow, blockSize times 2) < blockSize. Change the 2 in ROW()-2 to whatever row your data actually starts on.

If you would rather not write formulas at all, a custom table style can do blocks too. Its Stripe Size setting takes a number of rows, which is covered further down.

How do you change the color when the value in a column changes?

This is the one people usually want once they have seen it. Instead of striping every other row, the color flips each time the value in a key column changes, so all the rows for one customer, region, or invoice share a shade and the next group gets the other.

Regular striping cannot do this, because it counts rows rather than groups. Use a helper column.

1. Sort your data by the column you want to group on. This will not work on unsorted data, because a group has to be contiguous.
2. In a spare column, say H, put 1 in the first data row, for example H2.
3. In H3 enter =IF(A3=A2,H2,H2+1) and fill it down. Column A here is the column you are grouping by. The counter holds still while the value repeats and ticks up by one whenever it changes.
4. Select your data range, create a new conditional formatting rule with =MOD($H2,2)=0, and choose a fill.

The dollar sign in $H2 matters. It locks the column so every cell across the row reads the same counter, while the relative row number lets the rule step down the list.

Hide column H rather than deleting it. The rule reads that column live, so removing it takes the banding with it.

How to create custom table styles in Excel

Excel's built-in table styles don't allow for direct customization of row colors beyond the predefined alternating patterns. However, you can create a custom table style to apply a different pattern or set of row colors to your table.

1. Create a custom table style: Go to the table style gallery under "Format as table" and select "New Table Style" at the bottom.

2. Customize the row colors: Give your style a name in the New Table Style dialog box. To set custom colors for specific rows, click "First Row Stripe" or "Second Row Stripe."

• Choose "Format" and select your desired fill color, font, or border style for these specific rows.
• Set the "Stripe Size" to the number of rows each band should span. Setting it to 2 gives you two shaded rows followed by two clear ones, without any formula.

3. Apply your custom table style: Click "OK" to save your new style. It appears under the "Custom" section of the Table Styles gallery. Click it to apply.

Custom table styles are saved into the workbook they were created in, not into Excel. To reuse one, copy a sheet containing the style into the new file, or save the file as a template.

How do you alternate column colors instead of rows?

Same idea, one word different in the formula. COLUMN() replaces ROW().

1. Select the range: Open your Excel file and select the range of cells you want to stripe vertically.

2. Open conditional formatting: Go to the "Home" tab on the Ribbon and click "Conditional Formatting."

3. Create a new rule: Choose "New Rule," then select "Use a formula to determine which cells to format."

4. Enter the formula: Enter =MOD(COLUMN(),2)=0. This checks the column number and shades every even-numbered column. Use =1 to shade the odd ones instead.

• Set the formatting: Click "Format", choose the fill color, and click OK.

• Apply the rule: Click OK again. Every second column in your range is now colored.

If your data is already in a Table, skip the formula entirely. Tick Banded Columns and untick Banded Rows on the Table Design tab.

How do you remove alternating row colors?

Removal depends on which of the three sources put the color there, which is why one method never works for everybody.

• An Excel Table: click any cell in it, go to Table Design, and untick Banded Rows. To remove the whole style, open the Table Styles gallery and choose Clear at the bottom. To get rid of the Table itself, use Table Design, Convert to Range. See how to remove a table in Excel for the full walkthrough.
• Conditional formatting: go to Home, Conditional Formatting, Clear Rules, then either From Selected Cells or From Entire Sheet. To keep the rule but see it off, use Manage Rules and delete only the banding rule.
• A manual fill somebody applied by hand: select the range and set Home, Fill Color, No Fill.

Converting a Table to a range keeps the stripes as static fill. If you want the color gone as well, clear the table style first and convert second.

Why did my banded rows disappear?

Final thoughts on how to alternate row colors in Excel

Two methods, one decision. If the data can live in a real Table, press Ctrl + T and you are finished, with banding that survives every sort, filter, and new row you throw at it. If it cannot, a conditional formatting rule gives you total control over the color and the pattern, as long as you remember that it counts sheet rows rather than your data, and reach for the SUBTOTAL version the moment a filter is involved.

You can visit our homepage for more easy-to-follow how-to and step-by-step guides. Check the links in related articles for further details about Excel and Google Sheets templates.

Frequently asked questions about alternating row colors in Excel

Can I alternate row colors without converting my data to a table?

Yes. Use conditional formatting instead. Select your data, go to Home, Conditional Formatting, New Rule, choose Use a formula to determine which cells to format, and enter =MOD(ROW(),2)=0. Your range stays a plain range, with no filter arrows or structured references added.

Will the alternating colors adjust if I add or remove rows?

Inside an Excel Table, yes. The banding extends automatically to new rows and re-lays itself when rows are deleted. A conditional formatting rule only covers the range it was applied to, so rows added below that range stay unshaded until you extend the rule's Applies to range.

Why does my banding break when I filter the data?

Because =MOD(ROW(),2)=0 counts absolute sheet row numbers, including the rows the filter has hidden. Use =MOD(SUBTOTAL(103,$A$2:$A2),2)=0 instead, which counts only visible rows, or convert the range to a Table.

How do I shade every other row without shading the header?

Apply the rule to the data range only, starting at your first data row rather than row 1. If the stripes then start on the wrong row, subtract your first data row number inside the formula, for example =MOD(ROW()-2,2)=0 when data begins in row 2.

Can I use more than two colors for alternating rows?

Built-in table styles are limited to two stripe colors. Conditional formatting is not. Add one rule per color using a larger divisor, for example =MOD(ROW(),3)=0 for the first color and =MOD(ROW(),3)=1 for the second, and set the rule order in Manage Rules.

How do I make the color change when the value in a column changes?

Sort by that column first, then add a helper column with =IF(A3=A2,H2,H2+1) filled down, which increments only when the value changes. Apply a conditional formatting rule of =MOD($H2,2)=0 to your data range and hide the helper column.

Related Articles

How to group Columns in Excel

How to Subtract in Google Sheets

How to Remove a Table in Excel