In this post, you’ll learn how to alternate colors in Google Sheets and add row or column banding to your data tables.
Here’s an example of alternating row bands applied to a checklist in Google Sheets:
How To Alternate Colors In Google Sheets Rows
It’s super easy to add alternating row colors in Google Sheets.
Step 1:
Highlight your data table.
Step 2:
Go to Format > Alternating colors
Step 3:
Select one of the default styles and click Done:
Custom Colors And Styles
There are two checkboxes in the alternating colors sidebar, which allow you to toggle headers and footers on or off. If you check them, you can set a different color for the headers and footers.
You can also set custom colors under the default styles and even save your color scheme as a custom style with the big plus button.
Dynamic Alternating Rows
How do you apply alternate colors in Google Sheets if your table is dynamic and changes size?
Unfortunately, you can’t use the alternating rows tool because it gets applied to a static range. The alternating color bands are left in place whether there is data or not.
But the good news is that you can use regular conditional formatting to achieve the dynamic alternating colors.
Highlight your data range, open the conditional formatting sidebar, and set the custom formula rule to:
=AND(NOT(ISBLANK($B16)),ISODD(ROW()))Notice the $ sign in front of the A, so that the conditional formatting is applied to the whole row.
Also, this rule is operating on a range that starts from cell B16 (see the GIF above). You’ll want to change this to match the top-left cell of your range.
This formula works by checking that the row is an odd-numbered row AND not blank in column B, i.e. the row has data.
If the row is blank in column B, then the conditional formatting is not applied.
This technique could be adapted to columns by switching the ROW function to a COLUMN function.
How To Alternate Colors In Google Sheets Columns
Unfortunately, there is no equivalent alternating colors option that works on columns. However, since it’s much less common to do this, perhaps that’s no great loss.
But, in case you want to color columns with alternating colors, here’s how:
Step 1:
Highlight your data table.
Step 2:
Go to Format > Conditional formatting
Step 3:
Set the format rule to “Custom formula is”
Step 4:
Enter this custom formula, using the ISODD function and the COLUMN function, if you want the color to start in column A:
=ISODD(COLUMN())Alternatively, use this formula with ISEVEN to start the colors from column B:
=ISEVEN(COLUMN())Step 5:
Set the formatting style and click Done.
See Also
How To Make a Table in Google Sheets, and Make It Look Great
Is it possible to alternate colors after every certain number of rows, say 3?
Yes, you would have to use something like IF(MOD(ROW(),3)>0,A,B)
Thank you very much for this tutorial. This is one of the most helpful ones out there.
Could i get a copy of the sheet for more clarity
Is it possible to add the alternating colors format from a script?
Yes, it is: https://developers.google.com/apps-script/reference/spreadsheet/banding
I want to alternate colors by a change of value in one cell. I work with lists of names, and when they are alphabetized by last name, I want all Aaronsons in color A, Adams in color B, Anderson in color A, and so on, so I can easily identify whose data is on which line. Possible, or not?
Hello – do you have an explanation for achieving the table in the GIF for Dynamic Alternating Rows? I would like to achieve the exact same thing.
Thank you!
Hi Derek,
Feel free to make a copy of this Sheet that contains the that example: https://docs.google.com/spreadsheets/d/1tni6uTnZiAGSZd2nR56z-tJtRT5oVVsn-gCbX2wSaC0/edit?usp=sharing
It’s basically a FILTER to capture the priority choice and then the QUERY function to retrieve the data.
Hope this helps!
Ben
Hello,
is it possible to apply both alternate colors, to columns AND to rows?
I tried following the same procedure, adding another “Conditional formatting” with “=ISODD(ROW())”, but only the odd columns seem to display the changes.
Here’s the scheme I’d like to achieve:
COLOR1 – COLOR2 – COLOR1 – COLOR2 …
COLOR3 – COLOR4 – COLOR3 – COLOR4 …
COLOR1 – COLOR2 – COLOR1 – COLOR2 …
COLOR3 – COLOR4 – COLOR3 – COLOR4 …
COLOR1 – COLOR2 – COLOR1 – COLOR2 …
COLOR3 – COLOR4 – COLOR3 – COLOR4 …
…
Ehm, I see now that in the comment the text is all in the same paragraph, so the scheme is not so clear… I try to explain: COLOR1 and COLOR3 are in the same column (first, third, fifth…) and so are COLOR2 and COLOR4 (second, fourth, sixth…), and so on.
Very helpful from the first few slides, I was able to solve the issue from my Google Search: alternate color lines on google sheets
It’s great when small business owners have easy to find and thoughtful content beyond the arena of sales jargon – inflated phrases and solutions that sound impressive, yet lack full relative content.
As a coach of sort to small business owners, it’s small tips like this that can make a bad day great again. As I use to say when I started my business, “Sometimes, all you need is a little help.”
Thanks again for sharing!
REITERATING THE ABOVE QUESTION:
is it possible to apply both alternate colors, to columns AND to rows?
Here’s the scheme I’d like to achieve:
https://imgur.com/a/WETOeDo
hi
in my google sheets there is no ‘format’ tab – how is that even possible?
hence i can’t find ‘alternating colours’ anywhere
pls help
Hi Rob,
It’s a menu item called “Format”, see the picture under Step 2.
Hope this helps!
Ben