How do I select every 5th row in Excel?

Return to Excel Formulas ListSelect Every Other (or Every nth) Row Excel & Google SheetsDownload Example WorkbookDownload the example workbookThis tutorial will demonstrate ho

How do I select every 5th row in Excel?

Return to Excel Formulas List

Select Every Other (or Every nth) Row  Excel & Google SheetsDownload Example Workbook

Download the example workbook

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 Func

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 (odd / even).1=MOD(ROW(B3),2)

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:1=ROW(B3)

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.1=MOD(C3,2)

ROW MOD

Combining these functions yields the original formula.1=MOD(ROW(B3),2)

Select Every nth Row

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

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.1=OFFSET($B$3,(ROW(D1)*2)-1,0)

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.1=ROW(D1)

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.1=OFFSET($B$3,(D3*2)-1,0)

GET Value

Combining these functions yields the original formula.1=OFFSET($B$3,(ROW(E1)*2)-1,0)

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.123456789101112131415Sub SelectAltRows()Dim rng1 As RangeDim rng2 As RangeDim x As IntegerDim NoRws As Integer'select the rangeSet rng1 = Range("B1:B10")'count the rowsNoRws = rng1.Rows.Count'loop through every second cell of the rangeFor x = 1 To NoRws Step 2'put the value from column B into column Crng1.Cells(x, 1).Offset(0, 1) = rng1.Cells(x, 1)NextEnd Sub

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

Video liên quan