Highlight Every Other Row: Excel & Google Sheets

Highlighting every other row (banded rows) is a formatting trick that makes spreadsheets easier to read by adding alternating shading to rows. You can do it automatically in both Microsoft Excel and Google Sheets using a built-in table style or a simple conditional formatting rule, and this guide shows you both methods step by step.

Formula

The core of automatic banding is the MOD function, which returns the remainder after division. The rule checks whether a row number is even or odd:

=MOD(ROW(),2)=0 highlights even rows; =MOD(ROW(),2)=1 highlights odd rows.

• ROW() – returns the row number of the cell (e.g., 1 for row 1).
• MOD(number, divisor) – remainder after dividing number by divisor.
• If the remainder is 0, the row number is even; if 1, it is odd.

For a header row that should stay unshaded, start the rule at row 2 or adjust the formula to =MOD(ROW()-1,2)=0.

Worked Example

Suppose you want to shade every other row starting from row 2 (so rows 2, 4, 6… are highlighted) in Excel. Select the range A2:C10, open Conditional Formatting → New Rule → Use a formula, and enter:

=MOD(ROW(),2)=0

Apply a light gray fill. Row 2 is even → remainder 0 → highlighted. Row 3 is odd → remainder 1 → not highlighted. Row 4 → highlighted, and so on. In Google Sheets, the same formula works under Format → Conditional formatting → Custom formula.

Why Banded Rows Help

Alternating row colors reduce eye strain when scanning long lists of data. The human eye naturally follows a horizontal line better when there is a subtle background change between rows. This is especially useful for:

• Financial tables with many columns
• Attendance sheets or rosters
• Data entry logs where you need to avoid skipping a line
• Printed reports where the shading helps keep your place

Banding is purely visual—it does not change any values, sorting, or filtering.

Method 1: Excel Table Styles (Quickest)

If you do not need a fully custom look, Excel’s built-in table styles do the banding for you:

1. Select any cell inside your data range.
2. Press Ctrl+T (or go to Insert → Table).
3. In the Table Design tab, check the Banded rows box.
4. Pick a style from the gallery—most have alternating shading.

The table automatically extends formatting when you add new rows, and the banding stays intact even after sorting or filtering.

Method 2: Conditional Formatting (More Control)

For a range that is not a formal table, use conditional formatting:

1. Select the range you want to format (e.g., A2:F100).
2. In Excel: Home → Conditional Formatting → New Rule → Use a formula. In Sheets: Format → Conditional formatting → Custom formula is.
3. Enter =MOD(ROW(),2)=0 (for even rows) or =MOD(ROW(),2)=1 (for odd).
4. Set a fill color and click OK.

This method lets you choose any color, apply banding to only part of the sheet, and combine with other rules.

How to Band by Group (Every Time a Value Changes)

Sometimes you want shading to change only when a column value changes, like grouping by department or category. Use a helper column with a running count:

1. Add a helper column (e.g., column D). In D2 enter =MOD(IF(A2=A1,D1,0)+1,2) and copy down.
2. This gives 1 for the first row of a new group, 0 for the second, 1 for the third, etc.
3. Then apply conditional formatting to the data range using =$D2=1 (or =0) as the formula.

Now every time the value in column A changes, the banding resets.

Common Mistakes and Fixes

• Wrong row reference: If your data starts at row 5, =MOD(ROW(),2)=0 will shade rows 6, 8, 10… which may be fine, but if you want row 5 to be the first shaded, use =MOD(ROW()-4,2)=0.
• Formatting applies to entire columns: If you select entire columns, the rule will shade every other row across the whole sheet. Always select only the range you need.
• Header row gets shaded: Add a separate rule for the header or start your range at row 2.
• Copy-pasting breaks the rule: When you copy cells with conditional formatting, the rule moves with them. Use Paste Special → Formats to copy only the shading.
• Filtering hides the pattern: If you filter rows, the banding may appear irregular because the hidden rows are still there. Table styles handle this better than manual conditional formatting.

Alternatives and Related Tricks

If you do not want to use formulas, you can manually select alternating rows and fill them, but that is tedious and breaks when you insert or delete rows. Other related options:

• Banded columns: Use =MOD(COLUMN(),2)=0 to shade every other column.
• 3-color banding: Use two separate rules with different colors for even and odd rows.
• Dynamic range: Use a named range or an Excel Table so new rows automatically get the shading.
• Google Sheets “zebra” formatting: Sheets has a built-in “Alternating colors” option under Format → Alternating colors, which is even simpler than conditional formatting.

Frequently Asked Questions

Why does my banding stop after I sort or filter?

Conditional formatting rules are based on row numbers, not values. When you sort, the row numbers change, so the shading follows the new row positions. If you want the banding to stay with the data, use an Excel Table (which keeps banding intact) or add a helper column that assigns a group number based on your data.

Can I band every other row without a header?

Yes. Just start your selection at row 1 and use =MOD(ROW(),2)=0. Row 1 will be shaded if it is even (which it is), so the first row will be highlighted. If you want the first row unshaded, start at row 2 or use =MOD(ROW()-1,2)=0.

How do I remove banding later?

Select the range, go to Conditional Formatting → Clear Rules → Clear Rules from Selected Cells. Or if you used a table style, switch to “None” under Table Design styles.

Does banding affect printing?

Yes, the shading will print if your printer is set to print background colors. If you do not want it in print, uncheck “Print” under Page Setup → Sheet for gridlines, or set the fill to a very light gray that still prints as a light tint.

Can I band every 3 rows instead of 2?

Yes. Use =MOD(ROW(),3)=0 to shade every third row. For a repeating pattern of two shaded then one unshaded, you would need a more complex formula like =MOD(ROW(),3)=0 for one row and =MOD(ROW(),3)=1 for another.

Related

• Chromecast 4K Support: What It Means & How to Get It
• Payment Gateway Companies: What Evidence Supports
• 3D Printing Nuclear Reactor Components: How-To

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0 | 100000
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2