How to Alternate Row/Column Shading in Google Sheets

How to Alternate Row/Column Shading in Google Sheets

This tip is about the how to Alternate Row/Column Shading in Google Sheets. So read this free guide, How to Alternate Row/Column Shading in Google Sheets step by step. If you have query related to same article you may contact us.

How to Alternate Row/Column Shading in Google Sheets – Guide

Google Sheets is a spreadsheet program included as part of the free web-based Google Docs Editor package offered by Google. The service also includes Google Docs, Google Slides, Google Drawings, Google Forms, Google Sites and Google Keep. In Google Sheets, there is no simple method for coloring alternate lines; however, you can use conditional formatting to do this. Data is easier to read when alternate lines are colored. Zebra lines are another name for them.

Alternate color lines in Google Sheets

Here are the steps to color alternate lines in Google Sheets:

  • Select the cells in which you want to color alternate lines. In this example, I want to highlight cells A2:C10.
  • Go to Format -> Conditional Formatting.
  • In the Conditional Format Rules panel, click the ‘Format cells if’ drop-down menu and select ‘Custom formula is’.
  • In the field below it, type the formula =ISEVEN(ROW())
  • Select the formatting style. You can select the default option or access more fill color options from the toolbar below it.
  • Click Done.
  • That’s it. It would fill the even lines (in the selected range) with the specified color.

    How it works?

    All the magic in the formula =ISEVEN(ROW())

    It returns true for all cells where the row number is even. Conditional formatting checks each cell for this formula and any cell that scores TRUE is filled with the specified color.

    Paint every third in Google Sheets

    Let’s use the above logic and extend it to highlight every third row in a dataset.

    Here are the steps to do this:

  • Select the cells in which you want to color alternate lines.
  • Go to Format -> Conditional Formatting.
  • In the Conditional Format Rules panel, click the ‘Format cells if’ drop-down menu and select ‘Custom formula is’.
  • In the field below it, enter the formula =MOD(ROW(),3)=0
  • Select the formatting style. You can select the default option or access more fill color options from the toolbar below it.
  • Click Done.
  • This would fill every third row (in the selected range) with the specified color.

    Everything remains the same except the formula. Previously, when highlighting alternate lines, we only had to check every second line, so the ISEVEN function worked fine. In this case, we have to check every third row (which can be odd or even). That’s why we use the MOD function.

    The =MOD(ROW(),3) function returns the remainder when divided by 3. For rows that are multiples of 3, it would return 0. And this is the condition we check in the conditional formatting custom formula.

    For all cells that return 0 as the rest, the formula =MOD(ROW(),3)=0 returns TRUE and that cell is highlighted with the specified color.

    You can use the same logic, to highlight every 4th, 5th, 6th, row and so on.

    Final note

    I hope you like the guide How to Alternate Row/Column Shading in Google Sheets. In case if you have any query regards this article you may ask us. Also, please share your love by sharing this article with your friends.

    We will be happy to hear your thoughts

    Leave a reply

    How to Guides, Best Lists, Top 10 and Tech News - Compsmag
    Logo