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.
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:
- 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:
- 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.
- 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.
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:
- First, select column header C.
- To select two or more columns, hold CTRL + left-click on the desired columns.
- Furthermore, right-click on the selected column.
- Consequently, an option box will pop up.
- Here, tap Delete.
- Lastly, the columns are eliminated.
Read More: How to Delete Multiple Columns in Excel
- How to Delete Extra Columns in Excel (7 Methods)
- Clear Contents in Excel Without Deleting Formulas (3 Ways)
- VBA to Delete Column in Excel (9 Criteria)
- Delete Columns in Excel Without Affecting Formula (Two Ways)
- How to Delete Unused Columns in Excel(Easiest 5 Methods)
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.
- Firstly, Go to the Developer bar and press Visual Basic.
- 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.
- To begin with, select the whole dataset.
- Then, press Ctrl+F to open up the Find & Replace dialogue box.
- 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 the – key 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.
- 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.
- 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.
Read More: Delete Blank Columns in Excel (3 Ways)
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.
- Delete Empty Columns with Header in Excel (4 Easy Ways)
- How to Delete Columns in Excel That Go on Forever (6 Ways)
- Can’t Delete Blank Columns in Excel (3 Problems & Solutions)
- Macro to Delete Columns in Excel (10 Methods)
- How to Delete Infinite Columns in Excel (4 Methods)
- [Solved!] Can’t Delete Extra Columns in Excel (3 Solutions)
- How to Delete Multiple Columns by Number Using VBA in Excel (4 Ways)