Conditional Formatting Alternate Row Color in Excel

To alternate row colors in Excel, select the cells to format, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter =MOD(ROW(),2)=0. Set a fill color and confirm; the rule shades even-numbered worksheet rows in the selected range, making long lists easier to scan.

What alternate-row formatting does

Alternating row color, often called banding or a zebra stripe effect, gives adjacent records different background fills. It helps readers follow a line across a wide worksheet, especially when columns contain dates, names, quantities, or figures. The colors are visual cues: conditional formatting does not change the cell contents, values, or calculations.

A conditional formatting rule evaluates a formula for each cell or row in its applied range. If the formula returns TRUE, Excel applies the formatting you chose. The MOD and ROW functions provide a simple repeating test: MOD returns the remainder after division, and ROW returns a cell’s row number. Dividing the row number by 2 produces a remainder of zero for even rows and one for odd rows.

The formula uses worksheet row numbers, not a count of records starting at the top of the selection. That distinction matters when your data begins below a title or header. You can use a second formula to make the first row in your selected data the first shaded row.

Set up the rule in Excel for Microsoft 365

Select the full data area that should receive the bands, such as A2:F100. Include all columns in each record, but leave the header out if it should have its own formatting.
On the Windows ribbon, choose Home > Conditional Formatting > New Rule.
In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
With A2:F100 selected, enter =MOD(ROW(),2)=0 in the formula box. This formula shades worksheet rows 2, 4, 6, and so on within the selected range.
Choose Format, open the Fill tab, select a light background color, and choose OK. Choose OK again to create the rule.

Choose a pale fill so text and numbers remain easy to read, particularly if users may print the sheet or view it on a small screen. You can change the fill later through Home > Conditional Formatting > Manage Rules. On Mac, use the Home tab’s Conditional Formatting menu and the New Rule option; the dialog layout can differ slightly from Windows. Ribbon labels and available dialogs may vary by Excel build.

Make the first selected row the starting stripe

If the first data row is not an even worksheet row, or you want the pattern to start consistently wherever the range begins, make the formula relative to that first row. For data in A5:F50, select A5:F50 and enter =MOD(ROW()-ROW($A$5),2)=0. Row 5 gives a difference of zero, so the first data row is shaded; row 6 is not, row 7 is shaded, and the pattern continues.

The dollar signs in $A$5 keep the reference fixed as Excel evaluates the rule across the selected cells. The ROW function only needs the row component, so the reference column is not important; using the top-left cell of the data range makes the setup easier to understand. If you would rather leave the first data row unshaded, change the final test from =0 to =1, as in =MOD(ROW()-ROW($A$5),2)=1.

For a formula rule, the reference and the applied range work together. The formula should be written as if Excel is evaluating the top-left cell of the range. An accidental absolute reference to the current row, or selecting only one column instead of the whole record, can make the stripe appear in the wrong places.

Use an Excel table for automatically expanding data

If the list is a regular dataset with column headings, an Excel table is often the simplest alternative. Select a cell in the data and choose Insert > Table, or press Ctrl+T on Windows, then confirm that the table has headers if appropriate. On Mac, the usual shortcut is Command+T. The Table Design tab lets you choose a table style and turn on Banded Rows.

Table banding is convenient because new rows added to the table inherit its structure and styling. Tables also provide useful features such as filter buttons and structured references in formulas. Use a table when you want the worksheet to behave like a maintained data list, not merely when you need stripes. A conditional formatting rule is more flexible when the range is irregular, when the fill must follow a special logic, or when you need to preserve a particular style outside a table.

Table styles and conditional formatting can overlap. If a table already has a banded style, adding another row-color rule may make the result confusing or difficult to change. Decide whether the table style or the conditional formatting rule should control the fill, and remove or adjust the other styling if the colors conflict.

Choose a range that will keep working

A rule applies only to the cells listed in its Applies to range. A rule applied to A2:F100 will not automatically cover a new record entered in row 101 unless Excel expands the range or the new row is added within an expanding structure such as a table. For a fixed worksheet, you can set the applied range to include spare rows, for example A2:F500, so future entries within that area are formatted.

