Tanjima Hossain
If you are looking for some of the easiest ways to delete unused columns in Excel, then you are in the right place. Sometimes it happens to have some columns inside a data table that remain unused, and it becomes necessary to remove them in an effective way. So, let’s get into the article.
Download Excel Workbook
5 Ways to Delete Unused Columns in Excel
Here, I have a data table in which I have two unused columns as indicated by the red color. In addition, I have some empty cells in the Size column but I don’t want to delete them. Using the following methods I will show you how to delete these unnecessary columns easily.
Method-1: Using Go To Special Option
Case-1: Only Having Unused Columns
Step-01: Let’s consider that I have no empty cells in the used columns and I have two unused columns here. To delete these columns, go to Home Tab>>Editing>>Find & Select>>Go To Special
You can go there by pressing CTRL+G and then clicking on Special.
Step-02: After that Go To Special Dialog Box will appear where you have to select the Blanks option and then press OK.
Step-03: Then the two unused columns will be indicated as below.
Step-04: Now you have to follow Home Tab>>Cells>>Delete>>Delete Sheet Columns
Step-05: After that, the unused columns will be deleted, and the following table will appear.
Case-2: Having Unused Columns and Unused Cells in Used Column
Similar to the following table, if you have unused cells too then using the Go To Special option will not be effective because it will delete the used column having empty cells also.
Step-01: After following Step-01 and Step-02 of Case-1 of this method the blanks will be indicated as below.
Step-02: Then following Step-04 of Case-1 of this method will delete the Size Column which is necessary but due to having some empty cells it has also been deleted. So, this method is not appropriate for this case.
Read more: Delete Blank Columns in Excel
Method-2: Deleting Unused Columns with a Mouse Click
Step-01: At first select the two unused columns pressing CTRL and then right-click on your mouse and then select the Delete option.
Step-02: Then Delete Dialog Box will appear where you will have to select Shift cells left and press OK.
Step-03: After that, the following table will appear.
Read more: How to Delete Extra Columns in Excel
Method-3: Using Delete Sheet Column Option
Step-01: At first select the unused columns and then follow Home Tab>>Cells>>Delete>>Delete Sheet Columns.
Step-02: After that, the unused columns will be deleted as below.
Similar Readings:
- How to Delete Multiple Columns in Excel
- Delete Columns in Excel Without Affecting Formula (Two Ways)
- How to Delete Duplicate Columns in Excel (6 Ways)
Method-4: Using COUNTA Function
Case-1: Not using Sort Option
Step-01: For a large dataset having so many blank columns it may become easier to indicate the blank columns. To indicate the blank columns, you have to use the COUNTA function within the logical IF function.
=IF(COUNTA(B3:B1048576)=0,"BLANK","Not BLANK")
Here, COUNTA(B3:B1048576)=0 is a logical test; BLANK will return if the logical test is true otherwise it will return Not BLANK.
=COUNTA(value1,value2,...)
Here, values are B3:B1048576, and B1048576 is used because Excel has a total of 1048576 rows. The COUNTA function will count the number of cells that are not empty.
Step-02: After pressing ENTER and dragging it to the right side the following result will appear where blank columns will be indicated by BLANK.
Step-03: Then you have to select the BLANK indicated columns and follow Method-3.
Step-04: In this way, the unused columns will be deleted as the following one.
Case-2: Using Sort Option
Step-01: The blank columns can be put together to make it easy to delete these BLANK columns. To do this select the data table and then follow Data Tab>>Sort
Step-02: After that, a Dialog Box will appear where you have to select Options and then another Dialog Box; Sort Options will appear in which you have to select Sort left to right and then press OK.
Step-03: Then select Sort by>>Row 1, Sort On>> Cell Values and Order>> A to Z.
Step-04: After that, the BLANK columns will be arranged together as below.
Step-05: After deleting the BLANK columns the following table will appear.
Method-5: Using VBA Code
Step-01: At first go to Developer Tab >>Visual Basic or Press ALT+F11.
Step-02: After that Visual Basic Editor will appear where you will have to follow Insert>> Module.
Step-03: Then Module 1 will be created and here you have to write the following code.
Public Sub DeleteEmptyColumns()
Dim SourceRange As Range
Dim EntireColumn As Range
On Error Resume Next
Set SourceRange = Application.InputBox( _
"Select a range:", "Delete Empty Columns", _
Application.Selection.Address, Type:=8)
If Not (SourceRange Is Nothing) Then
Application.ScreenUpdating = False
For i = SourceRange.Columns.Count To 1 Step -1
Set EntireColumn = SourceRange.Cells(1, i).EntireColumn
If Application.WorksheetFunction.CountA(EntireColumn) = 0 Then
EntireColumn.Delete
End If
Next
Application.ScreenUpdating = True
End If
End Sub
Step-04: Then select the indicated Run Sub button or press on F5.
Step-05: Then select the range and press OK.
Step-06: In this way, the unused columns will be deleted.
Conclusion
In this article, I tried to cover the easiest ways to delete unused columns in Excel. Hope You have found this article useful. If you have any further suggestions, you can share them with us. Thank you.
” title=”