Select Every Nth Column in Excel

Selecting every nth column in Excel (for example, columns 1, 4, 7, 10…) is a common task when working with large datasets that have repeating patterns, such as weekly sales by day, survey responses by question group, or multi-year monthly data. You can do this with a formula that returns the column numbers, a simple Go To Special trick for a one-off selection, or a short VBA macro for a fully automated repeatable process. This guide covers the fastest methods, the exact formulas, and how to avoid the most frequent mistakes.

Method 2: Go To Special (for a One-Off Selection)

If you just want to select the columns (to delete, format, or copy them) without formulas, the fastest way is:

1. Select the entire range of columns you care about (e.g., A1:CV100).
2. Press F5 or Ctrl+G to open the Go To dialog.
3. Click Special…, then choose Columns differences – but this only works if you have a pattern of empty cells. A more reliable trick:
4. In a helper row (say row 1), type a formula in the first cell: =MOD(COLUMN(),3)=1 (for every 3rd starting at 1).
5. Copy that formula across the entire row.
6. Press F5 → Special… → Formulas → check Logical → OK. This selects all cells where the formula returns TRUE.
7. Now press Ctrl+Space to select the entire columns of those cells.

Method 3: VBA Macro for a Repeatable Selection

For a task you do often, a simple macro is the cleanest. Press Alt+F11, insert a module, and paste:

Sub SelectEveryNthColumn()
    Dim n As Integer, startCol As Integer, totalCols As Integer
    Dim i As Long
    n = 3  ' change to your interval
    startCol = 1  ' change to your start column
    totalCols = 100  ' change to your total
    For i = startCol To totalCols Step n
        Union(Columns(i), Selection).Select
    Next i
End Sub

Run it and it will select every nth column. Be careful: the Union method can slow down if you have thousands of columns, but for typical spreadsheets it is fine.

How to Choose the Right Method

• Need a list of numbers for a formula? Use SEQUENCE (method 1).
• One-off manual selection? Use Go To Special with a helper row (method 2).
• Repeat the same selection every week? Use the VBA macro (method 3).
• Want to sum every nth column? Use SUMPRODUCT with MOD: =SUMPRODUCT((MOD(COLUMN(A1:CV1)-1,3)=0)*A1:CV100) – adjust the range and the 0 (which corresponds to start column minus 1).

Common Mistakes and Pitfalls

• Off-by-one errors: If you want every 3rd column starting at column 1, the condition is MOD(COLUMN()-1,3)=0, not MOD(COLUMN(),3)=1 – both work but the first is easier to generalize.
• Forgetting that columns are numbers: Column A is 1, B is 2, etc. In formulas, COLUMN() returns the number, so you can do arithmetic on it.
• Using ROW instead of COLUMN: A common typo – ROW gives row numbers, not column numbers.
• Not locking ranges: When dragging a formula across, use absolute references like $A$1 if needed.

Frequently Asked Questions

How do I select every other column (n=2)?

Use the same methods but set n=2. For example, =SEQUENCE(50,1,1,2) gives columns 1,3,5,… up to 99. For Go To Special, use =MOD(COLUMN(),2)=1.

Can I select every nth column but skip the first one?

Yes, just change the start column. If you want columns 2,5,8,… set start=2. In the SEQUENCE formula, change the second argument to 2.

What if my data has headers in row 1?

Headers don’t affect column selection – you select entire columns regardless. If you only want to select data cells, select the range starting from row 2 instead.

How do I delete every nth column?

Select the columns using any method above, then right-click and choose Delete. Be careful: this shifts remaining columns left, which may break formulas that reference those columns.

Is there a way to do this without VBA or helper rows?

In Excel 365, you can use the FILTER function with a column index array: =FILTER(A1:CV100,MOD(COLUMN(A1:CV1)-1,3)=0) – this returns a new array with only every 3rd column. It is dynamic and does not require a helper row.

Why does my MOD formula return TRUE for the wrong columns?

Check your starting point. If you want column A (1) to be included, the formula must return TRUE when MOD(COLUMN()-1,3)=0. If you use MOD(COLUMN(),3)=1, it also works for column 1, but for column 4 (which is also 1 mod 3) it works too – so both are correct. The issue usually comes from using a different start column without adjusting the formula.

Related

• Zoom Dysmorphia: Definition, Causes & Fixes
• Future-Proof Your Test Automation Pipeline
• Dummy RAM: What It Is and How to Spot It