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.


Download Practice Workbook

You can download this workbook to practice yourself.


5 Easy Ways to Delete Every Other Column in Excel

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.

how to delete every other column in excel


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. However, we will use the MOD function to implement this method which returns a remainder. Let’s go through the following procedures:

Steps:

  • Firstly, we need to add row 3 over our header row.
  • After that, we use Excel =MOD functions to new
  • Now, in B3, input the following formula:
=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.
  • Then, hit enter and we get the remainder as 1 as it is an odd column index.

Use Custom Sort Feature to Delete Every Other Column

  • We 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.
  • Subsequently, click the Paste Values option.

  • Afterward, select the entire data range and go to Data > Sort.
  • Accordingly, a dialogue box will pop up.
  • Here, click Options.

  • Again, another dialogue box shows up.
  • Here, fill in the Sort left to right option.
  • And, press OK.

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

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

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

  • At last, we get the desired result.

output of custom sort feature to delete every other column

Read More: How to Delete Multiple Columns in Excel with Condition


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. Follow these simple procedures:

Steps:

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

Manually Select Column in Excel to Eliminate

  • Furthermore, right-click on the selected column.
  • Consequently, an option box will pop up.
  • Here, tap Delete.

  • Lastly, the columns are eliminated.

output to manual selection of columns to delete

Read More: How to Delete Multiple Columns in Excel


Similar Readings


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:

  • Firstly, Go to the Developer bar and press Visual Basic.

Delete Every Other Column Through Excel VBA

  • After that, Click Insert and create a Module.

  • Thirdly, 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
  • Lastly, hit the run icon.

 

  • As a consequence, an input dialogue will be on the display.
  • Here, select the whole dataset.
  • Then, press OK.

  • Therefore, we delete the alternate columns.

Read More: How to Delete Columns Based on Header Using VBA in Excel


4. Apply Find & Replace to Remove Alternate Column

Find & 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 Find & Replace tools. Let’s follow these procedures.

Steps:

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

Apply Find & Replace to Remove Alternate Column

  • Momentarily, the dialogue box will pop up.
  • Later, type Positive in the Find what box.
  • Then, 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 thekey to open the delete dialogue box.
  • Here, click the Entire column option and tap OK.

  • In turn, the desired output will appear.

Read More: VBA Macro to Delete Columns Based on Criteria in Excel (8 Examples)


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:

  • As we can see, 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.

Delete Every Other Column using Blank Cell in Excel

  • Later, press F5 to open up the Go to the dialogue box.
  • Eventually, the dialogue box appears.
  • After that, tap Special.

  • Next, fill in the Blanks option and press OK.

  • As a result, the tool will highlight all the blank cells.
  • Now, right-click on any of those.

  • Finally, Select the Entire column to delete the blank columns.

  • Therefore, we remove the blank cells from the dataset.

delete every other column carrying blank cells

Read More: Delete Blank Columns in Excel (3 Ways)


Conclusion

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


Related Articles

Yousuf Khan

Yousuf Khan

Hello! This is MD Yousuf Khan. I am a graduate & post-graduate in Information Technology from Jahangirnagar University, Bangladesh. Currently, I am writing articles for ExcelDemy. I am an independent, self-motivated person with enthusiasm to learn new things, and always try to do my best in any work assigned to me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo