How to Change Every Other Row Color in Excel Automatically

How-to basics · Updated November 20, 2026

To change every other row color in Excel, select your data and use either a table style for quick banding or a conditional formatting rule for more control. Both methods make long lists easier to scan; conditional formatting is especially useful when you want a particular color or need to shade a specific range without turning it into a table. In Excel for Microsoft 365 on Windows, the steps below show both approaches. Decide first whether your data should behave like a table, and save the workbook if it contains important formatting you may want to preserve.

What You’ll Need

  • A worksheet with the rows you want to format. Include column headings if you plan to convert the range into a table.
  • For the conditional formatting method, know the first and last columns and rows of the range to shade, such as A2:F100.
  • Optional: a preferred fill color. Excel’s built-in table styles offer ready-made alternating colors, while a conditional formatting rule lets you choose a fill.

Step-by-Step

  1. Choose a method and identify the range

    Use a table if the data is a list that you may sort, filter, or expand later. Table banding automatically follows the table rows as records are rearranged or new records are added. Use conditional formatting if you only want to color a chosen block of cells, if the range is not suitable as a table, or if you want the color to alternate by worksheet row position.

    Before selecting anything, decide whether the column headings should be colored. For a heading in row 1 and data beginning in row 2, select only the data range for conditional formatting if you want the headings to remain unshaded. For a table, include the headings in the range so Excel can use them as column names.

  2. Option A: apply alternating colors with a table

    Click a cell inside the data, or select the full range including its headings. On the ribbon, choose Insert > Table. Check that the range shown in the dialog is correct. If the first row contains field names such as Date or Product, select My table has headers, then choose OK. Excel creates the table and displays the contextual Table Design tab.

    On Table Design, find Table Style Options and make sure Banded Rows is checked. Then open the Table Styles gallery and choose a style with the alternating color you want. If you do not see the gallery choices, select its More button to expand them. The styles preview as you point to them, so you can compare light and dark options before committing.

    If the range has no headings, consider adding clear column names first. You can create a table without treating the first row as headers, but Excel supplies generic names such as Column1, which are less useful for filtering and formulas. Check the selected range carefully: including an unrelated note or a blank row can make that content part of the table.

  3. Option B: use conditional formatting for a selected range

    Select the cells you want to format. For example, select A2:F100 to band six columns of data from row 2 through row 100. Do not include a header row unless you also want the rule to format it. Keep the selection in place, then choose Home > Conditional Formatting > New Rule.

    In the New Formatting Rule dialog, select Use a formula to determine which cells to format. For the selected range A2:F100, with row 2 intended to receive the first colored stripe, enter this rule:

    With the selected range A2:F100 and row 2 as the first row to shade, use =MOD(ROW()-ROW($A$2),2)=0.

    This formula returns TRUE for row 2, row 4, row 6, and so on, and FALSE for the intervening rows. ROW returns the current worksheet row number; subtracting the row number of the anchored starting cell makes the pattern begin at the top of your selected data. The dollar signs keep A2 as the fixed starting point as Excel evaluates the rule across the range. If your selection begins somewhere else, replace $A$2 with the top-left cell of your selected range. For example, for a range beginning at C5, use $C$5 in the rule.

    Choose Format, open the Fill tab, select a color, and confirm with OK. Confirm again in the New Formatting Rule dialog. Excel applies the fill to every other row across the selected columns. If the first row of the selected range should remain uncolored and the next row should be colored, change the final part of the formula to =MOD(ROW()-ROW($A$2),2)=1, keeping the same A2:F100 selection and starting cell.

  4. Check the rule range and result

    Look at the first few rows to confirm that the stripes start where you expect and span all the intended columns. If you need to review or adjust the rule, choose Home > Conditional Formatting > Manage Rules. In the Show formatting rules for list, choose the current selection or the worksheet, then inspect the rule and the Applies to box. For the example, the range should be =$A$2:$F$100. Correct it if it excludes columns or rows you meant to include, then choose Apply and OK.

  5. Save and consider what happens when the list changes

    Save the workbook after checking the pattern. A table style normally extends as you add rows directly below the table, and its banding follows records when you sort. A conditional formatting rule only applies to its specified range unless you extend the Applies to range. If you expect regular additions, select a larger range in advance or update the range later through Manage Rules.

