How to Highlight Every Other Row in Google Sheets (2022)

Make your Google spreadsheet easier to read by highlighting every other row in different colors...

Shivam Kumar blogger image
Shivam Kumar
Updated on :

If you are working with a lot of data in Google Sheets, the stacks of white rows can dull your senses.

Shading every other row in Google Sheets can produce good contrast between those white rows and make your data easier to read and present.

In this tutorial, I will show you how to highlight every other row in google sheets (step-by-step)

Ready to dive in? Let’s get started…

Manually Highlight Every Other Row in Google Sheets

The easiest way to highlight every other row is to select alternate rows and fill in the cells with the color of your choice.

To select multiple rows, all you have to do is press the CTRL button and click on the row headers of each alternating row.

Select rows to highlight

Once you’ve selected all the alternate rows, click on the Fill Color icon appearing in the formatting menu.

A drop-down menu of different colors will appear. Select the color you want to use. You can even find more colors after clicking on the Pencil icon appearing near the custom color option.

Fill background color

That’s it… This will apply color to alternate rows in your Google spreadsheet.

Highlight alternate rows in Google Sheets

This is definitely an easy way to color alternate rows in Google Sheets, but it does have some limitations.

First of all, it can be really time-consuming when you have thousands of rows to highlight.

Secondly, since the background color is hard-coded if you change the location of the rows by applying sorting or filter, the background color will not sync with the data.

But, don’t worry!

I am going to share two other methods to highlight alternate rows especially when you have a large data set.

How to Color Alternate Rows in Google Sheets

In 2018, Google Sheets added a new functionality that makes it super easy to color alternate rows in Google Sheets.

Here’s how it works:

  1. Select the range of rows you want to apply alternating colors to. If you want to color alternate rows in your whole spreadsheet, simply press CTRL + A on your keyboard.
Select Range of rows
  1. Open the Format menu and click on Alternating Colors option appearing at the bottom.
Alternating colors in Google Sheets
  1. An Alternating colors pane will open on the right. Here are the different options you’ll find there:
  • First of all, you’ll see the range of rows you’ve selected in your table. If you need to edit your selection, you can do it from here.
  • Then select header and footer option if you have a header or footer in your table.
  • Next, you can choose a pre-made styles from the list of default styles.
  • If you don’t like one of the pre-made styles, you can also manually set colors to header & alternate rows under custom styles section.
shade every other row in google sheets
  1. Once you’re happy with the colors, click on the Done button below or the X in the top right corner.
Shade alternate rows in google sheets

That’s it… The above steps would instantly make every other row shaded in google sheets (as shown below).

Color alternate rows in google sheets

How to Highlight Every Other Row in Google Sheets With Conditional Formatting

Conditional formatting is an awesome way to change the layout and style of your Google spreadsheet.

Although conditional formatting is a little more complex than the above method, it offers the most freedom to create custom styles.

  1. To start, select the range of alternating rows you would like to highlight.
select range of cells
  1. Open the Format menu and click on Conditional Formatting option.
Conditional formatting in Google Sheets
  1. This will open the Conditional Formatting pane on the right. Under Format rules, click on the drop-down list and select Custom Formula is
custom format rules in conditional formatting
  1. In the ‘Value’ field, enter the following formula: =ISEVEN(ROW())
=ISEVEN(ROW()) formula
  1. Now, you can change the style by using the options under Formatting style. You can choose a background color for alternate rows as well as format the text styles.
Highlight every other rows in Google Sheets
  1. Click Done when finished. 

That’s it… The above formula will apply the formatting style to every even rows in your spreadsheet.

color every other row in google sheets

How to Highlight Every Nth Row in Google Sheets

What if you want to color every 3rd or 4th or maybe 5th row in your table?

Well, you can use the same conditional formatting feature with a different formula.

All you need to do is follow the same steps as above to open the conditional formatting pane and enter the following formula =MOD(ROW(),3)=0

highlight every third row in google sheets

This will apply conditional formatting to every third row in your spreadsheet. To color every fourth row, change the number 3 to 4

You can also change 0 in the formal to some other number.

For example the following formula will color every 5th row starting from the third one.



Final Thought on Coloring Alternate Rows:

Highlighting alternate (or even 3rd or 4th) rows in the spreadsheet doesn’t have to be hard. However, manually shading every row is a waste of time, especially when you are working with hundreds of rows and columns.

Using Google Sheets’s in-built alternating colors feature is a great option to color alternate rows within a few clicks. However, coloring with ‘conditional formatting’ gives you more flexibility over row selection.

Hopefully, now you won’t have any problems with applying color to alternate rows in order to make your worksheets elegant and readable.

Leave a Comment