To import or extract every nth row from one sheet to another sheet in the same file in Google Sheets, we can use the Query or Filter functions.

Some of you may already know the combination of IF, MOD, and ROW to extract the values from every nth row.

I have already published the same here – How to Copy Every Nth Cell in a Column in Google Sheets.

It has (the above combo formula) one issue! The output will have lots of blank rows as the formula simply extracts value from every nth row and place it in another column in the same row.

For example, see the values in A3:B and my formula in cell C3 in the same sheet which returns values from every 5th row (for this kind of formula explanation, please refer to the above-linked tutorial).

=ArrayFormula(
     if(
        mod(row(A3:A)-row(A3),5)=0,
        A3:B,
     )
)

Points to Note

• If you want to extract value from only column B, then just change A3:B to B3:B. If the values are from A1:B, not A3:B, then change all the numbers 3 in the formula to 1.
• The number 5 is the ‘n’ in the above formula. Feel free to change that.
• The formula is on the same sheet. If the above data is in ’employee’ sheet and wants to import the values to the ‘summary’ sheet in the same file, use the below formula.

=ArrayFormula(
     if(mod(row(employee!A3:A)-row(employee!A3),5)=0,
        employee!A3:B,
     )
)

See the blank rows between the extracted values. How to omit that?

We can use the Filter or Query function for that. Let’s see how to import every nth row in Google Sheets without ‘unwanted’ blank rows. If the nth row is blank, that will be included in the result.

Filter to Import Every Nth Row in Google Sheets

The Filter formula is almost the same as the above combo formula. Here instead of the IF logical test, we can use Filter.

Syntax:

FILTER(
     range,
     condition1
)

I hope the following formula is self explanative.

=filter(
     employee!A3:B,
     mod(row(employee!A3:A)-row(employee!A3),5)=0
)

Here also the ‘n’ is set to 5. The data/range is in A3:B in the ’employee’ sheet and the formula is in another sheet in the same file.

To make you understand the difference with IF formula, I am inserting this formula in the same source data sheet, i.e. ’employee’.

Here is one more formula which may be new to many Sheet’s users.

Query to Import Every Nth Row in Google Sheets

Did you ever use or heard about the SKIPPING clause in Google Sheets Query? If not, you can go through my post – How to Move Each Set of Rows to Columns in Google Sheets.

Using the SKIPPING clause it’s quite easy to import/extract/filter every nth row in Google Sheets.

=query(employee!A3:B,"Select * Skipping 5")

The SKIPPING clause skips a certain number of elements from the result based on the count parameter. Here the count parameter is 5 which is our ‘n’.

The above Query returns columns A and B similar to the filter example above.

If you want only a particular column(s) after skipping rows, you can specify that in the SELECT clause like Select A instead of Select *. Please check my Query clause order for more info.

Resources

• How to Highlight Every Nth Row or Column in Google Sheets.
• How to Return Nth Occurrence of a Day in a Month in Google Sheets.
• Vlookup to Find Nth Occurrence in Google Sheets [Dynamic Lookup].
• How to Sum Every Nth Row in Google Sheets Using SUMIF.
• Dynamic Formula to Select Every nth Column in Query in Google Sheets.
• Delete Every Nth Row in Google Sheets Using Filters.
• Extract, Replace, Match Nth Occurrence of a String or Number in Google Sheets.
• Extract Every Nth Line from Multi-Line Cells in Google Sheets.
• How to Match | Extract Nth Word in a Line in Google Sheets.