Shade Alternating Groups (Not Just Rows)

Excel Formulas › Conditional Formatting

All versionsISODD

Banded rows are easy — but when data is grouped, you want the shading to change with each group, not every other row. A running count of group changes makes the bands line up with the data.

Quick formula: with a sorted category column A, add a helper that counts group changes, then shade by its parity:
Helper C2: =IF(A2=A1, C1, C1+1) CF rule: =ISODD($C2)
The helper increments each time the category changes; shading odd groups gives clean alternating bands per group.

Functions used (tap for the full reference guide):

ISODDSUMPRODUCT

The example

Rows grouped by region; each region gets one shade.

ABC
1RegionRepGrp #
2EastAnn1
3EastBo1
4WestCy2
5NorthDi3

The formula

A helper column counts groups; the rule shades odd ones:

C2: =IF(A2=A1, C1, C1+1) CF: =ISODD($C2) // bands change with each new region

How it works

The trick is a group counter, then shade by whether it’s odd:

  1. Make sure the data is sorted by the grouping column so each group is contiguous.
  2. Add a helper in C2: =IF(A2=A1, C1, C1+1) — it keeps the same number within a group and bumps up by 1 at each change. Start C1 at 0 or a header.
  3. Select the table and add the rule =ISODD($C2) (column locked) to shade every odd-numbered group.
  4. Hide the helper column. Now each group — whatever its size — is one solid band, alternating shade by shade.

No helper column? A self-contained rule uses SUMPRODUCT to count distinct groups above the row: =ISODD(SUMPRODUCT((($A$2:$A2<>$A$1:$A1))*1)). It’s slower on big sheets, but keeps the sheet clean.

Try it: interactive demo

Live demo

Edit regions (one per line, grouped); bands follow the groups.

Variations

Helper-free (SUMPRODUCT)

Count group changes inline:

=ISODD(SUMPRODUCT(($A$2:$A2<>$A$1:$A1)*1))

Plain banded rows

If you just want every other row:

=ISODD(ROW())

Band every N rows

Groups of 3, say:

=ISODD(INT((ROW()-2)/3))

Pitfalls & errors

Data must be sorted by group. If the grouping column isn’t grouped together, the bands will fragment. Sort first.

Lock the helper column. Use $C2 so every cell in a row reads that row’s group number; a relative C2 shifts across columns.

SUMPRODUCT version is slow. The helper-free formula recalculates a growing range per row — fine for hundreds of rows, sluggish for tens of thousands.

Practice workbook

📊
Download the free Shade Alternating Groups (Not Just Rows) practice workbook
A grouped table with the helper-column and SUMPRODUCT band rules, plain banded-row variant, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I shade alternating groups instead of alternating rows in Excel?
Add a helper that counts group changes, =IF(A2=A1, C1, C1+1), then use a CF rule =ISODD($C2) to shade odd groups. Sort by the grouping column first.
Can I do it without a helper column?
Yes — use =ISODD(SUMPRODUCT(($A$2:$A2<>$A$1:$A1)*1)), which counts distinct groups above each row inline. It's slower on large data.
How do I just band every other row?
Use =ISODD(ROW()) for simple alternating-row shading.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Banded rows · Distinct count by group · Highlight entire row

Function references: ISODD · SUMPRODUCT