2 Ways to Color Alternate Rows in Google Sheets
- Using the built-in function of Google Sheets
- Using a formula in the conditional formatting
Method 1: Color Alternate Rows Using Google Sheets’ Built-in Function
Step 1: Open up your Google Sheets file.
Step 2. Click on the table you want alternate rows shaded.
Click on or highlight the table where you want the coloring of alternate rows done.
If you have multiple tables in one sheet, it is best to highlight the table you are formatting to avoid confusion.
You may choose to not include the table heading if you have already formatting styles applied.
Tip: Selecting the whole table can also be done by holding down the CTRL + A keys. This automatically selects the table on focus.
Step 3. Open the Alternating Colors menu.
After selecting those cells, click on the Format tab near the top-left corner of your screen. From there, move your mouse all the way down and select Alternating colors.
This will open a window where you can configure the color and format of the alternate rows.
The Alternating colors menu should appear on the right side of your screen.
On the menu, you can further customize the alternating colors of your table. You can change the range of the table in the field Apply to range. Under Styles, select Header or Footer if you want a darker shade on your header and footer, respectively.
You may select both or neither. Google Sheets applies your changes as you select the style you want.
As you may have noticed, you can customize the colors and create your own style if you don’t want to use the presets that are already there.
Once you are happy with your selection, click Done. Otherwise, click Remove alternating colors.
Method 2: Color Alternate Rows Using Formulas
Step 1: Open up your Google Sheets file.
Step 2. Select the table you want alternate rows shaded.
Highlight the whole table where you want the shading applied. You can also select the whole table by pressing on the CTRL + A keys. This way, you let Google Sheets automatically select your table.
Step 3. Open the Conditional Formatting menu.
Once you’ve selected the necessary cells, go ahead and click on the Format tab near the top-left corner of your window. With the drop-down menu open, look for and select Conditional Formatting.
You should see the Conditional Formatting menu on the right.
Step 4. Insert the formula as a format rule.
Under Format rules, click on the drop-down list and select Custom formula is. You’ll find this option at the bottom of the drop-down list. Insert the formula “=MOD(ROW(), 2)” in the Value or formula textbox.
Where:
= MOD() – returns the remainder after the division operation
= ROW() – returns the row number
2 – is the divisor, diving the row number returned by the = ROW() function
Google Sheets should preview the table with shaded rows accordingly.
You can change the style by using the options under Formatting style. Here, you can change the color of the cell background and the format of your texts.
Click Done when finished.
Specifying Number Of Rows To Be Shaded
Since these are formulas, we can actually change the parameters to our liking. Try the following examples:
Example 1. “=MOD(row(), 5)” will shade every after four rows starting from the first row.
Example 2. “=MOD(ROW()-2,4) >= 2” shades every 2 rows starting from the first row.
The MOD() and ROW() functions
Briefly, the MOD function returns the remainder of a division. For example, =MOD(6,2) returns 0 because 6 divided by 2 is exactly 3.
This results in the remainder being 0. Likewise, =MOD(5,2) returns 1, since 5 divided 2 is 4, with the remainder being 1.
On the other hand, the ROW function returns the row number of a cell.
Together, the formula simply tells Google Sheets to shade the ones with the remainder.
Alternative to the MOD() Function
Alternatively, you may use either the =ISEVEN(ROW()) or =ISODD(ROW()) formulas. As they’re names imply, =ISEVEN(ROW()) checks whether the row is even while =ISODD(ROW()) checks if the row is odd. Google Sheets then colors the row accordingly.
You will notice that these functions cannot be customized any further unlike the MOD() function.
Editing and Deleting Conditional Formatting Formulas
If you want to edit the formulas in the Conditional Formatting menu, open up the Conditional Format rules window. The steps are the same as when we opened it in the previous method.
Note, however, that the Conditional format rules will only be visible if there’s a formula that has been previously placed.
Now, select the formula you want to edit. You will be directed to the Conditional Format rule window we had in the previous method.
Otherwise, click the Trash Bin button across the formula if you want to delete.
Conclusion
We hope you found this article helpful!