How to Delete Unused Columns in Excel (5 Quick Ways)

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. Here, we will show you 5 easy ways to do the task smoothly, So, let’s get into the article.

How to Delete Unused Columns in Excel: 5 Ways

The following dataset has Product, Product Code, Color, Size, and Price columns. Along with that, we have two unused columns as indicated by the red color. Now, using this dataset, we will go through 5 easy methods to delete unused columns in Excel. Here, we used Microsoft Office 365. You can use any available Excel version.

1. Using Go To Special Feature to Delete Unused Columns in Excel

In this method, we will use the Go To Special feature to delete unused columns in Excel. This method is helpful for finding out and deleting blank columns in Excel at once.

Steps:

• First, we will select the entire dataset by selecting range B4:H13.
• Go to the Home tab >> Select Editing >> Click on Find & Select >> Select Go To Special.

• The Go To Special dialog box will appear.
• Select the Blanks option >> press OK.

• The two unused columns will be highlighted below.

• To delete these columns, go to the Home tab >> Click on Cells >> Select Delete >> Click on Delete Sheet Columns.

• As a result, the unused columns will be deleted, and the following table will appear.

Read More: How to Delete Every Other Column in Excel

2. Use of Delete Option

In this method, we will use the Delete option from the Context Menu bar to delete unused columns in Excel.

Steps:

• At first, we select the first blank column >> Press the CTRL key >> Select another blank column.

• Right-click on these selected columns >> Select the Delete option from the Context Menu.

• The Delete dialog box will appear.
• Make sure the Shift cells left option is selected >> Press OK.

• As a result, the unused columns will be deleted.

Read More: How to Delete Infinite Columns in Excel

3. Employing Delete Sheet Columns Feature

In this method, we will employ the Delete Sheet Column feature to delete unused columns in Excel.

 Steps:

• First, select the unused columns.
• Here, we select the first blank column >> Press the CTRL key >> Select another blank column.

• Go to the Home tab >> Cells >> Select Delete.
• After that, we will select Delete Sheet Columns.

• Hence, the unused columns will be deleted as below.

Similar Readings

• How to Delete Multiple Columns in Excel
• How to Delete Columns with Specific Text in Excel
• Delete Multiple Columns in Excel with Condition
• How to Delete Column in Excel Without Affecting Formula
• How to Delete Empty Columns with Header in Excel

4. Applying COUNTA Function

In this method, we will use the combination of IF and COUNTA functions to delete unused columns in Excel.

4.1. Deleting Columns Without Using Sort Option

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. Here, we will not use the Sort option to delete the unused columns.

Steps:

• First, we will type the following formula in cell B5 to find out the column status.

• Next, press ENTER.
• You can see the result in cell B5.
• Drag the formula toward the right with the Fill Handle tool.

• As a result, you can see the complete column status.
• You can easily notice 2 blank cells in the column status.
• Then, you have to select the blank indicated columns and follow Method 3 to delete these columns.

• In this way, the unused columns will be deleted as the following one.

4.2. Using Sort Option

Here, we will use the Sort option to delete the unused columns. In this way, the blank columns can be put together to make it easy to delete these blank columns.

Steps:

• Select the entire dataset along with the Column Status >> Go to the Data tab >> Select Sort.

• The Sort dialog box will appear.
• Then, select Options.
• Another dialog box Sort Options will appear.
• Select Sort left to right >> Press OK.

• Select Row 5 in the Sort by box >> Select Cell Values in the Sort On box.
• In the Order box select A to Z.

• After that, the blank columns will be arranged together as below.
• Then, we will delete the blank columns by following Method 3.

• After deleting the blank columns the following table will appear.

Read More: [Solved!] Can’t Delete Extra Columns in Excel

5. Inserting VBA Code to Delete Unused Columns in Excel

In this method, we will insert VBA code to delete unused columns in Excel. This is a quick and handy method to get rid of unused columns at once.

Steps:

• First, go to the Developer tab >> select Visual Basic.
• This will bring out the VBA Editor window.
• You can also press ALT+F11 to bring out the VBA Editor window.

• The Visual Basic Editor window will appear.
• From the Insert tab >> Select Module.

• Type the following code in the Module.

Sub Remove_Unused_Columns()
    Dim product_rng, whole_col As Range

 Set product_rng = Application.InputBox( _
        "Choose your range:", "Remove Unused Columns", _
        Application.Selection.Address, Type:=8)

    If Not (product_rng Is Nothing) Then
        Application.ScreenUpdating = False

        For i = product_rng.Columns.Count To 1 Step -1
            Set whole_col = product_rng.Cells(1, i).EntireColumn
            If Application.WorksheetFunction.CountA(whole_col) = 0 Then
                whole_col.Delete
           End If

        Next     

    End If
End Sub

• Then, Save the code.
• After that, return to the worksheet.
• To run the code we will go to the Developer tab >> Select Macros.

• The Macro window will appear.
• Select the Sub >> Click on Run.

• The Remove Unused Columns input box will appear.
• We will select range B4:H13 >> Press OK.

• In this way, the unused columns will be deleted.

Read More: How to Delete Columns Based on Header Using VBA in Excel

How to Hide Unused Columns in Excel

Here, we will show you how you can hide unused columns in Excel.
In the following dataset, you can see that we have 2 unused columns. Now, we will hide these columns.

Steps:

• Select the first blank column >> Press the CTRL key >> Select another blank column.

• Go to the Home tab >> Select Cells >> Select Format >> Hide & Unhide >> Select Hide Columns.

• The columns will be hidden like in the following picture.

Read More: How to Delete Hidden Columns in Excel

Practice Section

You can download the above Excel file and practice the explained methods.

Download Practice Workbook

You can download the Excel file and practice while reading this article.

Conclusion

Here, we show you 5 easy methods to delete unused columns in Excel. Thank you for reading this article. We hope it was helpful. If you have any queries, please let us know in the comment section. You can visit our website for more related articles.

Related Articles

• VBA to Delete Column in Excel
• VBA Macro to Delete Columns Based on Criteria in Excel
• How to Delete Column and Shift Left Using VBA in Excel
• How to Delete Multiple Columns by Number Using VBA in Excel