Common Mistakes

  • Starting the pattern on the wrong row: A formula based only on odd or even worksheet row numbers can shade an unexpected stripe when the range begins on a different row. The offset formula above starts relative to the top-left cell you specify.
  • Selecting only one column: If you select A2:A100, only column A receives the fill. Select the full width of the records, such as A2:F100, to make each stripe span the row of data.
  • Including headings by accident: Conditional formatting treats the heading like any other row if it is in the selected range. Start at the first data row when headings should stay separate.
  • Using a table when you only want a visual effect: A table adds table behavior, including filter buttons and structured references. That is useful for lists, but may not be wanted for a small layout or report. Choose conditional formatting for a visual-only range.
  • Assuming a table style changes a manually filled cell: Direct cell formatting can take precedence visually over the table style. If one row refuses to match, check for a manually applied fill before changing the banding setting.
  • Overwriting an existing rule: A cell can be affected by more than one conditional formatting rule. Editing an existing rule without checking its range or priority may alter formatting elsewhere. Review the rules in Manage Rules before removing or replacing them.

Tips

  • Keep the stripe subtle. A pale fill with dark text is usually easier to read than a saturated color. Check contrast with any font colors already in the worksheet.
  • Use a table for lists that grow. Tables make banding and filtering easier to maintain. When you type immediately below a table, Excel commonly expands it; verify that the new row is inside the table if the style does not continue.
  • Use conditional formatting for a fixed report area. It leaves the range as ordinary cells and lets you change the fill later by editing the rule. It is also convenient when only selected columns should be striped.
  • Distinguish row-position stripes from record stripes. The conditional formatting rule colors alternating worksheet positions. After sorting, the colors remain on alternating positions, not attached to a particular record. Table banding instead follows the table’s displayed record order, so the pattern is restored after sorting.
  • Set a future-proof range thoughtfully. You can apply a conditional formatting rule to more rows than are currently populated, such as A2:F500, if the list is likely to grow. Avoid formatting entire columns unnecessarily in a large or rule-heavy workbook, since broad rules can make management harder.
  • Change a table’s appearance without rebuilding it. Select a table cell, open Table Design, and choose another table style. To remove the stripe effect while keeping the table, clear Banded Rows; the table and its data remain in place.

Troubleshooting

No rows change color: For conditional formatting, revisit Home > Conditional Formatting > Manage Rules. Confirm the formula is entered in the formula-rule box, the fill is set under Format, and the Applies to range covers the cells you selected. Make sure the formula begins with an equals sign and that its anchor points to the first cell in the range. For a table, click inside it and check that Banded Rows is selected and a table style is applied.

The wrong rows are shaded: Check which row should receive the first stripe. In the offset formula, the reference inside ROW must identify the top-left cell of the range, including the correct column and row. If you chose a range starting in A2 but used $A$1, the first colored row will shift. You can switch the ending comparison from 0 to 1 to reverse which of the alternating rows gets the fill.

Only part of each row is colored: Open Manage Rules and expand the Applies to range so it includes every intended column. If the rule has multiple separate ranges, check each one; the formula’s anchored start should correspond to the top-left cell for the intended pattern.

Some cells keep a different color: A direct fill or another conditional formatting rule may be overriding the stripe. First inspect the cell’s fill and the rules listed in Manage Rules. If you use Home > Clear > Clear Formats, be aware that it removes other direct formatting too, including number formats and font styling, so use it only on cells where that is acceptable. You can instead change or remove just the conflicting rule.

New rows are not striped: A conditional formatting rule may stop at its original last row. Extend Applies to to include the new rows. For a table, confirm the new data was added as part of the table rather than separated from it by a blank row or another block of content.

Mac or Excel for the web: The same two approaches are available in current Excel for Mac, though dialog layouts and ribbon labels can vary slightly by release. Look under Home for Conditional Formatting and choose a formula-based rule. Excel for the web also supports table styles and conditional formatting, but some rule-management controls may be arranged differently; use Manage Rules when available or open the workbook in the desktop app for detailed rule editing.

Frequently Asked Questions

Can I color every other row without converting the data into a table?

Yes. Select the cells and create a formula-based conditional formatting rule. This changes only the appearance of the selected range and does not add table filters or structured table behavior.

Can I use a different color for each alternating row?

Yes. A single rule colors one set of alternating rows. Create a second conditional formatting rule for the other set, using the same starting cell but changing the final comparison from 0 to 1, then choose a different fill. Keep both rules applied to the same range.

Will the colors stay with rows when I sort?

With a table style, banding follows the table’s displayed order after sorting. A conditional formatting formula based on row position colors alternating positions instead, so the stripe may appear on a different record after a sort. Use a table when you want standard banding for a sortable list.

How do I remove alternating row colors?

For a table, select a cell in it and clear Banded Rows on Table Design, or select a different style. For conditional formatting, open Manage Rules, select the relevant rule, and delete it or change its format. Deleting a rule does not delete the cell values.

Can I apply the color to the whole worksheet?

You can select a large range before creating the rule, but coloring a whole worksheet can make it harder to read and manage. For a specific dataset, apply the rule only to its used columns and rows. If it will grow, allow a practical number of extra rows or expand the rule when needed.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.