Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Select Every Other Column in Excel (3 Methods)

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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Methods to Select Every Other Column in Excel

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.

Manually select a column to select every other column

  • 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.

Select every other column keep pressing Ctrl button

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.

Select every other row in Excel

Read More: How to Reorder Columns in Excel (6 Easy Methods)


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.

Insert MOD function in Conditional formatting to select every other column

  • 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())

Insert ISEVEN function in Conditional formatting to select every other 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())

Read More: How to Group and Ungroup Columns or Rows in Excel


Similar Readings


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

Insert VBA code to select every other column

  • 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.

Read More: How to Select an Entire Column in Excel (5 Quick Methods)


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.


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. I hope this will satisfy your needs. Please have a look at our website ExcelDemy and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo