I’ve seen many users using a helper column to mark and delete every nth row in Google Sheets. But the fact is that we can do that without using a helper column. By saying helper column, I mean a supporting column.

I will explain to you, without using a helper column how can we remove every nth row in Google Docs Sheets.

Actually, it’s quite easy to delete every nth row in Google Sheets using the Filters (Data > Create a filter) custom formula feature.

You May Like: Filter Unique Values Using the Filter Menu in Google Sheets.

Here is that quick tip to filter and remove every nth row in Google Sheets. Before that one question, that you may possibly want to ask me.

Why do you want to delete every nth row in Google Sheets?

I want to delete every 4th rows in one of my tables in Google Sheets since those rows contain some labels. Do you know why?

I don’t want those labels present, as it can (mixed content) cause issues in other Spreadsheet formulas like Query.

If you are not familiar with the term “mixed content data” see the sample data below. There are labels between numeric data that makes all the columns mixed type.

How to Filter and Delete Every Nth Row in Google Sheets

First I’ll explain how to filter and delete every 4th row in Google Sheets. After that, I will share with you how to use the 4th row as nth.

Set up Filters in Google Docs Sheets

First of all, select the data range. I have data in the range A2:C19 and I’ve selected that range. Then go to the “Data” menu and click on “Create a filter”. This action will create a Filter drop down in your Google Spreadsheet file.

In the second step, we want to insert the custom Filter formula in the designated field inside Filters. How to do that.

To key in the custom formula in the Filter views, do as follows.

• Click on the drop-down in the first column (Cell A2).
• Then click on the “Filter by condition”.
• Lastly, select the “Custom formula is”.

Still having any doubt? See the GIF screenshot below.

Now time to key in the custom filters formula to filter every 4th row.

Custom Formula Rule to Filter Every nth Row in Google Docs Sheets

Enter the below MOD formula in the provided field and click OK.

=mod((row(A3)-row($A$3)+1),4)=0

This filters only the rows that contain the labels. That means you can now only see every 4th rows in your data range.

Now select the rows 6, 10, 14 and 18 and right click and delete the rows. That’s all. Now you can remove the filter from the Data menu (Turn off filter). The Alt+D+F shortcut keys will also work.

See that all the labels have gone and you have clean data now. I didn’t delete the first row, that also you can delete.

How to Change the 4th Row to nth Row in the Custom Formula?

This is very important to know. First go thru’ the following formulas and try to understand the pattern.

The custom formula to delete every 2nd row:

=mod((row(A3)-row($A$3)+1),2)=0

The custom formula to delete every 3rd row:

=mod((row(A3)-row($A$3)+1),3)=0

I have already used the custom formula for the 4th row which is as follows.

=mod((row(A3)-row($A$3)+1),4)=0

Then see this Filters custom formula to delete every 5th row.

=mod((row(A3)-row($A$3)+1),5)=0

See the pattern. From this, you can understand one thing.

To filter and delete every nth row in Google Sheets you can use the Filters in Data menu. In that filter use a custom rule formula based on MOD.

In the MOD formula, the divisor represents the nth row number.

MOD(dividend, divisor)

The dividend is the very first cell in the data range. That’s all. Enjoy!

Similar Tutorials:

1. How to Highlight Every Nth Row or Column in Google Sheets.
2. How to Sum Every Nth Row in Google Sheets Using SUMIF.
3. Dynamic Formula to Select Every nth Column in Query in Google Sheets.
4. How to Copy Every Nth Cell in a Column in Google Sheets.