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 How to Colour Alternate Rows in Google Sheets that one can use and make their data easier to read:

• Method 1: Using alternating colours pane
• Method 2: Using conditional formatting
• Method 3: Using the MOD function

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:

Below you will find the different methods 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.

2. Or, you can click on the Fill Colour icon and select Alternating Colours. Fill Colour→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
   

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:

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

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

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

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.

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.

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

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.

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.

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 n^th 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