Explanation
In Excel, you can’t easily create formulas that skip columns following a certain pattern, because the references in the formula will automatically change to maintain 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 will follow a specific pattern using the OFFSET function.
The OFFSET function is designed to create references by using “offsets” from a starting cell. In the example shown, the starting cell is C5, provided to OFFSET as an absolute reference so it won’t change as the formula is copied:
=OFFSET($C$5
For the rows argument, we provide zero, since we want to stay in the same row. For the columns argument, we use a sub-formula to calculate the required offset value:
(COLUMN(A8)*3)-1
We use A8 inside COLUMN to return 1 (since A is the first column), then multiply by n (which is 3 in this case) to get 3.
As the formula is copied across the row to the right, the value returned by COLUMN increments by 1, which is what creates the “nth pattern”.
Starting at 1
If you want to start copying at the first value, you can adjust the formula like this:
=OFFSET($C$5,0,(COLUMN(A11)-1)*3)
By subtracting 1, we force a column offset of zero in the first formula.
Copy to rows instead of columns
To copy from columns into rows, you can modify the formula like this:
=OFFSET($C$5,0,(ROW(C1)*3)-1)
Here, the COLUMN function has been replaced with the ROW function , and a reference to the first row in the column, so that incrementing works correctly as the formula is copied down into multiple rows.
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 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.
