Adding alternating colors every 2 rows in Google Sheets is easy. Simply open the menu and click Format > Alternating colors. Select a color scheme or theme and you’re done! But what if you want to alternate colors every 3, 4 or even 5 rows?
You can’t do that using the in-built menus. Your best option is to use conditional formatting rules with a custom formula. But it’s pretty simple, I’ll show you how below.
If you want to jump right in and see how we made these alternating colors, make a copy of the sheet for this article.
How to alternate colors every 2 rows easily using ‘alternate colors’
Alternating colors every 2 rows, with or without a header row, is very easy in Google Sheets. Simply select the cells, rows or columns containing the data you want to format and open the Format menu from the toolbar and click Alternating colors.
After clicking this option sheets will format the selected area with the default alternating colors styles, like this:
And the Alternating Colors toolbar will open on the right side of the screen, allowing you to customize the styles in any way you wish. You can add or remove the header row color, footer row color and change the alternating colors. There is also a list of pre-defined styles for you to choose from.
How to remove alternating colors
Once you’ve formatted your cells using the alternating colors option, there are 2 ways you can remove the formatting.
Option 1 – Click any of the cells formatted with an alternating color and use the Format > Alternating colors option from the menu to open the Alternating colors toolbar. The bottom of the toolbar should display a button or link to Remove alternating colors (shown below). Simply click that to remove the formatting.
Option 2 – Select the cells that are formatted with alternating colors and use the keyboard shortcut Ctrl+\ (⌘+\ on Mac). This is the keyboard shortcut for the menu option ‘Clear all formatting‘. Which will remove all formatting rules from the cells, alternating colors, conditional formatting and any other choices such as font, bold, etc.
How to alternate colors every 3 rows using a conditional formatting formula
If you need to alternate the color of every 3 (or more) rows. The best option is to create your own conditional formatting rules using a formula. Here’s a screenshot of how we accomplished 3 row conditional formatting showing the 3 rules applied to the data cells. Coloring of the header row has to be done manually with this method.
If you want to check it out, please take a copy of our Alternating Colors sheet. Let’s look at how this works in more detail.
A conditional format rule is a way of applying formatting to a cell based on rules. You can use simple rules such as ‘cell is empty’ or ‘cell contains‘ but to truly unlock the power of this type of formatting, you need to use the custom formula option. Like the one shown below.
How do custom formulas work? The easiest way to think about it is that you are writing a formula to apply the selected formatting if the result of the formula is TRUE for the FIRST ROW of your range only.
You can then apply this to any range of data and sheets will automatically update the cell references in the formula with the correct ones.
Let's break our formula down:=MOD(ROW(D2),3)=0Get the row index of cell D2 usingROW(), the result is 2. UseMOD()(the modulo operator function) to divide 2 by 3 and return only the remainder. Which is 2. Does 2 = 0? No, don't apply the formatting.
The key function is MOD(), modulo with a divisor of 3 will only return zero when a number is exactly divisible by 3. So, as this formula applies to row 2 onwards it will return zero and format the rows 3, 6, 9, 12, etc.
We use the same formula but simply apply an offset to format the other rows. Like this:
| Formula | Affects Rows | Color |
|---|---|---|
=MOD(ROW(D2),3)=0 | 3, 6, 9, 12 … | Light grey 1 |
=MOD(ROW(D2)-1,3)=0 | 1, 4, 7, 10 … | Light grey 2 |
=MOD(ROW(D2)+1,3)=0 | 2, 5, 8, 11 … | Light grey 3 |
As you can see, each rule targets every 3rd row. We could easily expand this to 4 or 5 or more rules to target different sets of rows. For example:
| Formula | Rows Formatted |
|---|---|
=MOD(ROW(D2)-2,5)=0 | 1, 6, 11, 16 … |
=MOD(ROWD2)-1,5)=0 | 5, 10, 15, 20 … |
=MOD(ROW(D2),5)=0 | 4, 9, 14, 19 … |
=MOD(ROW(D2)+1,5)=0 | 3, 8, 13, 18 … |
=MOD(ROW(D2)+2,5)=0 | 2, 7, 12, 17 … |
For alternating colors every 5 rows, we need 5 formulas and we change the modulo divisor to 5 and the offset appropriately. In the table above, you can see the rows these formulas would format.
How to remove alternate colors added with conditional formatting
To remove alternating colors added using conditional formatting rules from your sheet, there are 2 options.
Option 1 – Click any of the cells formatted with an alternating color and use the Format > Conditional formatting option from the menu to open the Conditional formatting toolbar. If you move your mouse over any of the rules, you should see a trashcan icon appear on the right of the rule. Click that to delete that rule and the formatting applied by it.
Option 2 – Select the cells that are formatted with conditional formatting and use the keyboard shortcut Ctrl+\ (⌘+\ on Mac). This is the keyboard shortcut for the menu option ‘Clear all formatting‘. Which will remove all formatting rules from the cells, conditional formatting and also any other choices such as font, bold, etc.