How to Delete Unused Columns in Excel(Easiest 5 Methods)

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.

data table

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.

go to special

Step-02: After that Go To Special Dialog Box will appear where you have to select the Blanks option and then press OK.

go to special option

Step-03: Then the two unused columns will be indicated as below.

blank columns

Step-04: Now you have to follow Home Tab>>Cells>>Delete>>Delete Sheet Columns

deleting blank columns

Step-05: After that, the unused columns will be deleted, and the following table will appear.

how to delete unused columns in Excel

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.

go to special option for empty cells in used column

Step-01: After following Step-01 and Step-02 of Case-1 of this method the blanks will be indicated as below.

go to special option for empty cells in used column

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.

go to special option for empty cells in used column

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.

with a mouse click delete unused column

Step-02: Then Delete Dialog Box will appear where you will have to select Shift cells left and press OK.

deleting unused columns in Excel

Step-03: After that, the following table will appear.

deleting unused columns in Excel

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.

delete sheet column option

Step-02: After that, the unused columns will be deleted as below.

deleting unused columns in Excel


Similar Readings:


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.

using COUNTA function

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.

indicating blank columns

Step-03: Then you have to select the BLANK indicated columns and follow Method-3.

deleting unused columns in Excel

Step-04: In this way, the unused columns will be deleted as the following one.

delete unused columns in Excel

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

using sort option

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.

sort option

Step-03: Then select Sort by>>Row 1, Sort On>> Cell Values and Order>> A to Z.

sort option

Step-04: After that, the BLANK columns will be arranged together as below.

combined blank columns

Step-05: After deleting the BLANK columns the following table will appear.

deleting unused columns in Excel

Method-5: Using VBA Code

Step-01: At first go to Developer Tab >>Visual Basic or Press ALT+F11.

VBA code

Step-02: After that Visual Basic Editor will appear where you will have to follow Insert>> Module.

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

VBA code

Step-04: Then select the indicated Run Sub button or press on F5.

running macro

Step-05: Then select the range and press OK.

selecting range

Step-06: In this way, the unused columns will be deleted.

delete unused columns in Excel

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.


Further Readings

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo