Shade Groups of Rows With Conditional Formatting

Yes, you can shade entire groups of rows with conditional formatting in Excel and Google Sheets using a formula that identifies group boundaries, such as a change in a key column value. You enter the formula in the conditional formatting rule, apply it to the full row range, and the formatting fills every cell in each row that belongs to the selected group. This article explains the exact formulas, how to set them up step by step, and how to handle common issues like blank cells or filtered data.

Why Shade Groups of Rows

Alternating row colors (zebra striping) is a common way to improve readability, but it does not respect logical groups in your data. If you have a list of orders grouped by customer, or inventory grouped by category, a simple every-other-row pattern can split a group across two colors, making it harder to scan. Shading by group keeps each group visually unified, so your eye can quickly see where one group ends and the next begins. This is especially useful when a group spans many rows and you want to distinguish it from the neighboring group without adding borders or blank separator rows.

How the Formula Works

The core idea is to compare the value in the current row with the value in the row above. If they are different, a new group starts. You use a running count of these starts to assign a group number, then shade based on whether that number is even or odd. In Excel, the formula typically looks like this (assuming your key column is A and data starts in row 2):

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

In Google Sheets, the same formula works, or you can use a simpler approach with COUNTIF if your key column is sorted:

=ISODD(COUNTIF($A$2:$A2,$A2))

The first formula counts how many times the value changes from the row above, up to the current row. If that count is odd, the row gets shaded. The second formula counts how many times the current value has appeared so far; if that count is odd, the row is shaded. Both require that your data is sorted by the grouping column, otherwise the pattern will be inconsistent.

Step-by-Step Setup in Excel

Select the entire range you want to format, including the header row if you want it excluded. For example, select A2:F100.
Go to Home → Conditional Formatting → New Rule.
Choose Use a formula to determine which cells to format.
Enter the formula =MOD(SUMPRODUCT(($A$2:$A2<>$A$1:$A1)*1),2)=1. Make sure the first cell reference is absolute (with $) and the second is relative (without $) for the row part. The column letter should be absolute if your key column is fixed.
Click Format, choose a fill color, and click OK.
Apply the rule and check that the groups alternate correctly.

If your data starts in row 1 (no header), adjust the formula to compare A1 with a dummy row above, which you can simulate by using =MOD(SUMPRODUCT(($A$1:$A1<>"")*1),2)=1 but that is less common. Usually you have a header row, so starting at row 2 is typical.

Step-by-Step Setup in Google Sheets

Select the range, for example A2:F100.
Go to Format → Conditional formatting.
Under “Format cells if”, choose Custom formula is.
Enter =ISODD(COUNTIF($A$2:$A2,$A2)) if your data is sorted by column A.
Set the fill color and click Done.

Google Sheets also supports the SUMPRODUCT formula, but COUNTIF is often easier to read. The key is that the range in COUNTIF expands as the formula moves down, so it counts only the current group’s occurrences so far.

Choosing the Right Formula

Both formulas require sorted data. If your data is not sorted, the pattern will be random. If you cannot sort, you need a helper column that assigns a group number based on a unique identifier. For example, if you have an order ID that repeats for multiple rows, you can add a column that numbers each group sequentially, then shade based on that number. That approach is more robust but requires an extra column.

For most real-world data, sorting by the group key is acceptable. If you have a header row, always start your range at row 2 and adjust the formula accordingly. If you have blank cells in the key column, the formula may treat them as a new group, so either fill them or use a more complex formula that ignores blanks.

Common Problems and Fixes

All rows shaded or none shaded: Check that the formula references the correct column and that the relative/absolute references are correct. The first cell in the range must be the top-left of your selection, and the formula must be written for that cell.
Pattern breaks when filtering: Conditional formatting based on row position does not adjust when rows are hidden by filters. The shading remains tied to the original row numbers, so filtered groups may not alternate correctly. Use a helper column with a formula that recalculates based on visible rows, or accept the limitation.
Blank rows create unwanted shading: If your data has blank separator rows, the formula sees a change and starts a new group. Remove blank rows or modify the formula to ignore them, for example by adding a condition that checks if the cell is not empty.
Performance issues on large sheets: SUMPRODUCT over a large range can slow down Excel. If you have tens of thousands of rows, consider using a helper column with a simple IF formula, which is faster.

Alternatives to Conditional Formatting

If you do not want to use conditional formatting, you can manually shade rows using the Fill tool, but that is tedious and does not update when data changes. Another alternative is to use Excel’s built-in Table style with banded rows, but that only gives every-other-row shading, not group-based. A third option is to use a PivotTable with a “classic” layout, which often shades groups automatically, but that changes your data presentation. For most users, conditional formatting is the most flexible and dynamic solution.

Frequently Asked Questions

Can I shade based on a group that is not sorted?

Yes, but you need a helper column. Add a formula that assigns a group number whenever the key value changes, regardless of order. For example, in Excel, you can use =IF(A2=A1,B1,B1+1) in a helper column, then shade based on that column. This works even if the same group appears in non-contiguous blocks, but each block will get a different group number, so the shading will not be consistent across blocks.

Does this work with merged cells?

Merged cells complicate conditional formatting because only the top-left cell of a merged range holds the value. The formula will still work for the top row, but the rest of the merged cells will not evaluate correctly. It is best to avoid merged cells when using group-based shading.

Can I shade more than two alternating colors?

Yes, you can use multiple conditional formatting rules, each with a different MOD result. For example, use MOD(…,3)=0 for one color, =1 for another, and =2 for a third. This works with both the SUMPRODUCT and COUNTIF approaches.

Why does my formula work in one sheet but not another?

The most common reason is that the range or the starting row is different. The formula must match the first cell of your selection. If you select A2:F100, the formula should be written as if it is for cell A2. Also check that the column letter in the formula matches the column where your group key is located.

How do I remove the shading later?

Select the range, go to Conditional Formatting → Clear Rules → Clear Rules from Selected Cells. This removes all conditional formatting from that range without affecting other formatting.

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

More cards in this drawer

The whole Numbers and Powers drawer →