How to Highlight Every Other Row in Google Sheets (Alternate Row Color)

Looking for an easy and efficient way to highlight or color every other row of your Google sheet? You are in the right place. This article will cover two distinct methods of how to alternate row Colors in Google Sheets.

The first approach uses the alternating colors option, while the second involves using the conditional formatting menu’s custom formula option. We will also review more advanced methods for highlighting rows, such as conditional formatting based on specific rules and conditional formatting combined with functions.

Sounds interesting? Let’s dive in. 😊

Benefits of Row Highlighting

It might be helpful to highlight every other row in a spreadsheet to make your data easier to read and more organized. You may construct a visual hierarchy that makes it simpler to rapidly scan and understand the data in your spreadsheet by using various formatting choices on alternate rows.

The ability to group comparable data, making it simpler to see patterns and trends, is one of the key benefits of highlighting every other row. This is helpful when working with huge data sets or evaluating data over time. Highlighting every other row can also assist in visually breaking up a vast document, making it less intimidating.

Another advantage of doing so is that finding mistakes or discrepancies in your data may be more straightforward. For instance, if the rows are marked differently, it will be easier to spot if you mistakenly input the same information twice. This helps you in finding and fixing any errors in your data rapidly.

Highlight Every Other Row in Google Sheets

Many organizations use Google Sheets to maintain and organize official data in a manageable manner. There is a straightforward manner to highlight alternate rows in Google Sheets. You can follow these steps:

Select the dataset.
Go to Format Menu and Select Alternating Colors.
Select the color scheme from the pre-set styles or customize it according to your desire.
Click Done.

Let’s discuss these steps in detail.

First, select the dataset for which you need to highlight rows, and then click on Format Menu from the top Menu bar. From the menu displayed, select Alternate Colours.

Highlight Every Other Row in Google Sheets using alternating colours

When you select Alternating Colors, a window pane will pop up at the right side of the sheet and show you multiple highlighting options.

The pictures above show the alternating color window showing multiple options. Let’s discuss all of these options:

Cell Range Selection: This input field specifies the range of cells selected as a dataset.
Header and Footer: This option specifies whether your dataset has a header or footer row.
Pre-set Styles: These are some alternating color styles available. These styles include a color for the header row and a color for alternating rows.
Custom Styles: If you want to customize the colors of your choice, then you can choose custom styles and specify the colors you want.
Remove Alternating Colors: If you have applied alternating color formatting, you can remove it anytime by this option. This will revert the row colors to the original ones.

When all of these options are set, this would instantly highlight every other row in the dataset like this:

When you use alternating colors, there are some points to ponder:

If you have previously applied color to the cells, it will be replaced by the colors you select when highlighting using the “Alternating colors” option. And removing the alternating colors will remove the alternate colors while leaving the original colors intact.
You may alter the color of the header, as well as any single cell or range, manually. It will just replace the current colors.
When you extend the dataset and add more entries at the bottom, Google Sheets will highlight the alternate rows in the colors you specify. However, if you delete records, the colors will remain and must be manually removed.

Highlight Every Third or Fourth Row in Google Sheets: Formula method

The method discussed in the previous section is the fastest and easy to use, but the problem is that it is used only when you need to highlight every alternative row. It doesn’t allow highlighting every third, fourth, or so on.

For highlighting every third or fourth row, you need to use the Conditional Formatting feature and apply simple formulas to highlight rows.

The following steps are followed to apply conditional formatting

Select the dataset for which you want to highlight rows.

Highlight Every Third or Fourth Row in Google Sheets: Formula method

Open the “Format” Menu and select “Conditional Formatting.”

This will open the Conditional Formatting option pane on the right side. In that option pane, select “Custom Formula” in the Format rules menu.

In the “Value or Formula” text field, type in the following formula:

=MOD(ROW(),3)-1=0

Afterward, select the color or any other formatting style you need to apply to the rows from the “Formatting Style” options and click “Done.” This will color every third row in the dataset.

Formula Explanation

Let’s have a look at how this works.

The following formula contains the complete magic:

=MOD(ROW(),3)-1=0

The ROW function is used in the above formula to get the row number of each cell. The MOD formula then uses this row number.

The MOD formula divides the provided integer by the specified divisor (in this case, 3) and returns the remainder. As a result, the formula for the second row becomes like this:

=MOD(2,3)-1=0

The MOD formula thus gives 1, which is not equal to 0, for the second row. As a result, the first entry in our dataset returns FALSE. The same logic is repeated to all cells, with only the ones in every third row returning TRUE.

Furthermore, every third row is highlighted in the requested format since conditional formatting only applies the format to cells for which the formula returns TRUE. Since starting from the second row, we’ve deducted 1 from the MOD calculation.

Using the formula below, you can highlight every fifth row in Google Sheets using the same approach:

=MOD(ROW(),5)-1=0

This formula can also be used to highlight alternate rows. Since Google Sheets already has a feature for alternating rows, using it rather than using the same logic to highlight alternative rows is preferable.

Highlight Rows in Dynamic Sheets

Dynamic sheets are those that change all the time. You must repeat the process each time you edit the data in your sheet if you apply Google Sheets’ alternate row colors using the alternating color option.

Instead of making all this effort, you can make your data range’s colors alternate dynamically.

You must use conditional formatting for alternating colors to do this. Here’s how to use dynamic alternating colors to color every other row in Google Sheets:

Select all the columns in your dataset and go to Format Menu to select Conditional Formatting.

In the Conditional Formatting window, select the “Custom Formula” and type in the following formula:

=AND(NOT(ISBLANK($A2)),ISEVEN(ROW()))

You need to specify the first cell of your dataset in the “ISBLANK” formula. This formula will first evaluate if a row is blank or not. If not, then highlight it if it is even no row.

Select the color you need to apply on the rows and click “Done”. This will color all the even number rows.

Adding a single new row to this table will not highlight the row. However, if you add more than one row, it will apply these conditional formatting rules to the new rows.

Advanced Techniques for Highlighting Cells

Once you’ve mastered highlighting every other row, you may develop your spreadsheet formatting using several more complex methods. For instance, using conditional formatting, you may highlight rows depending on particular criteria, such as cells that contain a specific phrase or fall inside a given range.

Using Multiple Rules in Conditional Formatting

You can apply multiple rules for conditional formatting in your data. For example, we need to highlight cells with different colors that fall in different ranges. This helps us in quickly identify the data. Consider the data of some order details:

This contains the data of different products’ orders and their prices. Suppose we need to consider if the total amount exceeds 500. We can apply a conditional formatting rule: if a value goes greater than 500, then highlight that cell to give us an alarm.

We can apply multiple conditional formatting rules if it is greater than 500, then highlight it with light red; if the order exceeds more and becomes greater than 700, then highlight it with medium red. If it reaches above 1000, then highlight it with deep red. The following steps will be followed:

Select the data range you want to apply conditional formatting and click on format ->Conditional Formatting
In the Conditional Formatting window, Click on “Add another rule.”

Set Format rules to “Is between” in the options pan and specify the value like in the image below:

After adding the first rule, again click on “Add another Rule”, like in the image below:

In the options pane, add the required values like in the previous step and set the rule.

After all the rules have been added, the dataset will look like this:

Using Color Scales in Conditional Formatting to highlight every other row

We can also use color scales in conditional formatting to apply different colors to some range of data. For example, we can apply color scales to the total price column in our previous example data.

Select the column and open the Conditional formatting window.

In this window, we selected green for minimum value and red for maximum value. The values in between will have different shades of red and green depending on their value.

The output will look like this:

Conclusion

This tutorial demonstrated using the custom formula option and the alternating colors option as two distinct ways to highlight every other row in a Google Sheet.

Additionally, it covered several advanced ways for highlighting rows, including conditional formatting based on predefined criteria and the “Color Scale” options. The ideal solution is the one that fulfills your needs and enhances the readability & structure of your data.