To inspect it, choose Home > Conditional Formatting > Manage Rules. In the dialog, check the rule’s formula and Applies to field. The Show formatting rules for menu can show rules for the current selection or the whole worksheet. Expanding the range too far is not usually a visual problem, but it can make the rule harder to manage; tables are often neater for growing lists.

Keep the range aligned with complete records. If your data occupies A2:F100 but the rule applies only to A2:A100, only the first column will be shaded. If the rule extends into unrelated notes or totals, those cells may also receive stripes. Avoid including a separate total row unless you want it to follow the alternating pattern.

Understand sorting, filtering, and other rules

A ROW-based rule uses the worksheet position of each row. After sorting records, Excel recalculates the rule, so the bands follow row positions rather than belonging permanently to particular records. That is usually appropriate for visual striping: whichever record occupies an even row gets the even-row fill. If color is intended to identify a record category or status, use a rule based on the relevant cell value instead of row parity.

Filtering hides rows but does not change their worksheet row numbers. As a result, the visible records may not always appear in a perfectly uninterrupted alternating sequence when hidden rows are skipped. If alternating visible records is essential, test the behavior with your specific filtering and layout, or use table banding and confirm that it meets the requirement.

Conditional formatting rules can also compete with one another. For example, a rule that colors overdue dates may overlap a banding rule. Open Manage Rules to review the order and ranges. Where applicable, rule order and Stop If True affect which formats are displayed; a later or higher-priority rule may determine the visible fill. If a band disappears, inspect overlapping rules before recreating it.

Common problems and how to fix them

The first stripe starts on the wrong row: The simple formula follows worksheet row parity. Use the top-row-relative formula if you want the pattern anchored to the first row of the selection.
Only part of each row changes color: Check the Applies to range and include every column that should be shaded.
Every row or no rows are shaded: Confirm that the formula starts with an equals sign, is entered in the formula-rule option, and tests against zero or one as intended. Check that the selected range begins where you think it does.
New entries have no stripe: Extend the rule’s Applies to range or convert the list to a table so added rows can inherit formatting.
Another fill appears instead: Check for an existing conditional formatting rule or table style that also applies to those cells.

To remove only the banding rule, select the affected cells and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Be careful with Clear Rules from Entire Sheet, which removes conditional formatting beyond the selected data. Clearing a rule removes its formatting logic, not the underlying cell values.

When to choose conditional formatting

Use conditional formatting when the stripes need to be formula-driven, when you want to apply them to a specific non-table range, or when the appearance should be managed alongside other conditional rules. It is also useful if you want one rule that can be redirected or adjusted through Manage Rules.

Use a table’s Banded Rows setting when the data is a structured list that will grow and benefit from table features. For a small, static report, manually applying fills is possible, but it becomes tedious to maintain after inserting or deleting rows. The choice is less about which method is universally better and more about whether the worksheet is a data table, a custom report, or a fixed range.

Frequently Asked Questions

Can I alternate row colors without using a formula?
Yes. Convert the data to an Excel table and choose a style with banded rows, or enable Banded Rows on the Table Design tab. This is often easier for a growing list.

What formula colors every other row in Excel?
For even worksheet rows, use =MOD(ROW(),2)=0 in a formula-based conditional formatting rule. To start the pattern at the first row of a range, subtract the row number of that first row, as shown in the relative formula above.

Can I use two colors instead of one color and white?
Yes. Add two formula rules for the same range, one testing for a remainder of zero and the other for a remainder of one, and assign a different fill to each. Usually a single colored fill alternating with the normal sheet background is simpler and easier to read.

Will the color update when I add or delete rows?
The rule recalculates for rows it covers, but its Applies to range may not include newly added rows. Use a table or extend the range if the list is expected to grow.

Does sorting keep a color attached to its original record?
No, a row-number rule colors positions, not record identities. After sorting, the conditional formatting recalculates by worksheet row. Use a value-based rule if a color needs to represent a record’s status or category.

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.