Summary

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:

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

Which copies values from every 3rd row in column B as the formula is copied down.

Generic formula

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

Explanation 

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 increment 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 afterwards, 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)

And the results can be seen in column F.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.