How To Select Alternating Rows in Google Sheets
What are your thoughts on zebras? I know, not a question that you expected to be asked with regards to the topic of the article. But stay with me for a second. A zebra’s stripes are very apparent. When you see a zebra you know exactly what it is. The stripes on a zebra’s body make it stand out and provide a solid distinction between the black markings and white markings.
“Where exactly are you going with this?”
The way in which people digest visual information can be made easier when colored lines are alternated. It provides a line by line distinction making words, numbers, and symbols that much easier to read. Just like how it’s easy to see a black stripe followed by a white stripe, followed by a black one. That’s all I’m saying.
Microsoft Excel and other programs in Microsoft Office provide a nifty feature, not unlike zebra striping, that enables you to alternate the colors of each row. It’s called Quick Styles and it can help you to quickly format a selected range as a striped table.
“So alternating rows of white and black?”
Exactly, however, the colors chosen are irrelevant. That choice is predicated on what feels easier on the eyes to you and your intended audience. Unfortunately, Google Sheets does not currently support Quick Styles, aka zebra stripes, just yet.
Google Sheets is a great program for creating online spreadsheets and generally comes with quite a few quality of life features. Quick Styles just isn’t one of them. To produce the same results with alternating colored rows like those other programs, you’ll need to work a bit of conditional formatting magic.
Alternate Row Colors in Google Sheets
You can apply alternating colors to both rows and columns in Google Sheets easily through the use of conditional formatting. Conditional Formatting allows you to change the background colors and fonts of a cell or group of cells using specific rules and criteria.
To create alternating colored rows in Google Sheets:
- Launch a web browser (Google Chrome preferred for obvious reasons).
- Head over to the Google Sheets website.
- You can also choose to pull up Google Sheets through your Google Drive.
- Log in to your Google account if you’re not already currently logged in.
- Now, depending on if you’ve reached this point through Google Sheets directly or through Google Drive, you’ll need to open or create a new spreadsheet. For one already created, just double-click it to open it.
- New spreadsheet using the Google Sheets website:
- Move the cursor over the ‘+’ icon in the lower-right corner and click on the Create new spreadsheet icon when it appears.
- New spreadsheet using Google Drive:
- Click on the + NEW button on the left side menu, then select Google Sheets from the drop-down list.
- New spreadsheet using the Google Sheets website:
- With the spreadsheet open, new or otherwise, click on the “Format” tab from the menu at the top.
- Select Conditional formatting… from the available options in the drop-down menu.
- This will force a side panel titled “Conditional format rules” to pop-out from the right side of the screen.
- This is what you’ll use to specify the conditions for the formatting that will be applied to your currently opened spreadsheet.
- Enter your conditions into the input boxes based on what it is you’re attempting to pull off. Here are the details of each box:
- Apply to range – You can choose the cells that will receive the alternating colors that you plan to have applied. Just click the input box and select the cell or cell range in either the spreadsheet (highlight the cells for your purpose), or you can manually enter the values (A1-J1, etc.).
- Format cells if – Applying your range for the colored rows was the action, but now you need to enter the cause. This option lets you choose when the specified cells should be formatted with your chosen formatting settings. Click the box and from the drop-down menu select the “Custom formula is” option.
- Value or formula – This is where your ‘IF’ formula goes. Since you chose “Custom formula is” in the last step, you’ll need to enter in the formula yourself. To add color to every odd row, enter =ISEVEN(ROW()) into this field. For odd rows, just change EVEN to ODD.
- Formatting style – It’s here where you get to choose the colors and fonts of the alternating rows. Make changes where you feel they’re needed. If satisfied with the default, just leave it as is. The minty green color is easy on the eyes so you don’t have to get too crazy.
- When all fields have been filled out, click on Done at the bottom to save the settings.
The specified formatting should now be visible in the cells of your spreadsheet. You can now do the same thing for the odd rows (or whichever one you didn’t choose yet) if you’d rather not have a plain white row.
To do this:
- You’ll have to click on “Add another rule” in the same panel you have on the right side of your screen.
- Repeat the same process you did the first time only this go around change the EVEN to ODD (or vice versa).
- Obviously, you’ll want to also change the color to something other than what you applied the first time or this whole process would be pointless.
- Just to clarify, be sure to type in =ISODD(ROW()) this time when filling out the input box designated as Value or formula.
- Follow this up by clicking on Done just as you did the first time.
The results will be instant and you’ll now have alternating colored rows running throughout your spreadsheet.