How to Colour Alternate Rows in Google Sheets

How to Colour Alternate Rows in Google Sheets
Reading Time: 5 minutes

The availability of Big Data has made it necessary for professionals in data analytics to ensure that the data is easy to read. To do so, one can either use charts to simplify the data or use grouping to hide irrelevant information. But when all the data is relevant, and the task doesn’t require a chart, one can colour alternate rows in Google Sheets to make the data look fancier and easier to read.

In this article, you will learn the different methods that one can use to colour alternate rows in Google Sheets and make their data easier to read:

Example of a row with rows coloured alternately in Google Sheets
Colour alternate rows in Google Sheets

You can make a copy of this spreadsheet and follow along with the tutorial. The data sample as well as the formulas are mentioned.

Different ways to colour alternate rows in Google Sheets

Suppose you have the following dataset, and you want to highlight every other row in the spreadsheet that you are preparing:

Sample Dataset
The Dataset

Below are the different methods you can use to colour the alternate rows in Google Sheets:

Method 1: Using the Alternating Colours pane

Step 1: Select the data (including the headings)

Step 2: To access the Alternating Colours pane you can either:

  1. Navigate to the Format Tab and select Alternating colours. Format→Alternating Colours.
Navigating to the alternating colours option using the format tab
Format tab→Alternating Colours
  1. Or, you can click on the Fill Colour icon and select Alternating Colours. Fill Colour→Alternating Colours.
Navigating to the alternating colours option using the fill icon
Fill colour icon → Alternating colours

Step 3: The alternating colours pane will open up after step 2. Once the window opens, you can make the following changes/selections

  1. Under the ‘Styles’ option make sure the ‘Header’ option is selected. In case, your data doesn’t have any header, deselect it. 
  2. To colour alternate rows in Google Sheets, you can either choose any of the predefined colours or specify the colours you wish to have in the Header, Colour 1 and colour 2 option
Alternating colours pane
Alternating Colours pane

On successful completion of the above steps, you will be able to colour alternate rows in Google Sheets and present the desired output. One such result is shown below:

Using the Alternating colours pane to colour alternate rows in Google Sheets

Note: If you add new rows in between or at the end of the table, Google Sheets will intelligently consider those rows when colouring the alternate rows of your data

Method 2: Using the Conditional Formatting Option

Step 1: Select the data (Including the headers, if any).

Step 2. Navigate to the Format tab and select Conditional Formatting. Format→Conditional Formatting

Navigating to the Conditional Formatting window using the Format tab
Format tab→Conditional Formatting

Step 3: Once the conditional formatting tab opens, click the box below the Format rules option

Adding the rule for conditional formatting
Adding the rule for conditional formatting

Step 4: From the dialogue box that appears, scroll down to select “Custom Formula is”, as shown below:

Choosing "Custom Formula is" Option
Select “Custom Formula is”

Step 5: Apply the formula =ISEVEN(ROW()) and choose the colour you want for the even rows in your data through the fill () icon and click done. 

Note: The function Row() provides the row number and the function ISEVEN() checks whether the row number is even or not.

Colouring the even rows

Step 6: Repeat steps 1 & 2 and click on “Add another rule”. Repeat Step 4 and insert the formula =ISODD(Row()) this time, and choose another colour from the fill option.

Note: The function Row() provides the row number and the function ISODD() checks whether the row number is odd or not.

Colouring the odd rows
Colouring the odd rows

Step 7: Click done to find the new table below:

Result after colouring alternate rows using conditional formatting
Colour alternate rows in Google Sheets using conditional formatting

Note: In case you add new rows to the data in the middle or at the end, you will have to reapply the conditional formatting as Google Sheets will not consider the new rows while formatting the data.

In case, you want the headings to be in a different colour, you can do so manually or exclude the headings when selecting the cell range while applying conditional formatting in steps 2 and 6.

Method 3: Using the MOD() function

The only difference between methods 2 and 3 is that in method 3 we use the Mod() function in place of ISEVEN() and ISODD() that we used in method 2. Thus, one can repeat steps 1 to 4 of method 2 and modify steps 5 and 6 as shown below:

Step 5: Replace the =ISEVEN(Row()) function with =Mod(Row(),2)=0. 

Step 6: Replace the =ISODD(Row()) function with =Mod(Row(),2)=1.

Applying the MOD() function

Explanation: The Mod() function takes two arguments, the dividend and the divisor. In our case, the Row() function represents the dividend and 2 represents the divisor. The Mod() function divides the arguments and produces the remainder.

Using the =0 and =1 with the Mod() function completes the check for whether the row in consideration is odd or even and colours accordingly.

Colour alternate rows in Google Sheets using the Mod() function

Hence, Mod() function can be an longer version of the ISEVEN() and ISODD() function. However, the Mod() function is superior to the other two functions when checks other than for odd and even are being conducted.

For instance, if a person wants to highlight every third row the person can use the function =Mod(Row(),3)=0 when adding the rule for conditional formatting.

Conclusion

Although we found that there are three different ways one can use to colour alternate rows in Google Sheets when cleaning their data, the first method still is not only fast but also dynamic as it automatically colours any new rows added in the middle or at the end of the data. 

We also came to know that the Mod() function can be an effective tool to use when colouring every nth row of your data. However, that’s a story for some other day! Keep yourself up-to-date with our blog and become a Google Sheets expert in no time.

See Also

Refer to the articles below to learn more about the things you can do using Google Sheets:

Conditional Formatting based on another cell in Google Sheets

How to use the IF function in Google Sheets

Conditional Formatting in Google Sheets

How to highlight duplicate cells in Google Sheets