In this tutorial, you will learn how to select every other row in Google Sheets.
How To Select Every Other Row in Google Sheets
Quick Navigation
There are many reasons why you might need to select alternating rows in your dataset. For example, you may want to highlight every other entry in a dataset with a different color.
Perhaps you may want to copy only every other value in a dataset. Whatever reason that may be, you might be wondering if there is a simple and intuitive way to do it.
Google Sheets currently has no tool that automatically selects alternating rows. However, we can use conditional formatting and filtering to show only every other row in Google Sheets.
This step-by-step guide will detail everything you need to know to select every other row in Google Sheets.
How To Select Alternating Rows in Google Sheets
Here’s how to select alternating rows in Google Sheets.
Step 1
First, we’ll explain how to select every other row manually. To start, click on the row header of the first row in your spreadsheet that you want to select.
Step 2
Next, hold the Shift key and click on the row header two rows below your first selection. Continue this process until you have selected every other row in your dataset.
Since this manual process may take some time for larger datasets, we recommend using the manual selection for smaller datasets.
Step 3
If you have a larger dataset, you will likely find it tedious to select every other row manually.
We can select every other quicker through the use of conditional formatting.
First, select the range of cells where you will make your alternating selection.
Step 4
Next, click on the Conditional formatting option found under the Format menu.
Step 5
In the Conditional format rules panel on the right-hand side of your window, expand the drop-down list under the label ‘Format cells if…’
Step 6
Select the ‘Custom formula is’ option from the drop-down list. We will use this option to add a custom formula that will only be true if the row number is either even or odd.
Step 7
Next, we’ll type in our custom formula in the textbox that appears below the drop-down list.
Our custom formula =MOD(ROW(A2),2)=0 works by first using the ROW function to return the row number of each row in the selection. We use the MOD function to determine whether the row number is divisible by 2. If the row number is divisible by two, that row will be formatted as the user specifies.
Click on Done to apply the conditional formatting.
Step 8
Your range should now have every other row formatted a certain way. In this example, each even row in our selection has been colored green.
Step 9
Next, we’ll use add a filter to our range to take advantage of our conditional formatting. You can find the option to create a filter in the Data menu.
Step 10
Click on the filter icon on any of the range’s headers.
Step 11
Select the option Filter by color > Fill Color > (Your Chosen Color).
Step 12
Google Sheets now only shows every other row in the target range.
Step 13
From this point, you can perform any action you wish on the selected rows. For example, you can set every other row to be italicized.
Step 14
When we remove the filter we’ve added, we are left with our original data again.
Notice how our action earlier to italicize was only applied to every other row in Google Sheets. You can use this same technique to perform other actions such as copying data or batch editing every other row.
Summary
This guide should be everything you need to select every other row in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.