Which can be copied across row 8 to pickup every 3rd value from row 5. 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: 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: 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: 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: 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.
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.
