How to add alternating row colours to Google Sheets

When working with tables in a spreadsheet, we often want them to be easy to read and understand. To help us, Sheets has a quick little tool, which allows us to add alternating colours to the rows of our data, making them not just prettier but much easier to read, whilst being really quick to create.

Here, we have a plain table of data. First, highlight the area you want to format, usually the whole table, or maybe include rows below it if you expect to add more data.

Alternating colors

Go to the Format menu, then click “Alternating colours”.

This will open the sidebar. Here, from top to bottom you have:

  • The range the formatting will apply to.
  • Whether you want a different colour for the header of the data or the footer.
  • A set of default colours to choose from.
  • The option to create your own default colour combination, which is then saved for future use.
  • At the bottom, you have the option to remove the formatting.

When you first open the sidebar, by default it adds a grey and white format to your data.

To change this, just select one of the default options.

Here, I’ve selected a purple combination. As you can see, it’s easier to read and looks better too.

Editing the colours and adding a footer

If you want to choose, your own colours, either select a default one and edit it, or click the plus button to add a new one. Then click on the fill symbols to the right, to change the colours.

By default, this adds a darker header row at the top. You can choose whether you want to have this and also whether you want the last row, the footer, to be a different colour, for example, usual for totals. Just tick the ones you want.

Here, I’ve added both a header and a footer.

Notice, the Default styles now include the footer.

Once you’ve selected the options, click Done to confirm the changes.

Create your own custom styles

To create custom colours for your rows, click on the circle next to the type of row you want to change. Here, I’m changing the header to be orange.

Plus, I’ll change colour 2 to a light orange.

Removing alternating colours

To remove the alternating colours, click “Remove alternating colours” at the bottom of the sidebar.

One nice thing about this is that if you delete or insert a row, the colours automatically change, so they are always alternating, so you’re not left with two of the same colours next to each other.

This post is taken from my book “Beginner’s Guide to Google Sheets“, available on Amazon here.

Updated 2023. More Google Sheets tips on YouTube, check out my How to videos.

Want to learn more about Google Workspace and Apps Script? The books below are available on Amazon. Just click on a book! (Affiliate links).

JavaScript Fundamentals for Apps Script users

a

Google Apps Script Projects 1
Google Apps Script Projects 2