How to select alternate columns in Excel

Select alternate columns in Excel

The simplest way to select alternate columns in Excel is by utilizing the Ctrl key in combination with the mouse. Here's how you can do it:

1. Press and hold the Ctrl key on your keyboard.
2. While holding the Ctrl key, click on the header of every other column.
3. Repeat steps 2 and 3 until you have selected all the desired columns.
4. Release the Ctrl key.

As a result, you have the alternate columns selected:

By using the Ctrl key, you can handpick every 3^rd, every 4^th or every nth column, so you can apply formatting or perform calculations on specific sections of your data.

This method of selecting every other or every nth column is a straightforward and effective approach, particularly for small datasets. However, it may become cumbersome and time-consuming when dealing with large sets of data. Manually clicking on each column header can be prone to errors and can become tiresome. In such cases, alternative methods come in handy to streamline the process and save valuable time.

Selecting every other or nth column with formula

If you prefer a more precise method to select every other or nth column in Excel, you can achieve this by using the CHOOSECOLS function. Here's how you can use it:

1. In an empty cell, enter the CHOOSECOLS formula. The first argument should be the source range that contains the columns you want to select.
2. In the subsequent arguments, provide the column numbers you want to return.
3. Press Enter to apply the formula.

For example, to select every even column in the range A2:F20, the formula takes this form:

=CHOOSECOLS(A2:F20, 2, 4, 6)

This formula specifies that you want to return columns 2, 4, and 6 from the range A2:F20.

The CHOOSECOLS formula returns the specified columns as a dynamic array, which you to easily select and copy to another part of your worksheet.

Excel VBA to select every other column

Using VBA macros in Excel, you can automate the process of selecting every other column with precision and efficiency.

Macro to select every even column in Excel

The following VBA code allows selecting every even column such as 2, 4, 6, etc. It automates the process by iterating through the columns within the pre-selected range and creating a new range that includes only the even columns.

Sub SelectEvenColumns() Dim selectedRange As Range Dim i As Integer Dim newRange As Range ' define selected range Set selectedRange = Selection ' for each column in the selected range For i = 2 To selectedRange.Columns.Count Step 2 ' add every even column to a new range If newRange Is Nothing Then Set newRange = selectedRange.Columns(i) Else Set newRange = Union(newRange, selectedRange.Columns(i)) End If Next i ' select new Range If Not newRange Is Nothing Then newRange.Select End If End Sub

To add the codes to your workbook, follow the steps described in How to insert VBA code in Excel.

To select every other column using a macro, follow these steps:

1. Highlight the range in which you want to select columns.
2. Press Alt + F8 to open the macro dialog box.
3. Choose the desired macro - SelectOddColumns or SelectEvenColumns.
4. Click Run to execute the code.

Excel macro to select every Nth column

If your task is to select every Nth column within a range, you can accomplish it using a VBA macro and a UserForm.

First, create the UserForm:

1. In the VBA Editor, click Insert > UserForm.
2. Design the UserForm with an input field such as a TextBox to enter the value for N.
3. Add an OK button to trigger the macro.

After completing the form's design, add the code for the OK button. For this, double-click the OK button on the UserForm and paste the following code in the Code window:

Finally, create the SelectEveryNColumn macro to incorporate the UserForm and complete the functionality. For this, add the following code to the module in your workbook:

Once implemented, running the macro will display a pop-up dialog box asking for the value of N. Enter the desired number, and the macro will select every Nth column within the selected range.

Practice workbook for download

VBA to select every other or every Nth column in Excel (.xlsm file)