Microsoft Excel is one of the most widely used applications in today’s world. It is very popular with individuals and small & medium business enterprises. One of the basic tasks of Excel is to select desired cells, rows, or columns and perform any specific task. In this article, we will discuss how to select every other or alternative column in Excel with proper explanations.
Select Every Other Column in Excel: 3 Methods
We will discuss 3 different methods including VBA Macro to select every other column in Excel. We will consider the following dataset to show you the methods.
1. Combination of Ctrl Key and Mouse Click to Select Alternative Columns
Usually, we can select any number of columns only by scrolling the mouse or pressing the Shift button with the right or left arrow. But selecting alternate columns is a little bit tricky. Have a look at the discussion below.
📌 Steps:
- Place the mouse cursor heading of column B.
- Now, click the left button of the mouse.
We can see the whole column has been selected.
- Now, press the Ctrl Keep pressing this button and select every other column of the dataset using the left button of the mouse.
We can see every other column of the dataset has been selected.
This method can also be used based on the keyboard and mouse to select alternative rows.
- Similar way, keep pressing the Ctrl button and select the row numbers from the leftmost side of the dataset.
2. Use Conditional Formatting to Highlight Every Other Column
In this section, we will show the use of conditional formatting to highlight every other column in Excel. For that, we need to apply a function inside the conditional formatting. Here, we will show how different functions influence conditional formatting to highlight alternate columns in the below section.
2.1 Conditional Formatting with MOD and COLUMN Functions
The MOD function returns the remainder after a number is divided by a divisor.
The COLUMN function returns the column number of a reference.
In this section, we will use the MOD function with the COLUMN function in conditional formatting.
📌 Steps:
- First, select the dataset range.
- Click on the down arrow of the Conditional Formatting section of the Home tab.
- Choose the New Rule option from the list.
- The New Formatting Rule window appears.
- Choose to Use a formula to determine which cells to format option.
- Then, put the following formula.
=MOD(COLUMN(B4),2)=0
- After that, click on the Format option.
- We will select a Fill color from this window.
- Then, press the OKÂ button.
- We went back to the previous window to have a look at the Preview section.
- Finally, press the OKÂ button.
We can see alternative columns have been highlighted.
2.2 Conditional Formatting with the ISEVEN Function
The ISEVEN function returns TRUE if the number is even.
The ISODD function returns TRUE if the number is odd.
In this section, we will use the ISEVEN function with the COLUMN function in the conditional formatting in Excel.
📌 Steps:
- We already showed the New Rule option of Conditional Formatting. Just put the following formula on the box marked as 2.
=ISEVEN(COLUMN())
We also showed the customization of the Format option.
- Then, press the OKÂ button.
We can see that even columns are highlighted. You can also use the ISODD function to highlight odd columns. Just apply the below formula.
=ISODD(COLUMN())
3. VBA Macro to Select Alternate Columns in Excel
In this section, we will use a VBA code to select every other column in Excel.
📌 Steps:
- Go to the sheet name section at the bottom of the dataset.
- Press the right button of the mouse.
- Choose the View Code option from the Context Menu.
- The VBA window appears.
- Choose the Module option from the Insert tab.
- After that, put the following VBA code on the module of the dataset.
Sub alternative_columns()
Dim range_1 As Range
Dim column_1 As Range
Dim union_1 As Range
Set range_1 = Application.InputBox("Selecet Range", Type:=8)
For i = range_1.Columns.Count To 1 Step -2
Set column_1 = range_1.Columns(i)
If Not union_1 Is Nothing Then
Set union_1 = Union(union_1, column_1)
Else
Set union_1 = column_1
End If
Next i
union_1.Select
End Sub
- Press the F5 button to run the code.
- A dialog box appears to select our desired range from the dataset.
- Select range B4:H9 and then click on the OKÂ button.
- Have a look at the dataset.
We can see every other column of our desired range has been selected.
How to Select Every Other Cell in Excel
In the above section, we discussed how to select every other column. Now, we will show how to select every other cell. It is quite similar to method 1.
We will keep pressing the Ctrl button and then select alternate cells by clicking the left button of the mouse each time.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, we described 3 methods to select every other column in Excel. We also showed how to highlight every other column in Excel.
