Delete Columns in Excel (6 Different Cases)

In this article, you will learn how to delete columns in Excel. You can know about the methods of deleting columns with VBA, how to keep some defined columns, and when you want to remove the rest of the blank cells then what to do.

Not only this but also you will learn how to delete rows and cells in Excel.

Large datasets with numerous columns can slow down Excel’s performance. Deleting unnecessary columns reduces the file size. So, it will be leading to faster calculations. When you delete columns that contain unrelated information, your Excel file becomes more readable. This can be particularly helpful while sharing the file with others.

Basically, deleting unwanted columns helps you maintain a clean and organized worksheet. As a result, you can focus on relevant data only. Which will make you accurate conclusions and informed decisions.

Download Practice Workbook

You are recommended to download the Excel file and practice along with it.

Delete Columns in Excel: 6 Different Cases with Ways

1. Use of Delete Feature for Removing Multiple Contiguous Columns

• Select the columns by pressing on the left of the mouse. Then go to all adjacent cells and release the mouse.
• Alternatively, you can select all columns by pressing the CTRL key and select them manually.
• Now, right-click on selection >> from the Context Menu Bar >> choose Delete.

• Below, you can see all the columns are deleted.

• There is another way to delete contiguous columns. For this, you have to save your workbook with the .xlsm extension >> from the Developer tab >> go to Visual Basic.

• After that, the VB Editor will appear >> from Insert tab >> select Module >> write the following code in Module1.

Sub SelectDeleteAjacentColumns()
Worksheets("Dataset").Select
Range(Columns(7), Columns(10)).Delete
End Sub

Here, in the Range property, you have to set the column range. You just mention the 1st, and last columns as arguments of Range for deleting.

2. Deleting Columns in Excel That Go on Forever

• We have some unwanted columns (from the E column to the rest of the columns). Now we will delete those.
• Select E4 cell (must select a filled cell) >> press CTRL+SHIFT+Right Arrow >> so, the rest of the cells will be auto-selected.
• Now, from the Home tab >> under Cells group >> select Delete >> choose Delete Sheet Columns.

• Lastly, we removed all the columns.

3. Using Formula to Remove Blank Columns

• To keep the formula, firstly you need to insert a row. To do so, select the cell above which you want to insert the row >> right-click >> from Context Menu Bar >> select Insert.
• From the Insert dialog box >> select Entire row >> press OK.

• Now, in the C6 cell >> enter the following formula.

Here, in this formula, the COUNTA function will count the cells which are not empty. So, the logic in the IF function will be whether all the cells contain a value or not.

• Use the Fill Handle icon row-wise to copy the formula for the rest of the cells.

• Now, from the Home tab >> go to Editing group >> from Find & Select >> choose Find.

• As a result, you will get the Find and Replace dialog box. In that dialog box, write “TRUE” in the Find what box >> choose Values in the Look in box >> select Find All.
• Then you will get the list of all the cells having TRUE below. Press anyone from the list >> press CTRL+A to select them all.

• Now, from Home tab >> go to Cells group >> from Delete section >> choose Delete Sheet Columns.

• After that, select that extra row where was the formula >> again go to the Home tab.
• From Cells group , select Delete and choose Delete Sheet Rows.

• Finally, you will get the dataset without empty columns.

4. Using VBA to Delete Columns Having Certain Text

• First, we need to open the VB Editor from the Developer tab. Check out this article if you don’t have the tab on your ribbon: How to Display Developer Tab on the Ribbon in Excel.
• From the Developer tab >> click on Visual Basic >> so the VB Editor will be opened.
• From Insert tab >> click on Module >> write the following code in the module.

Sub Delete_Columns_having_Specific_text()
Dim my_cell As Range
For Each my_cell In Range("C5:N5")
If my_cell.Value = "Mar" Then
my_cell.EntireColumn.Delete
End If
Next my_cell
End Sub

Here, this code will remove all the columns (from the C column to the N column) if the cell value is “Mar”.

• To run the code now, save the code and go back to the worksheet.
• From the Developer tab, select Macros.
• In the Macro dialog box, select the name of the macro Delete_Columns_having_Specific_text >> press Run.

5. Use of Go To Special to Delete the Columns Having no Data

• From the Home tab >> go to Editing group >> from Find & Select >> choose Go To Special.
• Select Blanks in the Go To Special dialog box and press OK.

• As a result, you will get all the blank cells selected. Now, from the Home tab >> go to Cells group >> in Delete segment >> select Delete Sheet Columns.

Note: If there is any blank cell in a valued column, through this process that column will be also deleted.

6. Reducing Columns from Power Query

• Select the whole data >> from Data tab >> go to Get & Transform Data group >> select From Table/Range.
• You will get the Create Table dialog box >> check My table has headers (if your dataset contains the headers) >> press OK.

Note: As there is a blank column in the dataset, Excel can’t define the headers.

• In the Power Query Editor >> go to Home tab >> from Manage Columns group >> click on Remove Columns >> select the preferred one. Here, I want to remove most of the columns so I select these columns which will be kept >> then press Remove Other Columns.

• After deleting the columns, from the File tab >> Close & Load. Then, you will get your updated data in the worksheet.

• If you have a created Power Query, then click on any cell of the table >> from Query tab >> press Edit >> you will get the Power Query Editor.
• Use the Manage Columns group for deleting columns. In Power Query, there is a section named APPLIED STEPS, you can go back to the previous situation through this section.

Read More:

• Delete Unused Columns
• Delete Every Other Column

How to Delete Rows in Excel

• Select all the rows by pressing the row’s index number >> from the Home tab >> go to Cells group >> from Delete section >> select Delete Sheet Rows.

Read More:

• Delete Blank Columns
• Delete Empty Columns with Header
• Delete Hidden Columns

How to Delete Cells in Excel

• Select the cell >> from the Home tab >> go to Cells group >> from Delete section >> select Delete Cells.
• So, you will get a dialog box of Delete. From that, choose any one >> press OK.
• If you want to replace the right-side cell value with the selected one, then choose Shift cells left.
• Again, if you want to replace the lower cell value with the selected one then choose Shift cells up.
• Or, you can choose the Entire row which will delete the respective row.
• Else, you can choose the Entire column which will delete the respective column.

• As I have chosen Shift cells left, so the selected cell is removed, and all the other cells of the right-hand side shift towards the left.

Read More:

• Delete Multiple Columns
• Delete Multiple Columns with Condition
• Delete Columns with Specific Text

Frequently Asked Questions

1. What is the Shortcut for deleting columns?

Select the column index number (whole column) >> press CTRL+Minus(–) keys together. For multiple columns, just select all of them >> press the CTRL+Minus(–) keys together.

2. How do I remove columns from all sheets in Excel?

Press the CTRL key >> select all the sheets >> go to any of the sheets >> select the column >> from Home tab >> go to Cells group >> from Delete >> choose Delete Sheet Columns.

3. How do I delete 1000 columns in Excel?

Select the first column you want to delete >> Hold down the SHIFT key >> Press the right arrow key (→) 999 times. This will select a range of 1000 columns.

Then, press the CTRL+Minus(–) keys together >> a dialog box will appear asking whether you want to shift cells left or delete entire columns >> choose the option that says Entire column >> click OK.

Delete Columns in Excel: Knowledge Hub

• Delete Infinite Columns
• Delete Column Without Affecting Formula
• [Solved!] Can’t Delete Extra Columns

Conclusion

Hopefully, this article may help you to delete the unwanted columns of your Excel sheet. By removing unnecessary columns, you can optimize your worksheet, making it faster, easier to work with, and more focused on the relevant data.

Still, if you face any problems regarding this or any issues related to Excel then please comment below. Or you can mail us too.

<< Go Back to Columns in Excel | Learn Excel