How to Make Every Other Row a Color in Excel (2 Ways)

To color every other row in Excel, format your data as a table and choose a banded style, or use conditional formatting with a formula. A table is the quickest option for a list that will grow; conditional formatting gives you more control over the exact rows and fill color. The steps below are for Excel for Microsoft 365 on Windows.

What You’ll Need

An Excel worksheet with the data you want to format. Decide whether the selection includes a header row.
For the table method, a continuous block of data works best. For conditional formatting, know the first and last rows and columns to format.
If your workbook is shared or contains existing formatting, consider saving a copy before changing its appearance.

Step-by-Step

Method 1: Use a table style for automatic banding

Select the data. Click a cell in the data block. If Excel correctly detects the full range, you can use that cell; otherwise, drag to select the full range, including the column headings. Avoid selecting blank rows or unrelated columns.
Turn the range into a table. Choose Insert > Table. Check the range in the dialog. Turn on My table has headers if the first row contains column names, then select OK. You can also use Home > Format as Table and choose a style.
Choose an alternating-row style. Click inside the new table and open the Table Design tab. In the Table Styles gallery, select a style with alternating light and dark rows. If you do not see a suitable option, open the gallery’s More button to view additional styles.
Check the banding setting. On the Table Design tab, make sure Banded Rows is checked in the Table Style Options group. Excel applies stripes to the table rows; the header may have its own separate style.
Adjust the color if needed. Try another table style if you want a different shade or stronger contrast. Table styles coordinate the header, borders, and row fills, so check the whole table rather than judging only one stripe.
Confirm the result. Look down the data area and check that the alternating pattern starts where you expect. Add a new row at the bottom of the table as a quick check: table formatting generally extends to rows added directly beneath it.

Formatting a range as a table adds table features such as filter buttons and structured data behavior, not just color. If you do not want those features, or if the range is part of a carefully formatted report, use conditional formatting instead.

Method 2: Use conditional formatting for a custom color

Select only the rows to format. For example, if your data is in columns A through F, with headers in row 1 and records in rows 2 through 20, select A2:F20. Leave the header out if it should keep its current formatting. The exact selection matters because the rule will apply to that range.
Start a formula-based rule. Choose Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
Enter a formula relative to the first selected row. For the example selection beginning in row 2, enter =MOD(ROW()-ROW($A$2),2)=0. This returns TRUE for the first selected row, then every second row after it. The reference to A2 is fixed so the starting point stays the same as the rule is applied across the range.
Choose the fill. Select Format, open the Fill tab, choose a color, and select OK. Select OK again to create the rule. Choose a subtle shade that leaves the text easy to read.
Check the range and pattern. Inspect the selected data. In the A2:F20 example, rows 2, 4, 6, and so on should have the fill; rows 3, 5, 7, and so on should not. If the color begins on the opposite row from the one you want, replace the final =0 with =1.
Review the rule if necessary. Choose Home > Conditional Formatting > Manage Rules. Set Show formatting rules for to the current selection or worksheet, then check the formula and the Applies to range. Edit either one and choose Apply.

The formula is based on worksheet row numbers. If your selected range starts on a different row, change the anchor in the formula to the first cell in that selection. For example, if your data begins at B5, use =MOD(ROW()-ROW($B$5),2)=0 and apply the rule to the full data range starting at B5. This makes the first row of the selected data colored, regardless of whether it is an odd- or even-numbered worksheet row.

To shade the second, fourth, and other even-positioned rows within the selected range instead, use =MOD(ROW()-ROW($A$2),2)=1 for a range beginning at A2. The important part is that the anchor cell corresponds to the first row being formatted. The reference can be in any column of that first row; using the first column of the range makes the setup easier to recognize.

Excel for Mac and Excel for the web

In current Excel for Mac, the same two approaches are available. Table commands appear on the ribbon after you select the data or click inside a table; tab names and dialog layouts can vary slightly by release. For a formula rule, look under Home > Conditional Formatting for the option to create a new rule and use a formula. Excel for the web also supports tables and conditional formatting, although some advanced style and rule-management controls may be arranged differently or unavailable. If you cannot find a control, use the ribbon search box to search for “Format as Table” or “Conditional Formatting.”

Common Mistakes

Selecting the header by accident. If a formula rule is meant for records only, exclude the heading row from both the selected range and the rule’s Applies to range. Otherwise, the heading can receive a stripe or shift which data row gets colored.
Using the wrong starting row in the formula. A formula anchored to A1 does not make row 2 the first row of a selection that begins at A2. Anchor it to the first row in the range, such as A2 for A2:F20.
Using a fixed row number in the test. A formula such as =MOD(ROW($A$2),2)=0 keeps testing row 2 rather than evaluating each row in the selected range. Use ROW() for the current row and keep only the starting reference fixed.
Expecting a table to behave like plain formatting. Converting data to a table adds table behavior and can change how existing formatting is displayed. If that is not wanted, conditional formatting is usually the better choice.
Applying the rule to only one column. If you want the entire record shaded, make the Applies to range cover every column in the data block, not just the first column.

Tips

Choose a pale fill so black text, numbers, and hyperlinks remain readable. Check the stripe against any existing font colors and borders.
For a table, use Banded Rows rather than manually filling individual rows. Manually applied fills can become inconsistent when records are inserted or deleted.
For a custom conditional-formatting rule, copy the formula pattern and update the anchor and Applies to range to match the next data block. Make sure the anchor is the first row of that particular block.
If your data will grow, a table is convenient because it can extend formatting when rows are added to the table. If you choose conditional formatting, you can apply the rule to extra blank rows below the current data, but that may color empty rows too.
Alternating fills can help readers track across wide lists, but they do not sort or group records. Keep headings clear and use filters or sorting separately when readers need to find data.

Troubleshooting

No rows change color: Open Conditional Formatting > Manage Rules and confirm the rule is enabled and its Applies to range includes the cells you selected. Check that the rule formula starts with an equals sign and refers to the correct first row. Also check whether another conditional-formatting rule is taking priority or using a conflicting fill.

The wrong rows are colored: The rule may start on the wrong row, or its formula may be anchored to a different row. If the first selected row should be shaded, make the anchor match that row and use a remainder of zero. If the next row should be shaded instead, use a remainder of one. Review the first few rows after changing the rule.

Only part of each row is shaded: Expand the Applies to range across all columns you want to format. For example, if records occupy A:F, a rule applied only to A2:A20 colors only column A; the range should cover A2:F20.

Table stripes disappeared: Click inside the table, open Table Design, and check Banded Rows. Then select a table style that visibly uses row banding. If the range is no longer a table, you can use Home > Format as Table to format it again, checking the header setting carefully.

Stripes look irregular after filtering or inserting blank rows: Conditional formatting evaluates actual worksheet row numbers, so gaps can make the visible records appear not to alternate. Remove unintended blank rows or revise the rule to suit the new starting row. If you need a simple banded list that expands with added records, consider the table method.

Frequently Asked Questions

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

Yes. Select the data rows, then create a formula-based rule through Home > Conditional Formatting > New Rule. Use a MOD and ROW formula, choose a fill, and confirm that the Applies to range covers the full block. This keeps the data as a normal range.

How do I change the stripe color later?

For a table, select it and choose a different style in the Table Design tab. For conditional formatting, open Manage Rules, select the rule, choose Edit Rule, then use Format to pick another fill color.

Can I color every other row in only one column?

Yes. Select only that column’s data cells before creating the conditional-formatting rule, or set the rule’s Applies to range to that column. With a table style, banding normally applies across the table, so conditional formatting gives more control for a single column.

Will the colors stay correct when I sort the data?

Table banding follows the table’s rows as data is sorted. A conditional-formatting rule based on ROW() colors worksheet row positions, so after sorting, the visual stripes still alternate by row location rather than by a permanent record identity. That is usually suitable for readability.

Why does my table have filter arrows now?

Filter buttons are part of Excel table behavior. If you want to keep the table and hide the arrows, click inside it and clear Filter Button on the Table Design tab. If you do not want a table at all, use conditional formatting on the original range instead.

Can I use this method when new rows are added?

A table style generally extends to rows added directly to the table. A conditional-formatting rule only affects its Applies to range, so expand that range to cover future rows or edit it when the data grows. Check the first newly added rows to verify the alternating pattern.

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.