Copy value from every nth row

In this example, the goal is to copy every nth value from column B, where n is a variable that can be changed as needed.

In Excel, it's difficult to create formulas that skip rows following a certain pattern, because the references in the formula will automatically change as the formula is copied in 1-step increments across cells. However, with a little work it's possible to construct formula references that follow specific patterns. In this example, we are using the OFFSET function, which is designed to create references to other cells, or cell ranges, based on a given starting point, or origin.

Starting at n

In the example shown, the formula in D5, copied down, is:

=OFFSET($B$5,ROW(D1)*3-1,0)

which copies every third value from the range B5:B59, starting at B7, which is the third cell in the range. The starting point inside the OFFSET function is the reference argument, provided as an absolute reference:

=OFFSET($B$5

The reference to B5 is locked so that it won't change as the formula is copied down. The next argument is rows, which indicates the desired row offset from the starting reference. Rather than a typical hardcoded number, rows is provided as an expression that calculates the required offset:

ROW(D1)*3)-1 // calculate rows offset

This is where n is provided as 3, in order to copy every third value. Here, the ROW function is used to get the row number for cell D1. We start with D1 because we want to start with 1 for the first value. As the formula is copied down the column, the value returned by ROW increments by 1 because the reference to D1 is relative. This result from ROW is multiplied by n, which is what creates the "every nth" pattern, in this case, "every 3rd". As the formula is copied down, the expression is evaluated like this:

ROW(D1)*3-1 // returns 2
ROW(D2)*3-1 // returns 5
ROW(D3)*3-1 // returns 8

These numbers may look odd to you in the context of "every 3rd value" but remember, this is an offset, starting with cell B5. The reason we subtract 1 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.

Finally, the columns argument is provided as zero (0), since we don't want any column offset; we want to stay in column B. As the formula is copied down, it returns the required references:

=OFFSET($B$5,ROW(D1)*3-1,0) // returns B7
=OFFSET($B$5,ROW(D2)*3-1,0) // returns B10
=OFFSET($B$5,ROW(D3)*3-1,0) // returns B13

The number n can be changed as needed. For example, if n is changed to 5, the formula will pick up every 5th value.

Starting at 1

To start copying at the first row in a given range, then follow the "every nth" pattern afterward, you can adjust the formula like this:

=OFFSET($B$5,(ROW(A1)-1)*n,0)

In this version, we subtract 1 directly from the result from the ROW function. This "zeros out" the first instance of rows, so that OFFSET returns a reference to the current cell. The formula in cell F5 uses this approach:

=OFFSET($B$5,(ROW(F1)-1)*3,0)

The results can be seen in column F.