Excel formula: Copy value from every nth row

If you want to copy every nth row in Excel, you can accomplish this by combining several functions.

Copy a value from every nth row in Excel

Copying values with a specific pattern in Excel can be done using the OFFSET and ROW functions. OFFSET function returns a reference to a cell or range while ROW function returns the row number of a cell. This step by step tutorial will assist all levels of Excel users in copying values from every nth row.

Figure 1. Final result: Copy value from every nth row

Final formula starting at 1st row : =OFFSET($C$3,(ROW(C1)-1)*5,0)

Final formula starting at nth row : =OFFSET($C$3,ROW(C1)*5-1,0)

Syntax of the OFFSET function

=OFFSET(reference, rows, cols, [height], [width])

The parameters of OFFSET are:

reference – The reference cell where we will base the offset
rows – The number of rows either up or down the reference cell that determines the offset
Cols – The number of columns either left or right of the reference cell that determines the offset
Height and width are both optional; Height determines the number of rows while width determines the number of columns that we want the returned reference to be

Syntax of the ROW function

=ROW(reference)

reference – The cell or range of cells whose row number we want to determine

Setting up Our Data

Our data in column C shows a list of fifteen numbers, which are numbered accordingly for easy reference. In cells E3:E5, we will obtain the values every 5th row starting with the first value, while in cells E9:E11, we will obtain the values every 5th row starting with the 5th value.

Figure 2. Sample data to copy values from every nth row

Copy Value from Every nth Row

The formula to copy the values from every nth row depends on the first value that we want to copy. We could start at the first row or the nth row.

Formula starting with 1st row: =OFFSET(first_cell,(ROW(C1)-1)*n,0)

Formula starting with nth row: =OFFSET(first_cell,(ROW(C1)*n-1),0)

The first cell in our data serves as the reference cell for the OFFSET function.

Copy every 5th row, starting with 1st row

Figure 3. Formula to copy values from every 5th row, starting with 1st row

To copy values from every 5th row, starting with the first row in our data, we follow these steps:

Step 1. Select cell E3.

Step 2. Enter the formula: =OFFSET($C$3,(ROW(C1)-1)*5,0)

Step 3. Press ENTER

Step 4. Copy and paste the formula to the succeeding cells E4 and E5

The reference used is the first cell in the list which is C3. The ROW formula “(ROW(C1)-1)*5” determines the offset for the rows, which returns a multiple of 5.

In cells E3, E4 and E5, the 1st, 6th, and 11th values are copied. Finally, the results are 110, 160 and 210.

Copy every 5th row, starting with 5th row

Figure 4. Copy values from every 5th row, starting with the 5th row

To copy values from every 5th row, starting with the fifth row in our data, we follow these steps:

Step 1. Select cell E9.

Step 2. Enter the formula: =OFFSET($C$3,(ROW(C1)*5-1),0)

Step 3. Press ENTER

Step 4. Copy and paste the formula to the succeeding cells E10 and E11

The ROW formula “(ROW(C1)*5-1)” determines the offset for the rows, which returns the values 4, 9 and 14. This enables our OFFSET formula to return the cells corresponding to the 5th, 10th, and 15th rows.

In cells E9, E10 and E11, the 5th, 10th, and 15th values are copied. The results are 150, 200 and 250.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.