Select Every Other (or Every nth) Row – Excel & Google Sheets

This tutorial will demonstrate how to get a value from every other (or every nth) row in Excel and Google Sheets.

select every nth row Main Function

Identify Every Other Row

To identify every other row, we can use the MOD function to let us know if each Row is divisible by a certain number. Here we will use 2 to identify every other row.

MOD

We will walkthrough this below.

ROW Function – Return Row number

First, we will use the ROW Function to tell us which row we are in:

ROW

 

In the next section, we will use the MOD function to let us know if the row number is divisible by a certain number, in this case – 2.

MOD Function – Show if the Row is Divisible by 2

The MOD Function will show us if the row that we are in is divisible by 2.

ROW MOD

Combining these functions yields the original formula.

 

Select Every nth Row

To get the every 3rd (nth) row, we change the number to divide by to 3 (n).

With Filter 1

We can switch the filter on to filter on the MOD result required to show specific rows.

With Filter 2

 

Get Value from Every nth Row

To get the value from every other row or nth row, we can use the OFFSET and ROW functions.

OFFSET

We will walkthrough this below.

Return the nth Row

First, we will use the ROW Function to pick up the 1st Row.  Copying this formula down, will mean that the Row number picked up will dynamically change as the formula does.

GET ROW

 

In the next section, we will use the OFFSET function to get the value from the Row multiplied by the value you wish to offset the row by.

GET Value

Combining these functions yields the original formula.

 

Write VBA Code

We can use a VBA procedure to loop through a range of cells and return the value from every 2nd or nth cell.

VB 01

The following procedure will loop through the range of cells and populate every second row in Column C with the corresponding number from column B.

The result of running this procedure would be:

VB 02

Select Every Other (or Every nth) Row in Google Sheets

These formulas work exactly the same in Google Sheets as in Excel.

select every nth row Google Function

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!