Excel Conditional Formatting: Alternate Rows

How-to basics · Updated November 19, 2026

To shade alternating rows in Excel, select the data range and create a formula-based conditional formatting rule. For data in A2:F100, use =MOD(ROW()-ROW($A$2),2)=0 to format the first data row, then choose a fill color; the pattern will repeat down the range.

Set up alternating rows with a formula

In Excel for Microsoft 365 on Windows, select the cells to format, such as A2:F100, and go to Home > Conditional Formatting > New Rule. Choose Use a formula to determine which cells to format, enter the formula below, select Format, choose a fill on the Fill tab, and confirm with OK. The rule’s Applies to range should cover the full data area, not just the first column.

With the data range A2:F100 selected and A2 as its top-left cell, use =MOD(ROW()-ROW($A$2),2)=0. The formula returns TRUE on the first data row, row 2, and every other row after it. Conditional formatting applies the chosen style wherever the rule evaluates to TRUE. The dollar signs keep the reference anchored to A2 while the rule is evaluated across the rest of the selected range.

If your data starts on a different row, replace A2 in the formula with the top-left cell of the selected range. For example, with a range beginning at A5, anchor the formula to A5. The important detail is that the formula’s starting reference matches the first row being formatted; otherwise, the shading may begin on the opposite row from the one you expect.

  1. Select the data cells, including every column that should receive the shading.
  2. Open Home > Conditional Formatting > New Rule.
  3. Select Use a formula to determine which cells to format.
  4. Enter the rule formula, choose a fill color with Format, and select OK.
  5. Check the Applies to range in Conditional Formatting > Manage Rules if the rule misses rows or columns.

On Mac, the conditional formatting controls are also on the Home tab; the dialog wording and layout can vary slightly by Excel release. Excel for the web supports conditional formatting, though some rule-management controls may be arranged differently from the desktop dialog. If you cannot find the formula-rule option in your current interface, open the workbook in the desktop app to create or edit the rule.

How the formula creates a repeating pattern

The rule uses two worksheet functions. ROW returns the worksheet row number of a reference. MOD returns the remainder after division. Dividing a row’s offset from the selected starting row by 2 produces a remainder of zero on every other row, which makes the test TRUE for those rows. Excel then applies the format to the cells covered by the rule on each TRUE row.

Subtracting the starting row number is what makes the pattern relative to your selected data rather than the whole worksheet. Without that adjustment, a formula based only on the physical row number would shade even-numbered worksheet rows. That may work when the first data row happens to be an even-numbered row, but it can start with the wrong stripe when the table begins elsewhere.

The formula does not change cell values, sort records, or insert blank rows. It only controls how selected cells look. If you add or remove records, the stripe pattern is recalculated for the cells covered by the rule. The rule itself must still include any new rows you want formatted; check its Applies to range if the color stops at the old end of the data.

Reverse the first stripe or change the color

Sometimes you want the second data row shaded instead of the first. With A2:F100 selected and A2 as the first cell, use =MOD(ROW()-ROW($A$2),2)=1. The test is TRUE when the offset from the first row has a remainder of one after division by two, so row 3 receives the fill. You can also keep the original formula and choose a different fill; the formula controls which rows qualify, not the color itself.

To change an existing rule, go to Home > Conditional Formatting > Manage Rules, select the rule, and choose Edit Rule. In the dialog, update the formula or use Format to change the fill. In the rules manager, check Show formatting rules for to make sure you are viewing the rules for the correct selection or worksheet. If more than one rule affects the same cells, the rule order and any Stop If True setting can affect the result.

Choose between conditional formatting and table banding

Conditional formatting is useful when you need a custom pattern, want the fill to coexist with other rules, or are formatting an ordinary cell range. Excel tables offer another option: built-in banded rows. A table style is often the simpler choice for a list that will grow and be filtered or sorted.

  • Use a table style when the data is a structured list with headers and you want built-in banding alongside table features such as filter buttons. Select a cell in the range, choose Insert > Table, confirm whether the table has headers, and use the Table Design tab to select a style or turn on Banded Rows.
  • Use conditional formatting when you want a specific fill on a plain range, need a custom starting stripe, or want formula-driven formatting that is not limited to a table style.
  • Use manual fill colors only for a small, static range. Hand-coloring every other row is harder to maintain after sorting, inserting, or deleting rows.

Table styles and conditional formatting are different mechanisms. A table style is attached to the table’s design and is convenient as the table expands. Conditional formatting is a rule attached to a cell range and can be combined with conditions such as highlighting overdue dates. If you apply both, their visual effects can interact, so remove or adjust one if the final appearance is unexpected.

Adjust the rule for real worksheets

Apply the rule to all columns in each record if you want a continuous stripe across the row. For example, selecting A2:F100 shades columns A through F. Selecting only A2:A100 shades just the first column. You can include more columns than currently contain values if you expect the table to widen, but avoid selecting the entire worksheet unless that is genuinely necessary; a carefully sized range is easier to inspect and manage.

If the worksheet has a header row, usually leave it outside the alternating-row range and format it separately. For example, apply the rule to A2:F100 when row 1 contains headings. This keeps the header style stable and makes the first record the first stripe. If there are totals or notes below the records, exclude those cells unless they should also participate in the pattern.

To extend formatting, use Home > Conditional Formatting > Manage Rules and inspect Applies to. You can edit the range there, or select the expanded data range and create a new rule, taking care not to leave duplicate overlapping rules. When you insert rows inside a formatted range, Excel may adjust the range; when you add data below it, verify that the new rows are included rather than assuming the rule extended.

Common problems and how to fix them

  • The first row is the opposite color. The formula’s anchor may not match the first cell of the selected range, or the remainder test may be set to 1 instead of 0. Align the anchor with the range’s first row and choose the desired starting stripe.
  • Only one column changes color. The Applies to range may cover just one column. Edit it to include all columns in the records, such as A2:F100.
  • The pattern stops before the last record. The rule’s Applies to range ends too soon. Extend it in Manage Rules or recreate the rule over the full range.
  • Some rows have a different color. Another conditional formatting rule may also apply to those cells, or a table style or manual fill may be involved. Review the rules for the selection and simplify overlapping formats.
  • Filtering or hiding rows makes the pattern look uneven. The formula uses worksheet row positions, not the order of only the visible records. Hidden or filtered-out rows are not renumbered by this formula, so visible stripes may have gaps or repeated colors.
  • The formula appears as text or produces an error. Confirm that you created a formula rule rather than typing the expression into a worksheet cell, and check that the formula begins with an equals sign and uses the correct starting reference.

What alternating-row formatting does not do

Banding is a visual aid, not a data operation. It does not group related records, guarantee that a row stays paired with another row, or prevent sorting from changing the order. When you sort a properly selected dataset, records move as rows; the conditional formatting is recalculated according to the rows they occupy. If color carries meaning, such as identifying a particular status, use a separate condition or a clearly labeled column rather than relying on alternating bands.

Also, a formula based on ROW does not count visible records after filtering. It evaluates the worksheet row position even when some rows are hidden. That behavior is usually appropriate for a simple ledger or report, but it may not produce a perfect light-dark-light sequence among visible items in every filtered view. A table style may be a better fit for lists that users filter frequently, although you should check the appearance in your workbook and version.

Use a restrained fill with enough contrast to distinguish adjacent records without reducing the legibility of text, numbers, or other highlights. If a workbook will be printed or viewed by people with color-vision differences, consider subtle shading and keep borders, headings, or other cues that make the table structure clear. Conditional formatting provides appearance only; it does not add a semantic label that screen readers or other tools can interpret as a separate data category.

Frequently Asked Questions

Can Excel automatically shade every other row?
Yes. A formula-based conditional formatting rule can shade alternating rows in a range. For a structured list, converting the range to a table and choosing a banded table style is another built-in option.

How do I alternate row colors in Excel without a table?
Select the range, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter a formula anchored to the range’s first cell. Choose a fill and confirm that Applies to covers all the intended columns and rows.

Why does my alternating-row formula start on the wrong row?
The formula may be based on worksheet row parity rather than the selected range’s starting row, or the formula reference may not match the selection’s top row. Anchor the calculation to the first row of the range, then use a remainder of zero or one depending on which stripe should be colored first.

Will alternating colors stay when I sort the data?
Conditional formatting recalculates based on the rows cells occupy after a sort, so the stripe follows row position rather than a particular record. A table style also provides row banding as part of the table’s appearance. Neither method makes the color a dependable way to identify a record.

Can I alternate colors only for visible rows after filtering?
A basic ROW-based rule does not count visible records; it uses each record’s worksheet row number. Filtering can therefore leave gaps in the apparent sequence. Try a table style if your main goal is banding in a filtered list, and check the result in your Excel version.

How do I remove the formatting?
Select the affected cells and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. To remove only one rule, open Manage Rules, select that rule, and delete it. Clearing a rule removes its conditional formatting, not the worksheet’s underlying values.

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.