Retrieve the value from every Nth row in Excel

Imagine that you want to retrieve the values from every 5th row in a certain range of your Excel worksheet.


In case your range is placed on column A:

1. Edit the first cell on the in the column where you want to have the list of every 5th row of column A
2. Write the following formula.
   
   =IF(ROW(A1)*5-5>ROWS($A$4:$A$50),"",INDEX(A$4:A$50,ROW(A1)*5-5))
    
3. Copy the formula all the way down to as many cells as your range rows divided by 5.
4. There you have it.

Note: This example shows you how to retrieve values from every 5th row in Excel but you can do this for any number. To do so just replace the 5-5 by the number you want.

Now let's break down the formula:

The formula starts with a IF function which will test if  ROW(A1)*5-5>ROWS($A$4:$A$50)

Basically this test will avoid a #REF error in case you copy the formula down to more rows than your range divided by 5. If you do, the formula will just retrieve a blank cell.

In case ROW(A1)*5-5>ROWS($A$4:$A$50) is False, then the following condition will be applied:

INDEX(A$4:A$50,ROW(A1)*5-5)

The index function will grab your range A1:A50 and retrieve the values from the following lines:

ROW(A1)*5-5  → 1*5-5 = line 1

ROW(A2)*5-5  → 2*5-5 = line 5

ROW(A3)*5-5  → 3*5-5 = line 10

...

ROW(A11)*5-5  → 11*5-5 = line 50

Do you like this free website? Please share this page on Google+ or Facebook.