Exceljet

Quick, clean, and to the point

Copy value from every nth column

Excel formula: Copy value from every nth column
Generic formula 
=OFFSET($C$5,0,(COLUMN(A8)*n)-1)
Explanation 

To copy values or generate references with a pattern like every 3rd column, every 5th column, etc. you can use a formula based on the the OFFSET and COLUMN functions. In the example shown, the formula in C8 is:

=OFFSET($C$5,0,(COLUMN(A8)*3)-1)

Which can be copied across row 8 to pickup every 3rd value from row 5.

How this formula works

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 the OFFSET function. The OFFSET function is designed to create references by "offsetting" rows and/or columns from a starting reference.

In the example shown, the starting cell is C5, provided to OFFSET as the reference argument, and fixed 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 in 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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables