Copy Every Nth Column in Excel (3 Quick Ways)

This article explains how to copy values from every nth column in Excel (for example, every 2nd, 3rd, or 5th column) and paste them into a new location. You’ll learn three quick methods: using a formula, using Go To Special, and using a simple VBA macro. Each method is explained step by step so you can choose the one that fits your data and skill level.

Formula

To copy values from every nth column, you can use a formula that references the source cell and then drags it across. The most common formula is:

=INDEX($A$1:$Z$100, ROW(), (COLUMN()-start_col)*n + offset)

Where:

• $A$1:$Z$100 – the range containing your data (adjust as needed).
• ROW() – the row number of the current cell (in the destination).
• COLUMN() – the column number of the current cell (in the destination).
• start_col – the column number where your first target column is (e.g., if you want columns B, E, H, then start_col=2).
• n – the interval (e.g., 3 for every 3rd column).
• offset – usually 0 if your destination starts at column A, but adjust if you start elsewhere.

This formula pulls the value from the source column that is n columns apart, based on the destination column position.

Worked Example

Suppose you have data in columns A through L (12 columns) and you want to copy every 3rd column (A, D, G, J). Your destination starts in column M (or any empty area).

In cell M1, enter:

=INDEX($A$1:$L$10, ROW(), (COLUMN()-13)*3 + 1)

Here, COLUMN()-13 gives 0 for M, 1 for N, 2 for O, etc. Multiplying by 3 gives 0, 3, 6, … Adding 1 shifts to column 1 (A), 4 (D), 7 (G), 10 (J). So M1 gets A1, N1 gets D1, O1 gets G1, P1 gets J1.

Drag the formula down and across to fill the range. For the default values (n=3, total columns=12), the formula returns 4 columns (A, D, G, J).

When to Use Each Method

• Formula (INDEX) – best for dynamic data that may change; updates automatically.
• Go To Special – quick for a one-time copy of visible cells only; works on selected range.
• VBA Macro – best for repeated tasks or huge datasets; one click after setup.

Method 1: Using a Formula (INDEX)

This is the most flexible method. You write a formula that references the source columns with a pattern, then fill down and across.

1. In the first destination cell, enter the INDEX formula as shown above.
2. Adjust the range and the column offset to match your data.
3. Press Enter, then drag the fill handle to the right and down to copy the formula across the desired number of columns and rows.

If you want to copy values (not formulas), copy the result and use Paste Special → Values.

Method 2: Using Go To Special (Visible Cells Only)

If you just need to copy every nth column once, you can hide the columns you don’t want and copy only visible cells.

1. Select the entire row of headers (or the first row of data).
2. Press Ctrl (or Cmd on Mac) and click on the column letters you want to keep (e.g., A, D, G, J).
3. Right-click a selected column header and choose Hide.
4. Select the entire range of data (including hidden columns).
5. Press Alt + ; (or Cmd + Shift + Z on Mac) to select only visible cells.
6. Copy (Ctrl+C) and paste to a new location.
7. Unhide the original columns (select all, right-click, Unhide).

This method copies only the visible columns, effectively skipping the hidden ones.

Method 3: Using a VBA Macro

For automation, a simple macro can loop through columns and copy every nth column to a new sheet.

1. Press Alt + F11 to open the VBA editor.
2. Go to Insert → Module.
3. Paste the following code:

Sub CopyEveryNthColumn()
    Dim sourceSheet As Worksheet
    Dim destSheet As Worksheet
    Dim n As Integer
    Dim lastCol As Long
    Dim i As Long
    Dim destCol As Long
    
    Set sourceSheet = ThisWorkbook.Sheets("Sheet1") ' change as needed
    Set destSheet = ThisWorkbook.Sheets.Add
    n = 3 ' change to your interval
    lastCol = sourceSheet.Cells(1, sourceSheet.Columns.Count).End(xlToLeft).Column
    destCol = 1
    
    For i = 1 To lastCol Step n
        sourceSheet.Columns(i).Copy destSheet.Columns(destCol)
        destCol = destCol + 1
    Next i
End Sub

4. Run the macro (F5). It creates a new sheet with the copied columns.

Common Mistakes and Pitfalls

• Off-by-one errors – double-check your start column and interval. If you want every 3rd column starting from B, your formula must offset accordingly.
• Copying formulas vs. values – if you copy formulas, they may reference the original cells; use Paste Special → Values if you want static data.
• Hidden rows and columns – Go To Special only works on visible cells; make sure you select the correct range.
• Large datasets – VBA might be faster than dragging formulas for thousands of rows.

Frequently Asked Questions

Can I copy every nth column without a formula?

Yes, you can use the Go To Special method (hide columns you don’t want, then copy visible cells) or a VBA macro. Both avoid writing formulas.

How do I copy every nth column but only specific rows?

Combine the methods: use the formula approach with both row and column offsets, or use a macro that loops through both rows and columns with a step value.

What if my data starts in column C, not A?

Adjust the formula’s start_col parameter. For example, if your first target column is C (column 3), use start_col=3 in the formula. For Go To Special, just select the correct columns.

Does this work in Google Sheets?

Yes, the INDEX formula works the same in Google Sheets. Go To Special is not available, but you can use the FILTER function or a script.

Can I copy every nth column and paste them into a single column (stacked)?

Yes, use a formula like =INDEX($A$1:$L$10, MOD(ROW()-1,n)+1, (INT((ROW()-1)/n)*n)+1) and drag down. This stacks values from each nth column vertically.

Is there a way to do this without VBA for a one-time task?

Yes, the Go To Special method is perfect for one-time tasks. It takes less than a minute and requires no formulas or code.

Related

• Outlook Show Two Time Zones: How to Set It Up
• Decentralized: What It Really Means
• Black Screen Counter Strike: Causes & Fixes

in n | Interval (n) | columns | 3 | 1
in c | Total columns | count | 12 | 1
out count | Columns to copy | count | ceil(c / n) | 0