The Google Sheets filter is a powerful tool that can help you manipulate how your spreadsheet data displays nondestructively.
For example, you can use this feature to delete (or hide) every other row in Google Sheets.
By using the correct filter, you can use Google Sheets to delete every other row or take it a step further and delete every third, fourth, or Nth row in a dataset.
This is the data with which we’ll be working in this tutorial: our goal is to delete every other row. It’s a list of user IDs, first names, last names, and email addresses.
The following processes explain how you can use filters to delete rows on intervals in Google Sheets.
This Article Covers:
Deleting Every Other Row in Google Sheets
The trick to deleting every other row in Google Sheets is to use a filter to only display even or odd-numbered rows, then copying/pasting the visible rows into a new worksheet.
You’ll be using one of two formulas that don’t require any sort of modification.
If you want to hide all odd-numbered rows, use this formula:
=ISODD(row(A1)
If you want to hide all even-numbered rows, use this formula:
=ISEVEN(row(A1)
The following steps demonstrate how to hide then delete every other row in Google Sheets. In the example, we’ll be hiding every even-numbered row:
- Turn on the filter in the header row (you need a header row for this to work). Highlight the header row by clicking on the row number identifier, then click the “filter” icon found in the header menu.
- Click the filter icon for the first column and the click on “Filter by condition”
- Click the box under ‘Filter by condition’
- Choose ‘Custom formula is’ from the ‘Filter by condition’ select menu.
- Use the formula =ISEVEN(row(A1) to hide all the even rows (If you want to hide every odd-numbered row, just use the formula =ISODD(row(A1) instead). Then click “OK.”
- Google Sheets is now hiding every even-numbered row (notice that it is the row number and not related to the value in the “id” column).
- Select all the fields on the worksheet (Ctrl+A for PC, CMD+A on Mac), “Copy” the values (Ctrl+C on PC, CMA+C on Mac), then hit the “+” icon to create a new worksheet/page.
- Click on cell A1 on the new worksheet/page, then paste the visible contents from the original worksheet/page into the new worksheet/page (Ctrl+V for PC, CMD+V for Mac). You have now deleted every-other row in the Worksheet.
You can then manually format the cell widths to make your new worksheet easier to read.
Note: The filter will not remove the header row when you use the ISODD function.
Explanation of the Formulas
We’ll be using two functions to achieve this goal – the ISODD function and the ISEVEN function.
We’ll also be using the ROW command.
If you want to hide all odd-numbered rows, use this formula:
=ISODD(row({any dynamic cell value})
If you want to hide all even-numbered rows, use this formula:
=ISEVEN(row({any dynamic cell value})
The value for {any dynamic cell value} is the dynamic name for any cell on the worksheet. This is the cell value without the “$” designating a static value. Example dynamic cell values include “A1,” B5,” and “D27.”
For ease of use, we’ll be designated the dynamic cell value as “A1” in this tutorial. Filled out, the formulas look like this:
=ISODD(row(A1))
=ISEVEN(row(A1))
It does not matter which cell value you use for these formulas because the row command combined with a dynamic cell name forces the filter to check each row against itself.
Deleting Every Nth Row in Google Sheets
What if you want to delete every third, fourth, or even tenth row in Google Sheets?
The process for deleting every Nth row in Google Sheets is a bit more complicated than deleting every other row.
The trick to deleting every Nth row in Google Sheets is to use the MOD formula in a filter. For the example, we’ll be using the following formula to delete every fourth row, starting with the fourth row after the header:
=MOD((row(A1) - row ($A$1)-3),4)
The part “=MOD((row(A1) – row ($A$1)” stays the same in all use-cases for this formula. What you’ll need to change to set the “Nth” value is the “-3),4)” part.
The part you’ll be concerned with modifying uses the value of “Nth minus 1” in the first spot, and “Nth” in the second like this:
=MOD((row(A1) - row ($A$1)-{Nth -1}),{Nth})
So if you’re looking to delete every third row, use this: =MOD((row(A1) – row ($A$1)-2),3).
The formula for every tenth row is this:
=MOD((row(A1) - row ($A$1)-9),10).
We’ll be using the same dataset from before to show how this works:
The only difference in the process is we use the new formula, “=MOD((row(A1) – row ($A$1)-3),4)” instead of the ISODD/ISEVEN formula in the filter:
As you can see in the example, the rows for IDs 4, 8, and so on are hidden:
You will still need to copy/paste the resulting worksheet content into a new worksheet/page to delete the hidden rows.
Explanation of the Formula
Deleting every Nth row in Google Sheets uses a combination of MOD and ROW functions. With all the information we need, the formula will look like this:
=MOD((row({dynamic cell name}) - row ({static value of the dynamic cell})-{offset}),{nth factor})
Let’s look at each part of this formula:
- MOD(dividend, divisor): This is the modulus operator, we’re using it in a way that if the division operation returns a remainder of “0” we hide or delete the cell.
- ROW(cell_reference): This returns the value of a cell’s row. This is how we’re specifying we’re looking at rows, not individual cells.
- Dynamic Cell Name: This is a specific cell declaration that changes its value across columns and rows. If you reference “A1” in the first row and apply the formula to the second row, the formula will now address cell A2.
- Static Cell Name: This declaration forces the formula to always reference the same cell even when applied across multiple rows. You set static row and column assignments with a “$” before the representative character. So $A$1 in a formula will always reference cell A1.
- Offset: This value determines which row starts the hiding/delete interval. Calculate the offset as your Nth factor minus 1 to start on the Nth cell after the header ( if N is 7, the offset is 6).
- -0 starts hiding with the first row AFTER the header
- -1 starts hiding the second row AFTER the header
- -2 starts hiding the third row AFTER the header
- -3 starts hiding the fourth row AFTER the header
- This pattern continues until it shares the same value as N, then repeats. You can’t use this formula to start hiding rows after a specific point.
- Nth Factor: This is the numeric value that sets the row hide interval. The Nth value for deleting every third row is 3, every fourth row is 4, and so on.
If we put it all together to hide every third row starting with the third row excluding the header, we use:
=MOD((row(A1) - row ($A$1)-2),3)
Alternatively, if we put it all together to hide every fifth row starting with the fifth row excluding the header, we use:
=MOD((row(A1) - row ($A$1)-4),5)
If we want to hide every fifth row, but we want it to start with hiding the first row after the header, we change the offset to “0” and use:
=MOD((row(A1) - row ($A$1)-0),5)
Note that it does not matter which cells you use for the formula, just that both cells reference the same row. For example, the following filter formulas will return the same results for hiding every fourth row:
=MOD((row(A1) - row ($A$1)-3),4) =MOD((row(C1) - row ($C$1)-3),4) =MOD((row(A1) - row ($C$1)-3),4) =MOD((row(C1) - row ($A$1)-3),4) =MOD((row(A4) - row ($A$4)-3),4) =MOD((row(A4) - row ($C$4)-3),4)
All of these formulas produce the same result:
This tutorial covers how to delete rows over an interval in Google Sheets with filters. With a little modification, you can come up with all sorts of useful ways to display your spreadsheet data.
I hope you found this tutorial useful!
Other Google Sheets tutorial you may like:
