To highlight every other row in Excel, select the range, open Home > Conditional Formatting > New Rule, choose “Use a formula to determine which cells to format,” and enter =MOD(ROW()-ROW($A$2),2)=0 if the selected range begins in row 2. Choose a fill color and confirm; the rule shades the first selected row, then every second row after it.
How the alternating-row formula works
The formula uses the row number of each cell being evaluated. ROW() returns that cell’s worksheet row number, while ROW($A$2) returns the fixed starting row number, 2. Subtracting the two makes the first selected row equal zero; MOD(...,2) returns the remainder after division by two. The condition is true for every other row, and conditional formatting applies the chosen format wherever it is true.
That relative pattern is more flexible than simply testing whether the worksheet row number is even. A rule based on even row numbers could start with an unshaded row if your data begins on an odd-numbered worksheet row. By measuring from the first row in your selected range, the rule can start with the first record regardless of where the list sits on the sheet.
For example, with data in A2:F8, the rule is true on rows 2, 4, 6, and 8. With a range starting at row 5, the corresponding rule uses row 5 as its anchor, so rows 5, 7, and 9 are shaded. The formula does not inspect cell contents; it creates a visual pattern from row positions.
Apply the rule in Excel for Microsoft 365
On Excel for Microsoft 365 for Windows, set up the rule as follows:
- Select the cells you want striped, such as A2:F100. Include the full width of the records, not just the first column, if the entire row should receive a fill.
- Choose Home > Conditional Formatting > New Rule.
- Select “Use a formula to determine which cells to format.”
- With the selection beginning in row 2, enter
=MOD(ROW()-ROW($A$2),2)=0in the formula field. - Choose Format, select a fill color on the Fill tab, and confirm the format dialog.
- Confirm the new rule. Check the first few rows to make sure the starting stripe and range are right.
The selection matters: conditional formatting evaluates the rule across the cells listed in “Applies to.” If you selected A2:F100, Excel formats those columns only. To extend the stripes to column G, edit the rule’s range or create it while A2:G100 is selected. The row-based formula can be applied across multiple columns because it refers to the current row, not to a particular data value.
On Excel for Mac, select the range and use Home > Conditional Formatting > New Rule, then choose the formula-based rule type in the dialog. Dialog labels and layout can differ slightly by release, but the same formula works. Excel for the web also supports conditional formatting; the available rule-editing controls may be arranged differently from the Windows desktop ribbon.
Choose the correct starting row
The anchor in the formula must match the first row of the selected range. If your list has a header in row 1 and records begin in row 2, use row 2 as the anchor. If records begin in row 5, use row 5. A common mistake is selecting a range that starts on one row while leaving the formula anchored to another. The result is still alternating, but the first data row may have the opposite fill from the intended pattern.
With a selected range beginning at row 5, use this version to shade its first row and then every other row:
=MOD(ROW()-ROW($A$5),2)=0
The dollar signs keep the anchor fixed as Excel applies the rule to other rows. The column letter in that reference is not important to the calculation; the formula uses the row number. You can also write a fixed row number directly, but using ROW($A$5) makes the starting point easier to recognize if you revisit the rule later.
If you prefer the second selected row to receive the fill, change the final comparison so the formula matches odd rather than even offsets. For a range beginning in row 2, use =MOD(ROW()-ROW($A$2),2)=1. This reverses the stripe without changing the selected range. It does not change which rows are records; it only swaps the two visual groups.
Use an even-row formula when appropriate
A shorter option is to test the worksheet row number directly. This is useful when the stripes should always correspond to even-numbered worksheet rows, rather than beginning with the first row of a selected list.
With any selected range and a requirement to shade worksheet rows 2, 4, 6, and so on, use =MOD(ROW(),2)=0. It returns TRUE on even worksheet rows and FALSE on odd rows. For a list starting at row 2, that happens to shade its first record; for a list starting at row 3, it shades the second record instead.
This distinction is why a range-relative formula is usually the safer choice for a stand-alone list. The even-row version is not incorrect, but it answers a different question: “Is this an even-numbered sheet row?” The anchored version answers, “Is this the first row of my selected block, or every second row after it?” Choose the condition that matches how you expect the pattern to behave if you move the list.
Conditional formatting versus Excel table banding
If your data is already an Excel table, built-in banded rows may be simpler than a formula rule. Click in the table and use the Table Design tab to enable Banded Rows in the Table Style Options area. Excel then alternates table-row formatting as the table expands, and the style remains associated with the table as rows are added.
Table banding is a good fit for structured lists with headers, filters, and data that may grow. Conditional formatting is more suitable when the range is not a table, when you want a custom rule, or when only part of each row should be shaded. It also lets you make the stripe depend on conditions other than row position, although that is a different rule from a simple alternating pattern.
Using both methods on the same cells can make the result confusing. Table styles and conditional formatting can both affect appearance, and conditional formatting may take precedence for properties it sets. If the fill looks different from what you selected, inspect the table style and the conditional-formatting rules rather than assuming the formula is wrong. You can convert a table to a normal range if you no longer want table behavior, but doing so removes table features such as structured references and automatic expansion.
What happens when you sort, filter, or hide rows?
The standard formula follows worksheet row positions. Sorting a range usually moves the records while the conditional formatting continues to evaluate the rows in their new positions, so the stripes remain alternating in the sorted display. Filtering is different: hidden records keep their underlying row positions, and the formula does not count only the records currently visible. As a result, two visible records separated by a filtered-out row can have the same fill.
If you need the stripes to alternate over visible records, use a visible, consistently filled key column. For a list with records in A2:A100, where every record has a nonblank identifier in column A, this formula counts visible identifiers from the first record through the current row:
=MOD(SUBTOTAL(103,$A$2:A2),2)=1
Apply the rule to the rows or columns you want formatted, beginning with row 2. The first visible nonblank identifier produces a count of 1 and is shaded; the next visible record produces 2 and is not. The 103 option makes SUBTOTAL ignore filtered-out rows and manually hidden rows when counting nonblank cells. If the key cells can be blank, use a column that is reliably populated; otherwise the count may not match the number of visible records. This approach is more specialized than the basic row formula and is only necessary when the visible sequence itself must alternate.
Common problems and practical fixes
- The first row has the wrong color: Make the anchor row match the top row of the “Applies to” range, or reverse the final comparison to swap the pattern.
- Only one column is shaded: Expand “Applies to” to cover the full record width, for example A2:F100, rather than applying the rule only to column A.
- The stripes stop at the last selected row: Extend the rule’s “Applies to” range. A rule applied to a fixed range does not automatically cover data typed far below it unless the range expands through a table or is edited.
- The fill color does not appear: Look for another conditional-formatting rule affecting the same cells, or check whether the selected format actually includes a fill. Open Home > Conditional Formatting > Manage Rules to review rules and ranges.
- Rows alternate unexpectedly after filtering: The standard formula counts physical worksheet row positions, not visible records. Use the SUBTOTAL approach with a populated key column if visible rows must alternate.
- The formula produces an error: Check that it begins with an equals sign, has balanced parentheses, and uses straight quotation marks if any are present. The formulas here use no quoted text and use Excel’s comma argument separator.
To change a rule later, select a cell in the affected range and open Home > Conditional Formatting > Manage Rules. Check the “Show formatting rules for” setting so you are looking at the relevant selection or worksheet. From there, you can edit the formula, change the format, adjust the range, or remove a rule that is no longer needed.
Frequently Asked Questions
Can I highlight every other row without a helper column?
Yes. Conditional formatting evaluates the row formula directly, so you do not need to add a visible or hidden helper column. Select the target range, create a formula-based rule, and set a fill.
Should I select the header row too?
Usually, no. If row 1 contains headings and records start in row 2, apply the rule to the record range starting at row 2 and use row 2 as the anchor. Format the header separately so its appearance stays distinct.
Will new rows inherit the formatting?
They will if they are added inside the rule’s applied range. Excel tables are often more convenient for growing lists because table styles expand with the table. A normal conditional-formatting range may need to be extended if new rows are added beyond its boundary.
Can I use the rule to stripe columns instead?
Yes. For a left-to-right pattern, use the column number rather than the row number. With the selected range beginning in column A, an analogous formula is =MOD(COLUMN()-COLUMN($A$1),2)=0. The selected range’s first column is shaded, followed by every second column.
Does the rule change or reorder my data?
No. Conditional formatting changes only the appearance of cells. It does not insert rows, alter values, or sort the records. Sorting and filtering are separate worksheet operations.
Why use MOD rather than alternate fill colors manually?
MOD supplies a repeatable test for every row, so the pattern stays consistent when values change and is easier to extend than manually coloring individual rows. Manual fills can be adequate for a tiny, fixed display, but they are easy to disrupt when rows are inserted, deleted, or rearranged.
Related
- Append Queries in Power Query: How to Combine Tables
- Power Query Editor: What It Is, How It Works, and Why
- Excel Data Validation Icon: What It Looks Like and Does
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Right-Click Shortcut: Open the Context MenuIn Excel for Microsoft 365 on Windows, select a cell or range and press Shift+F10 to open its…
- Excel Lookup Function Examples: XLOOKUP, VLOOKUP and MoreExcel lookup functions find a value in one range and return a related value from another. For example,…
- Excel Date Minus Days: Formulas and ExamplesTo subtract calendar days from a date in Excel, subtract a number from the date cell: if the…
- COUNTIF Excel Cell Color: Methods That Work ReliablyExcel’s COUNTIF function cannot count cells by fill color on its own: its criteria are based on cell…
- Excel SUM and AVERAGE: Formulas, Examples, and TipsIn Excel, use SUM to add numbers and AVERAGE to find their arithmetic mean. With values in cells…
- Excel Time Conversion Formula: Hours, Minutes, and MoreIf A2 contains an Excel time, multiply it by 24 to get decimal hours: =A2*24. For decimal minutes,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.