How to Insert Alternating Color Rows in Excel Easily

To insert alternating color rows in Excel, select your data, choose Home > Format as Table, pick a style, and confirm whether your range has headers. Excel applies banded rows automatically, making records easier to scan; you can also use conditional formatting when you want more control over the range or pattern.

Before you start, decide whether the data should become an Excel table. Tables are usually the simplest choice because their row banding can extend as you add records. Conditional formatting is a good alternative for a range you do not want to convert into a table, or when the color pattern needs to follow a particular starting row.

What You’ll Need

Excel for Microsoft 365 on Windows, or an Excel version with tables and conditional formatting.
A worksheet containing the rows you want to format. Include column headings if your data has them, and check that the selected range does not include unrelated cells.
Optional: a preferred fill color or table style. Choose a light shade with enough contrast to keep text and numbers readable.

Step-by-Step

Select the data. Click any cell inside the contiguous range you want to format. Excel will usually detect the full block automatically. If it does not, select the complete range, including the headings if you have them. Avoid selecting entire worksheet columns unless the data really fills them; a focused range is easier to manage.
Turn the range into a table. On the ribbon, choose Home > Format as Table, then select a style with alternating row shading. You can also use Insert > Table, choose the range in the Create Table dialog, and then select a style from the Table Design tab. On Windows, Ctrl+T opens the Create Table dialog for the selected data.
Confirm the table range and headings. In the Create Table dialog, check that the displayed range covers all the intended columns and rows. Select My table has headers if the first row contains labels such as Date, Item, or Amount. Leave it unchecked if the first row is ordinary data. Click OK. If you mistakenly treat data as headers, Excel may change the first row into generic column labels or omit it from the table data.
Check that banding is enabled. Click a cell in the new table to show the Table Design tab. In Table Style Options, make sure Banded Rows is checked. If it is already checked but the stripes are hard to see, choose another table style in the Table Styles gallery. Styles with a pale fill often work best for dense reports because they preserve contrast.
Choose or adjust the table style. With the table selected, open the Table Design tab and select a different style if needed. The style gallery may show more choices by using its expand button. The style applies to the table, including its header and possibly its total row, while Banded Rows controls the alternating body-row effect. Turning off Banded Rows removes the alternating treatment without removing the table itself.
Check the result in the worksheet. Click outside the table and review several rows, including the first and last data rows. If the first data row is not the shade you expected, the style may simply begin with the opposite color; choose a different style or use conditional formatting to set the exact starting shade. Table banding changes row appearance, not the underlying values.
Add more records the table way. To keep the alternating style as the data grows, type in the first row directly below the table or press Tab from the last cell of the last table row to create a new row. Excel tables usually expand to include adjacent new entries and carry their style forward. If a new row is outside the table, its formatting may not follow the banding; resize the table from Table Design > Resize Table or enter the new record directly beside it.
Use conditional formatting for a normal range instead. If you do not want a table, select only the data rows to shade, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. For a range whose first data row is row 2, with headers in row 1 and the selected range starting at A2, use this rule to shade the first, third, fifth, and later odd-positioned data rows: =MOD(ROW()-ROW($A$2),2)=0. Click Format, choose a fill color on the Fill tab, select OK, and then select OK again to create the rule. The dollar signs keep the reference anchored to the first data row, while ROW() changes for each row being formatted.
Verify the conditional-formatting range. Open Home > Conditional Formatting > Manage Rules and check that Applies to covers the full rectangular data range, for example A2:F100 if those are the data cells. The rule should be applied to all columns in each row, not just the first column. If the range begins on a different row, replace $A$2 in the rule with a cell in the first data row, such as $A$5 when row 5 is the first row to format.

In Excel for Mac, use Insert > Table or Home > Format as Table for the table method; the names and locations of options can vary slightly by release. Excel for the web also supports tables and conditional formatting, though some ribbon controls may appear in a simplified layout. In either version, the key is to confirm the table range or the conditional-formatting Applies to range.

Common Mistakes

Including the heading in the conditional-formatting range. If the rule is intended for body rows but the selection begins at the heading, the pattern can start in the wrong place and may color the header. Select body rows only, or write a rule anchored to the actual first row you want shaded.
Applying a rule to only one column. Selecting column A and creating a rule colors only cells in column A, even if the goal is to stripe complete records. Select the full data width, such as A2:F100, before making the rule.
Choosing the wrong header setting. The My table has headers option should reflect what is actually in the first selected row. A title or blank line above the column labels should not be included as part of the table unless it belongs in the data.
Expecting manual fill to keep alternating automatically. Applying a fill color to every other row by hand is slow and can become inconsistent after inserting or sorting records. Use a table style or conditional formatting when the layout will change.
Assuming stripes change the data. Alternating fills are visual formatting only. They do not sort, group, or calculate values, and they do not indicate that a row is selected or validated.
Mixing several formatting systems. Direct cell fills, table styles, and conditional-formatting rules can compete visually. If a stripe does not appear as expected, check the table style and the conditional-formatting rules rather than repeatedly applying another fill.

Tips

Prefer a table for ongoing lists. Tables include useful features such as filter buttons and structured expansion as well as banding. If you only want the color effect, you can still use a table and ignore those extra features, or choose conditional formatting to keep the range as ordinary cells.
Pick a subtle shade. A light tint is easier to read than a saturated fill, especially in worksheets with many columns. Check readability with black text and any existing number formats before settling on a style.
Use the same pattern across related sheets. Reusing a table style or fill color makes a workbook more consistent. For a range-based rule, copy the formatting with Format Painter only when you also verify that the rule’s Applies to range and row anchor are correct.
Keep headings distinct. Use the table’s header style to separate labels from data rather than relying on the same alternating fill. Freeze the heading row separately if you want it to remain visible while scrolling; banding does not freeze panes.
Plan for sorting and filtering. Table banding follows the table’s rows as they are sorted. Conditional formatting based on worksheet row numbers follows the row positions in the sheet, so sorting or moving records can change which record receives a shade. If the stripes should alternate by current position, a row-based rule is appropriate.
Be careful when printing. Check Print Preview to ensure the pale stripe is visible on paper. If printing in grayscale, use a light shade with enough contrast, but do not rely on color alone to convey important status information.

Troubleshooting

The rows all look the same. If you used a table, click in it and check Table Design > Banded Rows. Then try a more noticeable style. If you used conditional formatting, open Home > Conditional Formatting > Manage Rules, select the relevant rule, and check both the formula and Applies to. Confirm the rule is enabled and that its fill color is not identical to the worksheet background.

The wrong rows are shaded. The conditional-formatting formula may be anchored to the wrong first row. For a range beginning on row 5, for example, the reference must anchor to a cell on row 5, not row 2. Also verify that the selection begins on the row named in the rule. When a rule is copied to another range, inspect the adjusted formula rather than assuming the reference moved as intended.

Only part of each row changes color. The rule may apply to a single column. Go to Home > Conditional Formatting > Manage Rules, select the rule, and expand Applies to so it covers every column in the data. For instance, change a one-column range such as A2:A100 to the full row width, such as A2:F100, if the record spans columns A through F.

New rows do not get stripes. With a table, check whether the new entry is actually inside the table. Type directly in the next row or use Table Design > Resize Table to include it. With conditional formatting, extend the Applies to range to include the new rows, or select a reasonable future range when setting up the rule. Avoid applying a rule to far more cells than needed if it makes the workbook slower.

Other colors override the stripes. Look for direct cell fills or additional conditional-formatting rules. In Manage Rules, inspect rules that apply to the same cells and their order; a higher-priority rule or a rule with Stop If True can affect what appears. Remove an unwanted rule or adjust its range and priority. If the range is a table, also check whether its table style is responsible for the base appearance.

Filtered rows no longer appear to alternate. A table’s banding is based on the table’s row sequence, which can leave two neighboring visible rows with the same shade after a filter hides rows between them. That is normal. If the visible records must alternate after filtering, use a conditional-formatting approach based on the displayed row positions only if it suits the workbook, and check the result whenever filters change.

Frequently Asked Questions

Can Excel automatically alternate row colors?
Yes. Format the data as a table and leave Banded Rows checked. Excel applies the style without requiring you to fill each row manually.

Can I alternate colors without converting the data to a table?
Yes. Select the range and create a formula-based conditional-formatting rule. This keeps the cells as a normal range and lets you control which rows receive a fill.

Can I use two custom colors?
Yes. For a table, choose a table style that uses the colors you want or create a custom table style where available. For a normal range, create two conditional-formatting rules with alternating tests and assign a different fill to each. Check both rules’ Applies to ranges and priorities.

Will alternating colors remain when I sort the data?
Table-style banding moves with the table’s records and remains consistent by table position. A conditional-formatting rule based on worksheet row numbers colors positions, so after sorting, a record may receive a different shade than before.

How do I remove alternating row colors?
For a table, click in it and clear Banded Rows on Table Design, or select a plain table style. To remove conditional formatting, select the affected range and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Clearing rules removes all conditional-formatting rules in that selection, so inspect them first if some should remain.

Does alternating row color work in Excel for the web?
Yes. Excel for the web supports formatting ranges as tables and supports conditional formatting. Ribbon wording or available style controls can differ slightly, so use the table or conditional-formatting options shown in the web interface and verify the result in the sheet.

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.