Alternating Color Rows in Excel: Three Easy Methods

How-to basics · Updated November 20, 2026

To create alternating color rows in Excel, format your data as a table and choose a banded style, or use conditional formatting to control the colors and range. For a small, unchanging list, you can also fill alternating rows manually. The table option is quickest and adjusts as you add records; conditional formatting is more flexible.

What You’ll Need

  • Excel for Microsoft 365 on Windows, or a version of Excel with Tables and Conditional Formatting.
  • A worksheet containing data arranged in rows and columns. If you plan to turn the range into a table, include a header row, such as Name, Date, and Status.
  • A preferred color or table style, if you want to use something other than Excel’s defaults.

These instructions use Excel for Microsoft 365 on Windows. Excel for Mac has similar table and conditional-formatting tools, although some ribbon labels and dialog layouts can vary. Excel for the web also supports tables and conditional formatting, but its ribbon may show fewer options than the desktop app.

Step-by-Step

  1. Use a formatted table for automatic banded rows

    This is usually the best choice for a list that may grow or change. Excel applies a style to the table and can keep the alternating pattern when you add rows.

    1. Click any cell in your data. Check that the range is one continuous block, with one header row and no completely blank rows or columns splitting the list.
    2. On the ribbon, select Insert > Table. Confirm that the range in the dialog covers your data. If the first row contains column names, leave My table has headers checked. If it does not, uncheck the box so Excel does not treat the first record as a header.
    3. Select OK. Click inside the new table to display the Table Design tab. In Table Style Options, make sure Banded Rows is checked. If you do not see the alternating effect, also confirm that a table style with visible row shading is selected in the table styles gallery.
    4. Choose a different style if you want another color. On Windows, use the table styles gallery on Table Design; expand the gallery to see more choices. On Mac, select the table and look for the table design or table style controls on the ribbon. In Excel for the web, select the table to show its table options.
    5. Test the pattern by entering a new record in the row directly below the table. Excel generally expands the table when you type in the adjacent row, and the style continues. If it does not expand, use Table Design > Resize Table on Windows to include the new row, or check that there is no content or blank separator blocking the expansion.

    A table also provides features such as filter buttons and structured references. If you only want the colors and do not want table behavior, use the conditional-formatting method below instead. You can usually change a table back to a normal range from the table design controls, but doing so removes table features; decide whether those features are useful before converting it.

  2. Use conditional formatting for a specific range or custom colors

    Conditional formatting is useful when you want to color only a selected area, need to preserve an ordinary range, or want the first data row to have a particular color. The rule below shades the first row in the selected range, then every other row after it.

    1. Identify the exact data area to format. For example, suppose the headers are in row 1 and the records occupy A2:F20. Select only A2:F20, not the header row, unless you intentionally want the header included in the alternating pattern.
    2. On the ribbon, choose Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
    3. With records in A2:F20 and the first data row at A2, enter =MOD(ROW()-ROW($A$2),2)=0. The rule evaluates as TRUE for rows 2, 4, 6, and so on within the selected range. The dollar signs keep the reference to the first data row fixed while Excel evaluates the rule on each row.
    4. Select Format, open the Fill tab, and choose a light background color. Select OK to close the Format Cells dialog, then select OK again to create the rule.
    5. Check the rows in your selected range. Row 2 should have the chosen fill, row 3 should not, and the pattern should repeat down the list. To see or adjust the affected range, go to Home > Conditional Formatting > Manage Rules. Check the Applies to box and make sure it refers to the intended cells.
    6. If the first data row is not row 2, change the anchor in the rule to the address of the first data cell in the range. For example, if records start in A5 and the selected range is A5:F30, use =MOD(ROW()-ROW($A$5),2)=0. The relative offset makes the first selected row TRUE regardless of its worksheet row number.

    On Mac, use the Home tab’s Conditional Formatting controls and choose a new rule based on a formula; dialog wording or placement can differ slightly. Excel for the web can create and edit many conditional-formatting rules, but if you cannot find the formula-based rule option in your current web interface, open the workbook in the desktop app.

  3. Fill alternating rows manually for a small, fixed list

    Manual shading is suitable for a short list that will not be sorted, expanded, or frequently edited. It is not the best choice for a live tracker because the pattern will not reliably update as rows move or are inserted.

    1. Select the cells in the first row you want shaded, such as A2:F2. Selecting only the data columns prevents the fill from extending across the entire worksheet row.
    2. On the Home tab, open the Fill Color menu in the Font group and choose a color.
    3. Select the next row to shade after leaving one unfilled. For example, if you shaded A2:F2, select A4:F4 and then A6:F6. On Windows, hold Ctrl as you select additional nonadjacent ranges; on Mac, hold Command. Select the Fill Color you used to apply it to all the selected rows.
    4. Review the bottom and right edges of your data to confirm that the shading covers the same columns on every chosen row. If selecting multiple separate ranges is awkward in your version, apply the fill to each row individually.

Common Mistakes

  • Including the header in the pattern by accident: If you select the header along with the data for conditional formatting, the first shaded row may be the header rather than the first record. Select the data rows only, or create a separate header style.
  • Applying the conditional-formatting rule to the wrong range: A correct formula will still appear not to work if Applies to excludes some columns or rows. Select the intended range before creating the rule and verify it in Manage Rules afterward.
  • Using an anchor for a different starting row: A rule anchored to A2 will not make row 5 the first shaded row if the formatted range begins at row 5. Set the anchor to the first row of the range so the pattern starts where you expect.
  • Expecting manual fills to adjust automatically: Direct fill color is attached to cells, not to a rule about row position. Sorting, inserting, or deleting records can produce two neighboring rows with the same color.
  • Confusing a table style with cell fill: A table’s banded appearance comes from its style and the Banded Rows option. Applying a separate fill to individual table cells can make the style look inconsistent or obscure the banding.

Tips

  • Use a pale fill with dark text for readability, especially if people may print the worksheet in grayscale. Avoid relying on color alone to convey status; include a label or symbol when the distinction matters.
  • For a sortable or growing list, prefer a table. When you sort a table, its records and formatting move together, and new table rows normally inherit the style.
  • For conditional formatting, select the full width of the records before creating the rule. A single row-based rule can then shade all selected columns together.
  • If you want the second data row shaded instead of the first, change the ending test in the formula from =0 to =1. Keep the same first-row anchor and check the result before applying the pattern to a large range.
  • Use Home > Conditional Formatting > Manage Rules to review rules when several formats are layered on the same cells. On Windows, the dialog may let you choose whether to display rules for the current selection or the whole worksheet.
  • Before applying a table style to an existing report, consider whether table filter buttons and table behavior are appropriate. If the range is part of a carefully arranged print layout, conditional formatting may be less disruptive.

Troubleshooting

The rows all have the same color: For a table, click inside it and confirm that Banded Rows is selected and that the chosen style visibly distinguishes alternate rows. For conditional formatting, open Manage Rules and check both the formula and its Applies to range. A rule may be present but target only one row or the wrong columns.

The pattern starts on the wrong row: Check whether your selected range includes the header or begins on a different row than the formula’s anchor. The anchor should identify the first row you want included in the pattern. With the conditional-formatting example, A2 is the first data row, so that row is shaded; using A5 as the anchor makes row 5 the first shaded row when the range begins there.

Only one cell changes color: When creating the rule, select the entire rectangular data range first, not just the top-left cell. Then check Applies to in Manage Rules. If the range is correct but some cells still look different, another conditional-formatting rule or direct fill may be overriding the appearance; review other rules and clear unwanted direct fill if appropriate.

New rows do not get the same formatting: A table should extend when you type directly below it, provided there is no obstruction. If it does not, resize the table to include the new records. A conditional-formatting range does not necessarily expand when you type beyond its current boundary, so edit Applies to to cover the added rows or apply the rule to a suitably larger range. Manual coloring must be extended by hand.

Colors disappear after sorting or editing: Manual fills can become mismatched after sorting or inserting rows. Convert the data to a table and use its banded style, or use a conditional-formatting rule based on row position. If the rule remains but the display is unexpected, inspect the rule order and range in Manage Rules.

Frequently Asked Questions

What is the fastest way to alternate row colors in Excel?
For most lists, select a cell in the data and choose Insert > Table. Confirm the header setting, choose a table style, and make sure Banded Rows is enabled on the Table Design tab.

Can I alternate colors without turning my data into a table?
Yes. Select the data range and create a formula-based conditional-formatting rule from Home > Conditional Formatting > New Rule. This colors the range without adding table features such as filter buttons.

Can I use more than two colors?
Yes. Create additional conditional-formatting rules that identify different row positions and assign each a fill color, or manually shade selected rows. For a simple two-color band, a table style or one conditional-formatting rule is easier to maintain.

Will alternating colors stay when I sort the data?
Table banding follows the table records when you sort. A conditional-formatting rule based on row position colors whichever records occupy those positions after sorting. Manual fills may move with cells, but can create an uneven pattern, so avoid them for data you sort often.

Why are the stripes missing on blank rows?
A conditional-formatting rule only affects its Applies to range, and a table may not include blank rows below it. Extend the relevant range or table if you want those rows formatted. If blank separator rows are intentional, consider whether including them would make the list harder to read or sort.

Can I remove the colors later?
For a table, choose a plain style or turn off Banded Rows in the table options. For conditional formatting, use Home > Conditional Formatting > Clear Rules and choose whether to clear rules from the selected cells or the whole sheet. For manual fills, select the cells and choose No Fill from the Fill Color menu.

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.