When you’ve got a large dataset in Google Sheets, it’s incredibly hard to read and navigate the spreadsheeet if every row and column looks the same. Breaking up your spreadsheet with colors and formatting is a simple way to give some visual structure to a large expanse of data. This will make it easier to read and find information, which is particularly important if others also need to use the spreadsheet. By default, spreadsheets are plain and simple, but luckily you can break up that huge sea of gray cells with an alternated colors list in Google Sheets. This function allows you to alternate row colors in a couple of clicks, throughout part or all of the spreadsheet.
Creating an alternated colors list in Google Sheets
Take a look at the spreadsheet below. A customer service team use this file to track of issues reported by clients. The file is data-heavy and it contains a huge number of records:
Your eyes will struggle as you attempt to read through this volume of information on a screen. It’s easy to confuse one row for another and you may have to do a double-take. Ultimately this is really time-consuming so it’s a productivity problem as well.
The alternating colors functionality colors every other row in a darker shade, and keeps the other remaining rows in a contrasting, lighter, color. This is easier on the eyes and it makes it easier to read the data.
I’ll show you this function applied to the previous spreadsheet.
Go to Format > Alternating colors. A sidebar opens on the right-hand side of the screen:
You can apply alternating colors to a specific range, choose from predefined color palettes, or customize one to suit your preferences, such as colors that match your company branding. While you test out the options, Google gives you a preview of how it will appear in the spreadsheet.
If your spreadsheet does not have a header, deselect the checkbox.
When you’ve found the color combination you want, click the Done button.
Here I’ve gone for a simple blue option:
Tips
- If you already have some color-filled cells in the data range, that will be overwritten by the alternating colors formatting. You can, however, add different colors to specific cells afterwards. Just click on a cell and use the Fill Color option on the menu bar.
- If you no longer want alternating colors in your Google Sheets file, it’s easy to remove the formatting. Click on any cell within the range where the alternating color scheme is applied. Go to Format > Alternating colors again and click Remove alternating colors.
Customizing: How to color every third row
If you want to apply alternating colors to every third row (or fourth etc) you need to do this with conditional formatting. Select the range of cells from your dataset, then go to Format > Conditional Formatting.
Under Format rules > Format cells if, select Custom formula is from the menu.
Enter the formula =MOD(ROW(),3)-1=0
Under Formatting style select the color you want to apply to every third row and click Done.
To change this to every 4th, 5th, 6th row etc, just change the number 3 in the formula =MOD(ROW(),3)-1=0
Color formatting in Google Sheets
Congratulations, you’ve just mastered how to alternate row colors in Google Sheets.
Colors are really helpful when working in spreadsheets, giving you a fast way to highlight and categorize data. You can set up a system so that the color of the cell changes based on the value entered into that cell. Learn more about conditional formatting and How to change cell colors based on the cell value in Google Sheets.
