How to Delete Every Other Column in Excel (5 Easy Ways)

Microsoft Excel is a very useful software. We use Excel functions and features for our educational, business, and other daily life purposes. Furthermore, Excel provides some cool features to delete alternate columns in our workbooks. This article will show you 5 easy ways to delete every other column in Excel.

How to Delete Every Other Column in Excel: 5 Ways

Deleting alternate columns manually can be really cumbersome if we consider a large amount of dataset. In this article, I will show you 5 easy ways to delete every other column in Excel. For instance, we take a dataset that represents the yearly sales budget and records of salespersons of a company below.

1. Use Custom Sort Feature to Delete Every Other Column

The Custom Sort feature specifies the order in which we sort our list. In this method, we will learn to delete every 2nd, 4th, and 6th column in our desired data range in the Excel workbook. We will use the MOD function to implement this method which returns a remainder.

Steps:

We need to add row 3 over our header row.
Input the following formula in cell B3.

=MOD(COLUMN()-1,2)

The COLUMN function returns the numeric value of the column number.
And, the MOD function will divide each column index with 2.
As a result, we will get two outputs: 1 & 0.
Hit enter and we get the remainder as 1 as it is an odd column index.

Drag the formula cell to the right to autofill all the columns.

Now, we have to change them to number values to avoid undesired outcomes while using the sort feature.
For this purpose, simply copy row 3.
Right-click on the same row.
Then, click the Paste Values option.

Select the entire data range and go to Data > Sort.

A dialogue box will pop up.
Here, click Options.

Another dialogue box shows up.
Mark the Sort left to right option and press OK.

Next, another dialogue box will be on the display.
Select Row 3 in the Sort by box and the Order will be Smallest to Largest.
Finally, tap OK.

Thus, the output row is sorted from 0 to 1.

We select 0 columns and right-click on them.
After that, click Delete to remove the columns.

At last, we get the desired result.

2. Manually Select Column in Excel to Eliminate

In this method, we manually select the columns we want to remove from the workbook. We will use the keyboard keys for this implementation.

Steps:

First, select the header of column C.
To select two or more columns, hold CTRL + left-click on the desired columns.

Right-click on the selected column.
An option box will pop up and select Delete option.

Finally, the columns are eliminated.

3. Delete Every Other Column Through Excel VBA

We can embed Excel VBA code to delete every other column. For instance, we want to delete the 2nd, 4th & 6th columns. Let’s see some easy steps to execute this.

Steps:

Go to the Developer bar and press Visual Basic.

After that, click Insert and create a Module.

Write the following VBA code in the module. You can also copy it from below.

Sub DltEvryOthrColumn()
Dim r As Range
Set r = Application.InputBox("Set the Range (Excluding headers)", "Range Selection", Type:=8)
For i = r.Columns.Count To 1 Step -2
If i Mod 2 = 0 Then
r.Columns(i).Delete
End If
Next i
End Sub

Hit the Run icon.

An input dialogue will be on the display.
Here, select the whole dataset and press OK.

Therefore, we delete the alternate columns.

4. Apply Find and Replace to Remove Alternate Column

Find and Replace is an Excel built-in tool that helps us to find data in the workbooks. In this method, we will look for positive cells and delete them using the Find and Replace tool. Let’s follow these procedures.

Steps:

To begin with, select the whole dataset.
Then, press Ctrl+F to open up the Find and Replace dialogue box.

The dialogue box will pop up.
Type Positive in the Find what box and press Find All.

As we can see, the highlighted matching results will pop up under the box.
Press Ctrl+A to select them all.

After selecting them, hold Ctrl and press the – key to open the delete dialogue box.
Here, click the Entire column option and tap OK.

The desired output will appear.

5. Delete Every Other Column using Blank Cell in Excel

Sometimes, our dataset contains blank cells. However, deleting them one by one can be really time-consuming. Follow these steps to quickly delete them using the Go to Special tool. It is an Excel tool that helps us to navigate our dataset and give us the exact locations of the searched data.

Steps:

We have some columns with blank cells. We have to delete these entire columns in order to implement this method.
First, select the whole dataset in the first place.

Press F5 to open up the Go to the dialogue box.
The dialogue box appears and tap Special.

Next, fill in the Blanks option and press OK.

As a result, all the blank cells are highlighted.
Now, right-click on any of those.

Select the Entire column to delete the blank columns.

Therefore, we remove the blank cells from the dataset.

Download Practice Workbook

Delete Every Other Column.xlsm

Conclusion

In conclusion, we have discussed here some easy ways to delete every other column in Excel. Not to mention, our website shows various simple problem-solving methods like this. Please leave any further queries or recommendations in the comment box below.

<< Go Back to Delete Columns | Columns in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

How to Delete Every Other Column in Excel (5 Easy Ways)

How to Delete Every Other Column in Excel: 5 Ways

1. Use Custom Sort Feature to Delete Every Other Column

2. Manually Select Column in Excel to Eliminate

3. Delete Every Other Column Through Excel VBA

4. Apply Find and Replace to Remove Alternate Column

5. Delete Every Other Column using Blank Cell in Excel

Conclusion

Related Articles

Leave a reply Cancel reply