As a digital marketer, I struggle sometimes to find pieces of data within my MASSIVE Google Sheets spreadsheets. Rows begin to blend together once you have tens or hundreds of columns with data. Luckily you can help your eyes by implementing alternating rows to your spreadsheets.
The easiest way to start alternating rows in your Google sheet is through the menu and selecting “Alternating Colors”. Alternatively, you can use the “Conditional Formatting” option to alternate more than 2 colors of rows but it requires (a little) more work.
If you were only looking for where the options are found then you can stop right here. The rest of the article will list the steps and advantages/disadvantages of each method. Below will also include a link to an example using both methods!
Alternating Colored Rows through the Menu
Google Sheets has a built-in function that can be easily accessed from the main menu.
Go to [Format] => [Alternating colors…] to access the built-in function.
The function lets users quickly color rows with several presets and the ability to choose custom layouts. A header and footer row can be designated and assigned their own colors as well.
Apply to Range
This lets you determine what rows and columns will receive alternating colors. Generally, you want to apply alternating colors to your whole sheet. However, it can be only applied to certain parts of the spreadsheet. In fact, you can have multiple alternating color rules.
Styles
Adding a header and footer, choosing from default styles, creating custom styles, and color selection are able to be changed from this section.
Header and Footer
Most of the time you will designate your top row for identifying the types of data in the rows below. Google added this feature to also give you the option of making it a separate color from the other rows.
Likewise, but not as often, you may have your last row of the spreadsheet set for different information. You can also check the “Footer” box to enable giving the last row its own color
Default Styles
There are 12 default styles to choose from and should get the job done if most cases. They all follow the same general pattern:
- Header: Dark Color
- Even Row: White
- Odd Row: Light shade of header color
- Footer: Medium shade of header color
Custom Styles
Here you can save your custom color combinations to the spreadsheet file with the “+” button. It will come in handy if you want to repeat an alternating color pattern onto other spreadsheets within the file.
Extra Tip: Sometimes I don’t feel like looking at the tab below to know which type of spreadsheet I’m on. I give my spreadsheets specific alternating colors to quickly let me know which sheet I’m working on.
Color Selection
Here you choose the colors of your header, footer, and rows. It uses the same color picker for changing other items in Google Sheets such as font color, cell color. That also means it uses any of the existing custom colors that may have been used on other parts of the sheet. It’s great for keeping a colorized theme throughout the spreadsheet.
Alternating Colors Through Conditional Formatting
Although alternating row colors is quick and easy through the built-in menu function, it is only limited to odd and even rows. Unfortunately, there is no way to have three, four, or more alternating row colors through that method. Luckily there is a solution for those that need more than two alternating colors. That solution is Conditional Formatting.
Start by selecting cells you wish to then in the menu find:
“Format” then “Conditional Formatting”
Next, select the dropdown menu under “Format cells if…”. Here we will select the “Custom Formula” option.
We’ll make use of the MOD(), or modulus, and ROW() functions to select our rows.
The following example will use four alternating rows.
Custom Formulas for the example rows:
Every 1st Row: =mod(row(), 4)=1
Every 2nd Row: =mod(row(), 4)=2
Every 3 Row: =mod(row(), 4)=3
Every 4 Row: =mod(row(), 4)=0
Generic Custom Formula
The generic custom formula for any row (except for the last of the set) for the number of alternating color rows will be:
=mod(row(), [Total Number of Alternating Rows])=[Row Number]
Last row of the set:
=mod(row(), [Total Number of Alternating Rows])=0
Bonus: Columns can be given alternating colors with this same method. Simply replace ROW() with COL() in your formulas!
Now you’re able to alternate the colors of as many rows as you want. Using conditional formatting has its own drawbacks. It takes more time to set up, especially with much more than 2 rows.
Google Sheets Example
Are you still stuck or prefer to learn by doing it yourself? We’ve prepared an example Google Sheet with alternating rows.
Wrapping It Up
Now you can alternate the rows of your colored spreadsheet to your liking. Need something basic with 2 rows and a header/footer? The “Alternating Rows” option has you covered. Do you need more than 2 row colors or alternating columns? You can apply that with “Conditional Formatting”. Let us know if we’ve missed anything or have any questions. Happy Sheets!