Table of Contents
When working with large datasets in Excel, it can be useful to select every nth row. This can be done by using the OFFSET function, which takes a range as an argument and returns a reference to a range that is offset from the original range. To select every nth row, the OFFSET function is used in combination with the ROW function to create a dynamic range that is incremented by n for each row. An example would be: OFFSET(A1,(ROW()-1)*n,0). This formula will select every nth row starting from the first row.
You can use the following basic formula to select every nth row in Excel:
=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 Excel
Suppose we have the following list of values in Excel:
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.
=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:
