How to Delete Every Other Row in Google Sheets (Easy Formula Trick)

By

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.

Dataset to delete every other row

The following processes explain how you can use filters to delete rows on intervals in Google Sheets.

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:

  1. 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.Apply filter from toolbar
  2. Click the filter icon for the first column and the click on “Filter by condition”Click on Filrer by condition
  3. Click the box under ‘Filter by condition’Click on the filter by condition drop down
  4.  Choose ‘Custom formula is’ from the ‘Filter by condition’ select menu.Click on Custom formula is
  5. 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.”Enter the ISEVEN formula
  6. 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).Filtered data with only even rows
  7. 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.Copy the cells
  8. 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.Paste the copied data

You can then manually format the cell widths to make your new worksheet easier to read.

Data with alternate rows deleted

 

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:

Dataset to delete every other row

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:

MOD formula in filter

As you can see in the example, the rows for IDs 4, 8, and so on are hidden:

Every fouth row is 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)

Every third row is hidden

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)

Every fifth row is hidden

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)

Every fifth row is hidden starting from the first one

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:

Popular Posts