To highlight every other row in Google Sheets, select your data, click Format > Alternating colors, pick a style in the side panel, and click Done. Sheets shades every second row and can give your header row its own color. If you need more control, such as shading every third row or alternating columns, use conditional formatting with a custom formula like =ISEVEN(ROW()).
Both methods are below, along with how to change the colors, how to highlight every Nth row, and how to remove the banding later.
Method 1: Use Alternating colors
- Open your spreadsheet and select the cells you want to format. Click a cell inside your data to let Sheets detect the range, or drag to select a specific range.
- Click Format in the menu bar.
- Click Alternating colors. A panel opens on the right.
- Check that the Apply to range box shows the right cells, such as A1:F200.
- Turn Header on or off depending on whether your first row is a header. Turn on Footer if your last row is a totals row.
- Pick one of the Default styles, or scroll to Custom style to choose your own header, color 1 and color 2.
- Click Done.
New rows you insert inside the range get the banding automatically. If you add data below the range, open Format > Alternating colors again and extend the range, or select whole columns (such as A:F) from the start.
Method 2: Use conditional formatting
Conditional formatting lets you write your own rule. This is the way to go if you want to shade every third row, alternate columns, or keep the pattern when you sort.
- Select the range, such as A2:F200.
- Click Format > Conditional formatting.
- Under Format cells if, choose Custom formula is.
- Enter =ISEVEN(ROW()) to shade even-numbered rows, or =ISODD(ROW()) for odd-numbered rows.
- Under Formatting style, pick a fill color.
- Click Done.
Formulas for other patterns
| Pattern | Custom formula |
|---|---|
| Every other row (even rows) | =ISEVEN(ROW()) |
| Every other row (odd rows) | =ISODD(ROW()) |
| Every third row | =MOD(ROW(),3)=0 |
| Every Nth row | =MOD(ROW(),N)=0 (replace N with a number) |
| Every other column | =ISEVEN(COLUMN()) |
| Blocks of 2 rows | =ISEVEN(ROUNDUP(ROW()/2)) |
To start the pattern from a different row, change the row number in the formula. For example, if your data starts in row 2 and you want the first data row shaded, use =ISODD(ROW()-1).
Alternating colors vs conditional formatting
| Alternating colors | Conditional formatting | |
|---|---|---|
| Setup | A few clicks, no formulas | Needs a custom formula |
| Header and footer colors | Built in | Need separate rules |
| Every third or Nth row | No | Yes |
| Alternating columns | No | Yes |
| Works with other cell colors | Can be covered by manual fill colors | Overrides manual fill colors |
How to change or remove alternating colors
- Click any cell in the banded range.
- Click Format > Alternating colors.
- Change the style or colors, or click Remove alternating colors at the bottom of the panel.
To remove a conditional formatting rule, open Format > Conditional formatting, hover over the rule, and click the trash can icon.
Tips for readable banded rows
- Use light colors. Pale gray or a soft tint keeps text readable when printed or viewed on a phone.
- Freeze the header row. Click View > Freeze > 1 row so column names stay visible as you scroll.
- Remove old fill colors first. If some rows don’t change color, they probably have a manual fill. Select the range and set Fill color to Reset.
- Try a table. Converting your data with Format > Convert to table adds banded rows, filters and column types in one step.
Related: remove alternating colors in Google Sheets.
Related: lock a row in Google Sheets.
Related: copy conditional formatting in Google Sheets.
Frequently asked questions
Why don’t my alternating colors show up?
Cells with a manual fill color can hide the banding. Reset the fill color, or use conditional formatting, which takes priority over manual fills.
Do alternating colors stay correct when I sort or filter?
Alternating colors keep the pattern when you sort. When you filter, hidden rows can make the pattern look uneven, because the colors are based on the row position, not what’s visible.
Can I add alternating colors on the Google Sheets mobile app?
Setting up alternating colors and custom formulas is easiest on a computer. Once added, the colors show up in the mobile app too.
Kermit Matthews is a freelance writer based in Philadelphia, Pennsylvania with more than a decade of experience writing technology guides. He has a Bachelor’s and Master’s degree in Computer Science and has spent much of his professional career in IT management.
He specializes in writing content about iPhones, Android devices, Microsoft Office, and many other popular applications and devices.