Sometimes you may have a large dataset that you are working with in Excel and you want to select alternate columns, or every third or every fourth column in your dataset.
While there is no inbuilt feature to do this in Excel, there are some easy workarounds.
In this article, I’ll show you three ways to select every other column quickly, or every third or every fourth column.
Manually Select Columns Using Control Key
If you have a small dataset, where you only want to select a couple of alternate columns, the easiest and the fastest way to do this will be to do it manually.
Below I have a dataset where I want to select every other column.
Here are the steps to do this.
- Select the first column by clicking on the column header.
- Hold the Ctrl key on your keyboard and continue to hold it.
- One-by-one select all the columns that you want selected (while continuing to hold the Ctrl key).
- Once done with the selection, leave the Ctrl key.
Pro Tip: If, by mistake, you select a column that you did not want selected, you can click on that column header again and it would get deselected.
If you do not want to select the entire column but a specific range in each column (e.g., the first 10 cells or the first 20 cells), you can still use the same method by holding the Ctrl key and then one-by-one selecting the cells in the columns.
Extract Columns Using TOCOL Function
If you wish to select every other column so that you can copy them and paste them somewhere else, you can use a formula to do this.
Below I have a dataset where I want to extract every other column and get the result in a separate sheet (or in the same sheet).
Here is the formula that will do this:
=LET(
rng,B1:M11,
colgap,2,
CHOOSECOLS(rng,SEQUENCE(,COLUMNS(rng)/colgap,,colgap)))
Enter this formula where we want the columns to be extracted.
Select Columns using VBA
If you have a large dataset and selecting the columns manually or using a formula is not an option, you can also use a simple VBA code to do this.
Below is the VBA code that would select every Nth column in your dataset (where you can specify the column interval).
'Code by Sumit Bansal from https://trumpexcel.com
Sub SelectColumns()
Dim selectedRange As Range
Dim intervalInput As String
Dim interval As Integer
Dim col As Range
Dim resultRange As Range
Dim i As Integer
Dim columnCount As Integer
' Ask user to select a range
On Error Resume Next
Set selectedRange = Application.InputBox("Please select a range:", "Select Range", Type:=8)
On Error GoTo 0
' Check if user cancelled
If selectedRange Is Nothing Then
MsgBox "No range selected. Operation cancelled.", vbInformation
Exit Sub
End If
' Ask user for the interval
intervalInput = InputBox("Enter the column interval:" & vbCrLf & _
"2 = every 2nd column (alternate)" & vbCrLf & _
"3 = every 3rd column, etc.", "Column Interval")
' Check if user cancelled
If intervalInput = "" Then
MsgBox "No interval specified. Operation cancelled.", vbInformation
Exit Sub
End If
' Validate the interval input
If Not IsNumeric(intervalInput) Then
MsgBox "Please enter a valid number.", vbExclamation
Exit Sub
End If
interval = CInt(intervalInput)
' Validate interval is positive
If interval < 1 Then
MsgBox "Interval must be 1 or greater.", vbExclamation
Exit Sub
End If
' Build the range of columns to select
i = 1
columnCount = 0
For Each col In selectedRange.Columns
If (i - 1) Mod interval = 0 Then
If resultRange Is Nothing Then
Set resultRange = col
Else
Set resultRange = Union(resultRange, col)
End If
columnCount = columnCount + 1
End If
i = i + 1
Next col
' Select the result range
If Not resultRange Is Nothing Then
resultRange.Select
MsgBox "Selected " & columnCount & " column(s) with interval of " & interval, vbInformation
Else
MsgBox "No columns to select.", vbInformation
End If
End Sub
How to use this code:
- Press Alt + F11 to open the VBA Editor
- Go to Insert and then click on Module to create a new module
- Paste the VBA code above
- Close the VBA Editor
- Run the macro by pressing Alt + F8, selecting SelectColumns macro, and clicking Run
How this VBA code works:
- First, it prompts you to select a range (you will see an input box and then you can select the range in which you want to select the columns)
- Then it asks you to enter an interval number (2 for alternate, 3 for every third column, etc.)
- It will select the 1st column, then every nth column after that based on the interval you specify. For example, with interval = 2, it will select every other column, with interval = 3, it will selects every third column.
The VBA code also includes validation to handle cancellations and invalid inputs.
In this article I have covered three ways to select every other column in Excel. You can choose the method that suits your condition the best.
Other Excel articles you may also like:
