Get/Extract Every Nth Value In Excel

In this post we will see how to extract every nth value/row in excel.

We will try to understand how to extract every nth value row in excel with couple of steps mentioned below. There are ways  we could achieve that by using mod function in a column to get every nth row and then applying filter in that column for only 0’s(MOD(row_num,n)=0 means the row number is n)).

As you could see below we have data in column A from which we want to extract every nth value/row and in cell C2 we have the value of N(Currently 4) that we could change to some other number based on our requirement.

In column E we have the out data which every nth value, in this case every 4 th value.

Step 1

In cell C2, enter the value of N, in this example we want to extract every 4^th value hence 4.

Enter the formula =IFERROR(INDEX($A$2:$A$38,ROWS(E$3:E3)*$C$2,1),””) in the cell E2

We will find the row number with the help of rows formula and once we have the row number we could use INDEX formula to extract that row’s value.

IFERROR helps us suppressing the error messages in the cell when no more values are there to be returned.

As you could see we have the value 82 which is the 4^th value in our data column A.

Step 2

Now let’s drag down our formula to the cells below to extract every 4^th value.

So, we are done now in extracting every nth value in excel. In case you want to extract every 6^th value, just change the value in cell C2 to 6.

We have used the IFERROR formula and therefore we are not seeing any error message after the last value 80 in our output column.

Hope this helped.