A quick guide:
How to filter every other row in Google Sheets:
Step 1: Select the header -> filter function
Step 2: Choose the ‘Filter by condition’ option and then scroll to select the ‘Custom formula is’ option
Step 3: Type in the following formula and click OK:=ISODD(row(A3))
Step 4: Rows with odd index get removed from the spreadsheet
Sample Google Sheets template with formula here.
Google Sheets offer a variety of tools that you can use to your advantage. Do you know how to filter every other row in Google Sheets? It might seem tricky, but the procedure is simple and easy to implement.
There are instances where one has to select only odd or even indexes. In those cases knowing how to filter every other row in Google Sheets might come in handy. This way we won’t need to delete the rows manually and the job can be accomplished quickly.
In this tutorial, we will learn the step-by-step process of how to filter every other row in Google Sheets. Further, we will also try to generalize this approach and learn how to filter every nth row in Google Sheets.
Using the filter tool to filter every other row in Google Sheets
In this tutorial, we will use the filter tool to filter every other row in Google Sheets. In the following example, we have a spreadsheet containing data about students. Our objective is to filter the data of students with odd-row indexes.
The step-by-step procedure is as follows:
- We are going to use the filter tools. That’s why we need to select the headers. In this case, it will be A3:C3
- Click on the filter tool
- The Filter icon appears on every header as shown
- Click the filter icon present in the Roll no. header
- A drop-down menu will appear. Choose the ‘Filter by condition’ option and then further scroll through it to select the ‘Custom formula is’ option to insert the customized formula
- We will use the custom formula to hide all the odd indexed rows. We will use ‘ISODD’ function to find filter rows with odd indexes. Type in the following formula:
=ISODD(row(A3))
- Click on OK and only the even rows are visible. The odd rows are hidden.
- You can copy the whole sheet and paste it into a new sheet if required
We have successfully learned how to filter every other row in Google Sheets. In case of removing the rows with an even index, we would have used the ‘ISEVEN’ function in place of the ‘ISODD’ function.
We will now look forward to generalize this approach by filtering every nth row in Google Sheets.
Using the filter tool to filter every nth row in Google Sheets
We have already learned how to filter every other row in Google Sheets. We are going to use the MOD function in this case. Our objective is to filter every row whose index is a multiple of 4 in the same spreadsheet which we used in the previous section.
The step-by-step procedure is as follows:
- All the steps are the same except the custom formula part. Type in the following formula and click on OK:
=MOD((row(A3)-row($A$3)-3), 4)- In the above formula, ‘(row(A3)-row($A$3)-3)’ the first parameter generates different indexes under the selected headers. Here, ‘3’ stands for every 4th row i.e ‘n’ stands for ‘(n+1)th row. The second parameter stands for every (n+1)th row. In this case, it will be 4 as we have to filter every row which is a multiple of 4. The MOD function returns 0 for every multiple of 4 and those indexes get filtered.
- In this case, the output will be something like this:
We have successfully learned how to filter every nth row in Google Sheets.
Conclusion
We have successfully learned how to filter every other row in Google Sheets. Along with this, we also learned how to use a generalized approach to filter every nth row in a Google Sheets. Now you are all set to use these methods to your advantage.
See Also
Are you interested in learning more about how much you can succeed with Google Sheets? With so many powerful features of Google Sheets, you save time and effort.
We have several tutorials that cover tricks and tips in Google Sheets. You can discover them here.
Here are some articles you might be interested in:
https://blog.tryamigo.com/colour-alternate-rows-in-google-sheets/
https://blog.tryamigo.com/sort-query-using-order-by-in-google-sheets/
https://blog.tryamigo.com/introduction-to-date-function-in-google-sheets/