ThisCalculator

Everyday

Zebra Stripes in Excel: Conditional Formatting Guide

Updated · Standard formula, shown below

This guide shows you how to apply zebra stripes (alternating row shading) to any Excel range using conditional formatting. You’ll learn the formula-based approach that automatically adjusts when you add, delete, or filter rows, and you’ll see how to make the stripes follow your data instead of staying static.

What You’ll Need

  • Microsoft Excel (2010 or later; the steps are identical in Excel for Microsoft 365, Excel 2021, 2019, and 2016)
  • A worksheet with data you want to format
  • Basic familiarity with selecting ranges and opening dialog boxes

Step-by-Step

  1. Select the range you want to apply zebra stripes to. This can be your entire data table (including headers) or just the body rows. If you want the stripes to extend beyond your current data, select a larger range now—the formatting will only appear on rows that meet the formula condition.
  2. Open Conditional Formatting: go to the Home tab, click Conditional Formatting in the Styles group, and choose New Rule.
  3. Choose the rule type: in the New Formatting Rule dialog, select Use a formula to determine which cells to format. This is the last option in the list.
  4. Enter the formula: in the formula box, type =MOD(ROW(),2)=0 to shade even-numbered rows, or =MOD(ROW(),2)=1 to shade odd-numbered rows. The MOD function returns the remainder after dividing the row number by 2, so it alternates between 0 and 1.
  5. Set the formatting: click the Format… button, go to the Fill tab, choose a light color (light gray or a pastel blue works well), and click OK. You can also set a font color or border here, but the fill is what creates the stripe effect.
  6. Apply the rule: click OK in the New Formatting Rule dialog. Your selected range now shows alternating row shading.
  7. Adjust for header row: if your first row is a header and you want the stripes to start on row 2, use this formula instead: =MOD(ROW()-1,2)=0. This shifts the pattern so row 2 is shaded, row 3 is not, and so on.
  8. Make stripes follow filtered data: if you often filter your table, the basic formula will keep the original row positions, so after filtering, adjacent rows may not alternate. To fix this, use the SUBTOTAL formula: =MOD(SUBTOTAL(3,$A$2:$A2),2)=0 (adjust $A$2 to the first data cell in your range). This counts visible rows and shades every other visible row.
  9. Apply to a table object: if your data is in an Excel Table (Insert > Table), you can skip conditional formatting entirely—use the built-in table styles with banded rows. But if you need more control (e.g., only shade certain columns), apply the conditional formatting formula to the specific columns.

Common Mistakes

  • Using absolute row references: the formula =MOD(ROW($A$2),2)=0 will always evaluate the same row (row 2) for every cell, so the entire range gets the same formatting. Always use ROW() without an argument so it evaluates each row individually.
  • Applying the rule to the wrong range: if you select the entire column (e.g., A:A), the stripes will apply to all 1,048,576 rows, which can slow down Excel and create odd patterns when you have data in only part of the column. Select only the range with data.
  • Forgetting to adjust for headers: if you use =MOD(ROW(),2)=0 and your header is in row 1, the header will be shaded if row 1 is even (it isn’t, but row 2 will be). This might look fine, but if you want the header unshaded, use the row-shift formula.
  • Using a static fill instead of conditional formatting: manually filling every other row with a color works, but it doesn’t update when you insert or delete rows. Conditional formatting is dynamic—that’s the whole point.
  • Overlapping rules: if you already have other conditional formatting rules on the same range, the zebra stripe rule might conflict. Check the order of rules in Manage Rules and use the “Stop If True” checkbox if needed.

Tips

  • Use a light fill color: zebra stripes are meant to improve readability, not distract. A very light gray (e.g., #F2F2F2) or a soft blue (#DDEBF7) works well on both screen and print.
  • Combine with borders: if you want a cleaner look, add a thin white border to the shaded cells to create a gap between rows. In the Format Cells dialog, go to the Border tab and set a white bottom border.
  • Shade columns instead: you can create vertical stripes (column banding) by using =MOD(COLUMN(),2)=0 in the same way.
  • Use a named range: if your data range changes frequently, define a named range (e.g., “DataRange”) and use it in the conditional formatting rule. Then you only need to update the named range, not the rule.
  • Copy the formatting to other sheets: use Format Painter to copy the conditional formatting to another range. Select a cell with the formatting, click Format Painter, then drag over the new range.
  • Test with different starting rows: if your data starts on row 5, adjust the formula to =MOD(ROW()-5,2)=0 to make row 5 the first shaded row.

Troubleshooting

The stripes don’t appear at all. First, check that you selected the correct range and that the formula is entered without extra spaces. The formula must start with an equals sign. Also, make sure the fill color you chose is actually visible—if you picked white, you won’t see anything on a white background.

The stripes appear but they’re all the same color. This means your formula is not evaluating per-row. Check that you used ROW() without an argument, not ROW($A$2). Also, ensure you didn’t accidentally use absolute references in the formula.

The stripes shift when I filter or sort. The basic formula uses the physical row number, so sorting or filtering will break the alternation. Use the SUBTOTAL formula mentioned in step 8 to make the stripes follow visible rows.

The stripes apply to the entire sheet. You likely selected the whole sheet before creating the rule. Click on the range you want, then re-create the rule. You can also edit the “Applies to” field in the Conditional Formatting Rules Manager.

The stripes don’t update when I insert a new row. Conditional formatting should update automatically when you insert rows within the range. If it doesn’t, check that the range in the rule includes the new rows—sometimes Excel doesn’t expand the range if you insert rows at the very edge of the range. Re-select the range and update the “Applies to” field.

The rule works but the colors are too dark or too bright. Go back to the rule, click Format, and choose a lighter fill color. You can also use a custom color by clicking “More Colors” and entering an RGB value.

Frequently Asked Questions

Q: Can I use zebra stripes with an Excel Table?
A: Yes, Excel Tables have built-in banded row options. Select your table, go to Table Design, and check “Banded Rows”. If you need custom colors, use conditional formatting instead.

Q: How do I make the stripes start on a specific row number?
A: Use the formula =MOD(ROW()-N,2)=0, where N is the row number before your first data row. For example, if your data starts on row 3, use =MOD(ROW()-3,2)=0.

Q: Will the stripes print correctly?
A: Yes, conditional formatting prints by default. If you don’t see them in Print Preview, check that “Print” is enabled in the formatting options (it is by default). You can also choose to print in black and white if you want the stripes to appear as gray shades.

Q: Can I apply zebra stripes to only certain columns?
A: Yes. Select only the columns you want to format (e.g., A:C) before creating the rule. The formula will still evaluate each row, but only the selected cells will be filled.

Q: How do I remove zebra stripes?
A: Select the range, go to Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Or open Manage Rules, select the rule, and delete it.

Q: Why does my formula use SUBTOTAL and what does the 3 mean?
A: SUBTOTAL(3, range) counts the number of visible cells in the range. The 3 is the function code for COUNTA (count non-empty cells). This is used in the filtered-row formula to count only visible rows, so the stripes alternate correctly after filtering.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.