Quick, clean, and to the point

Copy value from every nth row

Excel formula: Copy value from every nth row
Generic formula 

To copy values or generate references with a pattern like every 3rd row, every 7th line, etc. you can use a formula based on the the OFFSET and ROW functions. In the example shown, the formula in D5 is:


Which can be copied down column D to pickup nth values from column B.

How this formula works

In Excel, you can't easily copy formulas that need to skip rows or columns following a certain pattern, because the references in the formula will automatically change following the relationship between the original source cell and the new target cell. However, with a little work it's possible to construct formula references that follow specific patterns.

In this case, we're using the OFFSET function, which is designed to build references to other cells, or cell ranges, based on a given starting point, or origin.

In the example shown, the starting cell is B5, provided to OFFSET as the reference argument, and fixed as an absolute reference so it won't change as the formula is copied:


To calculate the correct value for the rows argument, we use a sub formula based on the ROW function:


Row is given the first cell in the same column, and returns 1. This result is multiplied by n, which is 3 in this example to get 3.

As the formula is copied down the column, the value returned by ROW increments by 1, which is what creates the "nth pattern". 

The reason we subtract 1 in each case is because the OFFSET function doesn't include the reference cell when the rows argument is applied. In other words, offsetting by one row from A1 returns A2:

=OFFSET(A1,1,0) // returns A2

Subtracting 1 takes this behavior into account.

Starting at 1

If you want to start copying at the first row, then follow the every nth pattern, you can adjust the formula like this:

Dave Bruns

Excel Formula Training
