Color Every Other Row
The quickest route is to make the range a table: select it and press Ctrl+T, and banded rows arrive switched on as part of the style. Where the data has to stay a plain range, use conditional formatting instead — Home > Conditional Formatting > New Rule > Use a formula, enter =MOD(ROW(),2)=0 and pick a fill. The difference shows the moment you filter: a table re-bands whatever rows are still visible, while the formula reads sheet row numbers and the stripes come out uneven.
The formula
=MOD(ROW(),2)=0 shade even-numbered rows (use =1 for the odd ones)
=MOD(COLUMN(),2)=0 the same idea applied to columns
=ISEVEN(ROUNDUP((ROW()-1)/3,0)) shade in blocks of three rows, counting from row 2
Ctrl+T make it a table instead — banded rows are on by defaultA worked example
A2:F150 holds a plain list with headers in row 1, and every second data row should be grey.
Select A2:F150, Home > Conditional Formatting > New Rule > Use a formula, enter =MOD(ROW(),2)=0 and set a grey fill
Rows 2, 4, 6 and onwards take the grey fill, and the rule travels with the range as you add rows inside it. Filter the list afterwards and the shading looks wrong, because the rule reads each row's real number on the sheet rather than its position among the visible rows.
Which one do I need?
| If you want to… | Use |
|---|---|
| Banding a list you are happy to convert into a table | Select it and press Ctrl+T; Table Design > Banded Rows toggles the stripes |
| Banding a range that must stay a plain range | Conditional formatting with =MOD(ROW(),2)=0 across the range |
| Shading in blocks of three rows rather than every other one | =ISEVEN(ROUNDUP((ROW()-1)/3,0)) as the rule, changing the 3 to whatever the block size is |
| Striping columns instead of rows | The same rule with COLUMN() in place of ROW(): =MOD(COLUMN(),2)=0 |
| Keeping the stripes correct after filtering | Only a real table re-bands the visible rows — a MOD(ROW()) rule cannot see the filter at all |
| Doing it in Google Sheets | Format > Alternating colours, which is built in and has separate header and footer options |
Frequently asked questions
How do I alternate row colours without using a table?
Conditional formatting. Select the range, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter =MOD(ROW(),2)=0, click Format and pick a fill. The rule only covers the range you had selected, so select generously — or apply it to whole columns such as A:F — if the list is going to grow.
Why is my banding wrong after I filter the list?
MOD(ROW(),2) reads a row's position on the sheet, and filtering hides rows without renumbering them, so two shaded rows can end up next to each other. Sorting is fine, because the shading belongs to the row numbers rather than to the data. Only an Excel table re-bands itself to match what is actually visible.
How do I remove alternating colours someone else applied?
It depends how they were made. In a table, untick Table Design > Banded Rows, or use Convert to Range and clear the style. For conditional formatting, open Home > Conditional Formatting > Manage Rules, switch the scope box to This Worksheet and delete the MOD rule. If it was a fill applied by hand, Home > Fill Color > No Fill is the answer.
Will the shading cover rows I add at the bottom?
A table extends by itself — type in the row directly beneath it and the new row joins the table with the banding already on. A conditional-formatting rule only covers the range it was given, so check Manage Rules and widen the Applies To box, or set the rule on whole columns from the start so there is nothing to maintain.
New guides and tools, once a month
DE + EN · double opt-in · no spam