Shade Alternating Row Groups: How To

Shading alternating groups of n rows in a spreadsheet means highlighting every block of n consecutive rows with a fill color, leaving the next block of n rows unshaded, and repeating that pattern down the sheet. You enter the number of rows per group, and the formula-based conditional formatting rule does the shading automatically. This article explains the exact formulas for Excel and Google Sheets, how to adapt them for any group size, and how to troubleshoot the most common problems.

Formula

The core idea is to convert each row number into a group number, then test whether that group number is odd or even. The formula used in conditional formatting is:

=MOD(CEILING(ROW()/n,1),2)=1

• ROW() – returns the current row number (e.g., 1, 2, 3…).
• n – the number of rows per group (your input).
• CEILING(ROW()/n,1) – rounds the division up to the nearest whole number, giving the group number. For n=3, rows 1–3 give group 1, rows 4–6 give group 2, etc.
• MOD(…,2) – returns the remainder when the group number is divided by 2. If the remainder is 1, the group is odd; if 0, it is even.
• =1 – the condition that triggers shading for odd groups. Change to =0 to shade even groups instead.

In Google Sheets, the same formula works, but you may need to use ROW() relative to the first row of the range. For example, if your data starts at row 2, use =MOD(CEILING((ROW()-1)/n,1),2)=1 so that row 2 is the first row of group 1.

How to Apply the Rule in Excel

1. Select the range you want to format (e.g., A2:Z100).
2. Go to Home → Conditional Formatting → New Rule.
3. Choose “Use a formula to determine which cells to format.”
4. Enter the formula, replacing n with your group size (e.g., =MOD(CEILING(ROW()/3,1),2)=1).
5. Click Format, pick a fill color, and click OK.

If your data starts at row 1, the formula works as is. If it starts lower, adjust the ROW() offset as described above.

How to Apply the Rule in Google Sheets

1. Select the range (e.g., A2:Z100).
2. Click Format → Conditional formatting.
3. Under “Format rules,” choose “Custom formula is.”
4. Enter the formula, e.g., =MOD(CEILING(ROW()/3,1),2)=1 (adjust for starting row).
5. Set the fill color and click Done.

Google Sheets automatically applies the formula to the entire selected range, so you don’t need to copy it down.

Choosing the Right Group Size

The group size n depends on your data. Common choices:

• n=1 – classic alternating single rows (every other row shaded).
• n=2 – pairs of rows shaded together, often used for two-line entries.
• n=3 – blocks of three, useful for quarterly data or groups of three items.
• n=5 – weekly blocks when each week has five workdays.

There is no “correct” value – it depends on how your data is organized. If your data has a header row, you usually start the pattern at the first data row, not the header.

Common Mistakes and Fixes

• Shading starts on the wrong row. If your data starts at row 5, the formula =MOD(CEILING(ROW()/n,1),2)=1 shades rows 1–n, not rows 5–5+n. Fix: subtract the offset: =MOD(CEILING((ROW()-4)/n,1),2)=1.
• Pattern shifts when you sort or insert rows. Conditional formatting is based on row numbers, so sorting changes which rows are shaded. To keep shading with data, use a helper column with a stable identifier (like an ID) and base the formula on that column’s values instead of ROW().
• Entire row doesn’t shade. If you apply the rule to a range like A2:C100, only those columns shade. To shade entire rows, select the full row range (e.g., A2:Z100) or use the formula with a relative column reference like =MOD(CEILING(ROW()/n,1),2)=1 applied to the whole sheet area.
• Formula works in one cell but not in conditional formatting. Conditional formatting formulas are evaluated as if they were entered in the top-left cell of the selected range. Always write the formula relative to that cell, not absolute (no $ signs unless needed).

Alternatives to Conditional Formatting

If you prefer not to use formulas, you can:

• Use Excel’s built-in “Banded Rows” table style. Convert your data to a table (Ctrl+T) and choose a style with banded rows – but this only does single-row bands, not groups of n.
• Use a helper column. Add a column with the formula =MOD(CEILING(ROW()/n,1),2), then use AutoFilter or a simple fill to color the groups manually. This is more work but gives you full control.
• Use Google Sheets’ “Alternating colors” feature. This also only supports single-row bands, but you can set a custom formula for groups.

Conditional formatting is the most flexible because it automatically updates when you change n or add rows.

Performance and Limits

Conditional formatting formulas are recalculated whenever the sheet changes. For very large sheets (tens of thousands of rows), this can slow down performance. To minimize lag:

• Apply the rule only to the used range, not entire columns (e.g., A2:Z1000 instead of A:Z).
• Avoid using volatile functions like OFFSET or INDIRECT in the formula.
• If performance is a problem, use a static fill instead of conditional formatting.

There is no hard limit on the number of conditional formatting rules, but too many complex rules can make the sheet sluggish.

Frequently Asked Questions

Can I shade groups of n rows starting from a specific row? Yes. Adjust the formula to subtract the offset of the first data row. For example, if data starts at row 2, use =MOD(CEILING((ROW()-1)/n,1),2)=1.

How do I shade even groups instead of odd? Change the final comparison from =1 to =0. For example, =MOD(CEILING(ROW()/n,1),2)=0 shades groups 2, 4, 6, etc.

Can I use this to shade columns instead of rows? Yes, replace ROW() with COLUMN() in the formula. The logic is identical, but applied horizontally.

Why is my pattern off by one after sorting? Conditional formatting uses the row position, not the data content. If you sort, the shading stays with the row numbers, not the original data. To keep shading with data, use a helper column with a sequential number and base the formula on that column.

Does this work in LibreOffice Calc? Yes, the same formula works in LibreOffice Calc. The menu path is different (Format → Conditional → Condition), but the formula syntax is compatible.

Related

• What Is a Cell Reference? Excel & Sheets Guide
• Standalone vs Embedded Charts: Key Differences
• Unhide Chat in Viber: Step-by-Step Guide

in n | Rows per group | rows | 3 | 1 | 100
out group_index | Group number | - | ceil(row() / n) | 0
out is_shaded | Shade this group? | - | mod(ceil(row() / n), 2) | 0