Home » How to Select Every Nth Row in Google Sheets

How to Select Every Nth Row in Google Sheets

by Erma Khan

You can use the following formula to select every nth row in Google Sheets:

=OFFSET($A$1,(ROW()-1)*n,0)

This formula selects every nth row.

Simply change the value for n in the formula to select specific rows.

For example, you can use the following formula to select every 3rd row:

=OFFSET($A$1,(ROW()-1)*3,0)

The following examples show how to use this formula in practice.

Example: Select Every Nth Row in Google Sheets

Suppose we have the following list of values in Google Sheets:

We can use the following formula to select every third row from the list:

=OFFSET($A$1,(ROW()-1)*3,0)

We will type this formula into cell C1 and then copy and paste it to the remaining cells in column C:

Notice that every third row has been selected from the list of original values:

If we change the value of n in the formula, we can select a different nth value.

For example, we can use the following formula to select every fifth row from the list:

=OFFSET($A$1,(ROW()-1)*5,0)

We will type this formula into cell C1 and then copy and paste it to the remaining cells in column C:

Notice that every fifth row has been selected from the list of original values:

Feel free to use whatever value you’d like for n in the formula to select every nth row.

Additional Resources

The following tutorials explain how to perform other common operations in Google Sheets:

How to Count Rows with Value in Google Sheets
How to Count Filtered Rows in Google Sheets
How to Average Filtered Rows in Google Sheets

Related Posts