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

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset to Delete Unused Columns in Excel


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.

Using Go To Special Featue to Delete Unused Columns in Excel

  • 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.

Use of Delete Sheet Column Feature to Delete Unused Columns in Excel

  • 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.
Note: You must press the CTRL key to select non-adjacent columns.

Pressing CTRL Key to Delete Unused Columns in Excel

  • 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.

Use of Delete Option to Delete Unused Columns in Excel

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.

Employing Delete Sheet Columns Option to Delete Unused Columns in Excel

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


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.
=IF(COUNTA(B7:B1048576)=0,"BLANK", "Not BLANK")

Combining IF and COUNTA Functions to Delete Unused Columns in Excel

Formula Breakdown

  • COUNTA(B7:B1048576)=0 is a logical test; BLANK will return if the logical test is true otherwise it will return Not BLANK.
  • B1048576 is used because Excel has 1048576 cells in a column. The COUNTA function will count the number of cells that are not empty.
  • COUNTA(B7:B1048576)=0 → becomes
    • Output: FALSE
  • IF(COUNTA(B7:B1048576)=0,”BLANK”,”Not BLANK”) → becomes
    • IF(FALSE,”BLANK”,”Not BLANK”)
      • Output: Not BLANK
    • Explanation: Since the logical test is true, the IF function returns Not BLANK.
  • Next, press ENTER.
  • You can see the result in cell B5.
  • Drag the formula toward the right with the Fill Handle tool.

Use of Fill Handle Tool to Delete Unused Columns in Excel

  • 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.

Using Sort Feature to Delete Unused Columns in Excel

  • 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.

Inserting VBA to Delete Unused Columns in Excel

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

Inerting Module to Delete Unused Columns in Excel

  • 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.


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


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


<< Go Back to Delete Columns | Columns in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo