How to Get Alternating Color Rows in Excel Automatically

To get alternating color rows in Excel, select your data, choose Home > Format as Table, and pick a style with banded rows. Confirm whether your range has headers. Excel will apply alternating shading and continue the pattern as the table grows. If you want to color a range without turning it into a table, use conditional formatting instead. Alternating colors make rows easier to follow across a wide sheet, especially when scanning names, dates, or figures.

What You’ll Need

Excel for Microsoft 365 on Windows, with the workbook open. The steps also work in recent desktop versions of Excel, though some ribbon labels can vary slightly.
A worksheet range with data. If you plan to make a table, include a header row with a heading for each column.
A color or table style that is easy to read. A light fill usually keeps text and numbers clear.

You do not need a formula for the table method. Conditional formatting is a useful alternative when you want to keep the data as a plain range or need more control over which rows receive a color.

Step-by-Step

Check the shape of your data. Make sure each column has a heading and that the data forms one continuous rectangle. Remove completely blank rows or columns inside the range if they are not intentional; gaps can make Excel guess the wrong table boundary. If your first row contains actual records rather than headings, add a header row or use the conditional-formatting method below.
Select the data. Click a cell inside the range you want to shade. Excel will usually detect the connected data block when you create a table. To be precise, select the complete range, including the headings, such as A1:F100. Check that you have not included unrelated notes or empty columns.
Convert the range to a table. On the ribbon, choose Home > Format as Table, then select a style with alternating light and dark row shading. You can also use Ctrl+T on Windows to open the Create Table dialog. If Excel has not selected the right range, correct it in the dialog before continuing.
Confirm the header setting. In the Create Table dialog, check My table has headers if the top row contains column names, then click OK. If the box is checked incorrectly, Excel may treat a record as a heading or give your actual headings generic names such as Column1. You can correct the setting later from the Table Design tab.
Review the table style. Click any cell in the new table to show the Table Design tab. In Table Style Options, make sure Banded Rows is checked. Choose another style from the table-style gallery if the default colors are too strong, too faint, or difficult to read. The banding should be visible across the table, including its data columns.
Use conditional formatting if you want to keep a normal range. For this example, assume the data is in A1:F100, row 1 contains headings, and shading should begin on row 2. Select A2:F100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter this formula: =MOD(ROW()-ROW($A$2),2)=0. Click Format, choose a fill color on the Fill tab, and click OK to save the rule. The formula shades rows 2, 4, 6, and so on within the selected range.
Check the result and adjust the scope if needed. Look at the first several rows to confirm the colors alternate as intended and that the full width of each record is covered. For conditional formatting, open Home > Conditional Formatting > Manage Rules and check that the rule applies to the full range, such as =$A$2:$F$100. Extend that range if your data has more rows or columns.
Test what happens when the data changes. Add a row directly beneath a table and enter data to see whether Excel expands the table and carries the style forward. For a plain range, add a test row inside the conditional-formatting range and confirm that the shading appears. Undo the test or remove its contents when you are done.

The table approach is generally simplest for a list that you sort, filter, or extend. The conditional-formatting approach is better when you need to preserve a plain range, avoid table features, or apply a custom stripe pattern.

Common Mistakes

Choosing a table style but leaving Banded Rows off. A style may include a colored header or borders without showing alternating rows if the Banded Rows option is not enabled. Click inside the table, open Table Design, and check that option. The visual preview in the style gallery is helpful, but the table option controls whether row banding is used.

Including the wrong cells. A conditional-formatting rule can be correct but appear broken if it applies only to one column or stops before the bottom of the list. Select the whole data area before creating the rule, or edit its Applies to range in Manage Rules. If the first record is on row 3, update the selection and formula reference to start at row 3; do not leave the reference at A2.

Marking the wrong row as a header. If Excel treats the first data record as the table header, it will not be shaded like the other records and its values may become column names. Conversely, turning off the header option when headings are present can cause the headings to be included as ordinary data. Revisit Table Design and confirm the header row is set correctly.

Mixing manual fills with automatic banding. Manually applying a fill to cells can make the table stripes look inconsistent or hide the color you expect. Remove unwanted cell fill with Home > Fill Color > No Fill, then check the table style again. For conditional formatting, remember that a matching conditional-formatting rule can take precedence over a normal fill.

Using alternating colors to signal meaning without a key. Row banding is for easier reading, not a reliable way to label categories or indicate a pass, warning, or error. Sorting, filtering, or changing the table style can change how a stripe looks. Use a separate status column or an appropriately designed rule when color represents information.

Tips

Choose a pale background and keep the text color dark. Strong saturated fills across many rows can make a worksheet tiring to read and can reduce contrast when printed. If the sheet has important headings, use a distinct header style and keep the alternating row colors quieter. Preview the result at the zoom level people will normally use, not only at a close-up scale.

Tables are convenient when you regularly add records because the table style and banding usually extend with the table. They also provide filter buttons and make it easier to sort a list. You can rename a table from the Table Design tab to make it easier to identify, but renaming is not necessary for the colors to work. Keep a table as a table if you rely on those features; converting it back to a range removes table behavior.

For a conditional-formatting rule, select the exact range before you create the rule and anchor the starting cell in the formula with dollar signs. In the example, $A$2 stays fixed as the rule evaluates the other rows, while ROW() changes for each row. If your data starts elsewhere, use the first cell of the selected data rows in place of $A$2 and select the matching range. That alignment is what makes the first data row receive the intended stripe.

If the worksheet may be printed, use Print Preview to check that the shades remain distinguishable on paper. Some printers render subtle fills almost white, and black-and-white printing may remove the distinction. You can use light borders or a slightly stronger alternate fill if the row boundaries are hard to follow, while still keeping the sheet readable.

Troubleshooting

No alternating colors appear: For a table, click inside it and check Table Design > Banded Rows. If the selected style has little contrast, choose a clearer style. For a plain range, open Conditional Formatting > Manage Rules, set the rule display to the current worksheet if necessary, and verify both the formula and Applies to range.

Every row has the same color: Look for a manual fill covering the cells, a different conditional-formatting rule, or a table style that does not make its banding obvious. Remove unnecessary fills or review competing rules in Manage Rules. With the example formula, confirm you selected A2:F100 rather than including the header row in the selection.

The stripes start on the wrong row: The conditional-formatting formula is tied to the first selected data row. If the selection begins at A3, change the fixed reference in the formula to $A$3. If the row colors are simply reversed from your preference, switch the comparison from =0 to =1; the result will start with the opposite set of rows.

New rows do not get a color: Check whether the new record is actually inside the table. Type in the row directly below the table or use the last table cell and press Tab to create a new table row. If you are using conditional formatting, increase the Applies to range to include the added rows. A rule cannot format cells outside its scope.

Colors change after sorting or filtering: This is normally expected. Table banding follows the table as its records are sorted, and hidden records are not displayed while a filter is active. Conditional formatting recalculates based on the row positions in its selected range. If the color is meant to identify a category rather than improve readability, use a category-based rule instead of simple alternating stripes.

Frequently Asked Questions

Does Format as Table change my data?
It does not rewrite the values in your cells, but it adds table behavior such as filter buttons and structured formatting. Check the range and header setting before confirming. If you only want color and do not want a table, use conditional formatting.

Can I alternate colors without creating a table?
Yes. Select the data rows and use Home > Conditional Formatting > New Rule. The formula in the steps applies a fill to every other row in the selected range. This leaves the data as a normal worksheet range.

Can I choose two different colors for the alternating rows?
A table style controls the banded colors as a set; choose a different style in the Table Design gallery. With conditional formatting, create two rules for the same range, using one rule for each alternating row pattern, and choose a separate fill for each. Keep the formula references aligned to the first data row.

Will the colors remain when I sort or filter?
Table styles remain attached to the table when you sort it, and filtering hides rows without removing the table. Conditional formatting applies to the cells in its range and recalculates as their row positions change. If banding must represent a specific record category, use a rule based on that category rather than row position.

Does this work in Excel for Mac or Excel for the web?
The table-style and conditional-formatting features are available in current Excel for Mac and Excel for the web, although the ribbon layout and dialog wording can differ. The Windows paths above may appear in a slightly different location. On Mac, the table shortcut is commonly Command+T; if it is unavailable or intercepted, use the ribbon instead.

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.