Watch Video – How to Color Alternate Rows in Google Sheets (with and without Conditional Formatting)
In case you prefer reading over watching a video, below is a written tutorial for you.
Google Sheets has been adding some awesome features lately.
Things that used to take a lot of time and know-how can now easily be done with a few clicks.
And one such thing is to color alternate rows in Google Sheets.
Now, Google Sheets has added new functionality that makes it super easy to highlight alternate rows (every other row) in Google Sheets. Earlier you had to use conditional formatting to get this done, but now it can be done in two clicks (both methods are covered in this tutorial).
In this tutorial, I will show you the quick in-built method to color alternate rows in Google Sheets, and the conditional method in case you want to color every third or fourth row.
Let’s get started!
Table of Contents
Color Alternate Rows In Google Sheets
Suppose you have a dataset as shown below and you want to highlight/color every alternate row in this dataset:
Below are the steps to color alternate rows:
- Select all the cells in the dataset (including the header)
- Click the ‘Format’ tab
- Click on ‘Alternating’ colors
- In the Alternating colors pane that opens on the right, make the following changes/selections:
- In the Styles option, make sure ‘Header’ option is selected. in case your data doesn’t have headers, deselect this option
- Select from any of the pre-made templates, or specify custom colors for Header, Color 1 and Color 2.
The above steps would instantly highlight every other row in the dataset (as shown below).
A few things to know when using the ‘Alternating colors’ functionality in Google Sheets:
- In case you already have some color applied to the cells, these will be overwritten by the ones you choose when highlighting using the ‘Alternating colors’ functionality. And when you remove the alternating colors (steps mentioned in the yellow box above), it will remove the alternate colors while keeping the original colors intact
- In case you want to change the color of the header or any specific cell or range, you can do that manually. It will simply overwrite the existing colors.
- If you expand the dataset and add more record at the bottom, Google Sheets will automatically highlight the alternate rows in the colors you specified. However, in case you delete records, the colors would stay and you would have to manually remove these.
- If you insert additional rows in the dataset, Google Sheets will automatically adjust the coloring.
Color Every Third/Fourth Row in Google Sheets
For highlighting/coloring alternate rows, the method shown above is best and fastest.
But what if you don’t want to color every other row. What if you want to color every third row or every fourth row?
In that case, you can use the ‘Alternating colors’ functionality of Google Sheets.
You need to use Conditional Formatting with a simple formula.
Suppose you have the following dataset and you want to highlight every third row in this dataset:
Below are the steps to use Conditional Formatting to color every third row in Google Sheets:
- Select the dataset for which you want to color every third row (excluding the header)
- Click the ‘Format’ tab
- Click on ‘Conditional formatting’ option. This will open the Conditional Formatting pane on the right
- In the Format rules drop-down, click on ‘Custom formula is’ option (you need to scroll down the list to get this option)
- In the ‘Value or Formula’ field below it, enter the following formula: =MOD(ROW(),3)–1=0
- Specify the formatting style. You can color every third row or can make it bold, italicize it, apply strikethrough format, etc.
- Click on Done
The above steps would apply the specified format/color to every third row in Google Sheets (as shown below).
How does this work?
Now let’s understand how this works.
The entire magic is in the below formula:
=MOD(ROW(),3)-1=0
The above formula uses the ROW function to get the row number of each cell. This row number is then used in the MOD formula.
MOD formula divides the given number with the specified divisor (which is 3 in our example) and gives us the remainder. So for the second row, the formula becomes =MOD(2,3)–1=0
So for the second row, the MOD formula returns 1, which is not equal to 0. So the first row in our dataset returns a FALSE.
Similarly, the second row would also return a FALSE and the third row will return a TRUE.
The same logic is applied to all the cells and only the ones in every third record will return TRUE.
And since Conditional formatting only applied the specified format to those cells for which the formula returns TRUE, every third row gets highlighted in the specified format.
The reason I have subtracted 1 from the MOD formula is that I am starting from the second row onwards.
You can use the same logic to highlight every fourth row in Google Sheets by using the below formula:
=MOD(ROW(),4)-1=0
Note: You can also use the same logic to highlight alternate rows as well, however, since Google Sheets has an in-built functionality, for alternating rows, it’s best to use the inbuilt one.
In case you don’t edit the formatting or want to delete it, click any of the cells in the dataset (that has the formatting applied), click on Format and then click on Conditional Formatting. This will open the conditional formatting pane where you can edit the rule or delete it.
I hope you found this tutorial about how to shade every other row in Google Sheets useful.
You may also like the following Google Sheets Tutorials:
- How to Delete Empty Rows in Google Sheets
- How to Filter By Color in Google Sheets
- How to Change Row Height In Google Sheets
- How to Wrap Text In Google Sheets
- How to Insert Multiple Rows in Google Sheets
- How to Freeze Rows in Google Sheets
- Apply Conditional Formatting based on Another Cell Value in Google Sheets
