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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

Use Custom Sort Feature to Delete Every Other Column

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

output of custom sort feature to delete every other column

Read More: How to Delete Infinite Columns in Excel


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.

Manually Select Column in Excel to Eliminate

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

  • Finally, the columns are eliminated.

output to manual selection of columns to delete


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.

Delete Every Other Column Through Excel VBA

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

Apply Find & Replace to Remove Alternate Column

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

Delete Every Other Column using Blank Cell in Excel

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

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

delete every other column carrying blank cells

Read More: How to Delete Multiple Columns in Excel


Download Practice Workbook


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.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Yousuf Khan
Yousuf Khan

Yousuf Khan has been working with the ExcelDemy project for over one year. He has written 47+ articles for the ExcelDemy site. He also solved a lot of user problems with ExcelDemy Forum. He has interests in Excel & VBA, Desktop and mobile applications, and projects & templates. He completed his graduation and post-graduation in Information Technology from Jahangirnagar University. Currently, he works as a VBA & Excel Content Developer in ExcelDemy projects, writing unique and informative content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo