Reading data from Google Sheets spreadsheets can be difficult if you have many rows and columns that you must scroll to view data (and remember which row you are looking at). That is why on many occasions, the authors of large spreadsheets use alternate colors to help differentiate the data in each row.
Google has made it easy to add optional colors to your spreadsheet, with extra features to make the final product look great.
Here’s what you can do with the optional color formatting feature in Google Sheets:
- Automatically apply alternate colors to cell range
- Specify whether the category contains headers and footnotes (and format them differently)
- Specify a color theme (choose one already created or create a custom color set)
Color optional rows in Google Sheets
Suppose you have a dataset, and you want to highlight/color each optional row in this dataset:
Below are the steps for painting alternate rows:
- Select all cells in the dataset (including headers)
- Click on the ‘Format’ tab.
- Click on ‘Optional’ colors.
- In the Optional Color panel that opens on the right, make the following changes/selections:
- Make sure the ‘Header’ option is selected in the Styles option. If your data does not have a header, deselect this option.
- Choose from any already-created templates or specify custom colors for the header, colors 1 and 2.
The steps above will instantly highlight every other row in the dataset.
Here’s what to look for when using the ‘Alternating Colors‘ functionality in Google Sheets:
- If you have already applied some color to the cell, it will be overwritten by the one you selected while highlighting using the ‘Alternating Colors’ functionality. And when you remove the alternate colors, they will remove the alternate colors while keeping the original colors intact.
- If you want to change the color of the header or any particular cell or category, you can do so manually. It will only overwrite existing colors.
- If you expand the dataset and add more records at the bottom, Google Sheets will automatically highlight the optional rows in the colors you specify. However, if you delete the history, the colors will remain, and you will have to remove them manually.
- If you add extra rows to the dataset, you can change the color of Google Sheets.
Paint every third/fourth row in Google Sheets
To highlight / alternate color rows, the method shown above is the best and fastest.
But if you don’t want to color every other row. What if you’re going to paint every third row or fourth row?
In that case, you can use Google Sheets’ Alternating Colors functionality.
It would be best if you used conditional formatting with a simple formula.
Let’s say you have the following dataset and you want to highlight every three rows of this dataset:
The following steps apply to using conditional formatting to color every third row in Google Sheets:
- Select the dataset for which you want to color every third row (excluding headers)
- Click on the ‘Format’ tab.
- Click on the ‘Conditional Formatting option. It will open the Conditional Formatting pane on the right.
- In the Format Rules drop-down, click on the ‘Custom Formula Is’ option (you will have to scroll down the list to get this option).
- Enter the following formula in the ‘Value or formula’ area underneath it: 0= MOD (ROW (), 3) -1
- Specify formatting style. You can color or highlight every third row, apply diagonal, strikethrough formats, etc.
The steps above will apply the specified format/color to every third row in the Google Sheets.
How does it work?
Let us now understand how this works.
The real magic is in the following formula:
= MOD (ROW (), 3) -1 = 0
The formula above uses the ROW function to get the row number of each cell. This row number is then utilized in the MOD form.
The MOD formula divides the given number by the specified divisor (3 in our example) and gives us the remainder. So for the second row, the formula would be = MOD (2,3) -1 = 0
So for the second row, MOD gets formula 1, which is not equal to 0. So the first row in our dataset gets FALSE.
And since conditional formatting only applies the specified format to the cell for which the formula gives TRUE, every third row is highlighted in the prescribed format.
I subtracted one from the MOD formula because I start from the second row.
You can use the same logic to highlight every fourth row in Google Sheets using the following formula:
0= MOD (ROW (), 4) -1
If you are not editing the formatting or want to delete it, click on any cell in the dataset (in which configuration is applied), click Format, and click Conditional Formatting. It will open the Conditional Formatting pane, where you can edit or delete the rules.