How to Get Rid of Empty Columns in Excel: 3 Proven Ways

Want to remove empty columns in Excel? There aren’t any shortcuts! You must click a few buttons and use one or two Excel functions. To make things a bit easier, I’m explaining here how to delete empty columns in Excel with a little effort.

Probably you already know, data cleaning is an inseparable part of data analysis. When you import databases from third-party sources, you don’t know how the data will look on your Excel worksheet. You only know the format of the database like a CSV, TSV, etc. After importing in Excel, you may find many blank columns and cells in the database. 

Also read: How To Open A TSV File In Excel

You definitely need to get rid of these empty columns before you can effectively analyze datasets. There are a couple of smart processes for that if you want to save yourself from manually locating and deleting empty columns. Keep reading!

How to Delete Blank Columns in Excel Using a Formula

If you’re not ready to explore VBA scripts, this is the best way to remove empty columns from a large Excel worksheet. This is a semi-automatic way so you’ve got good control over the data you delete. Before trying any of the following steps, do take a backup of the Excel workbook.

Locate Empty Columns

1. Create a new row above the column headers of your database on the Excel worksheet.
2. To do this, click the column header row and right-click and choose Insert.
3. A new row will show up above the data columns.
4. Now, copy and paste the following formula in the first cell of the newly-created row above the column headers:

=IF(COUNTA(A5:A1048576)=0,"Empty","Not Empty")

5. Hit the Enter key to find the result, which is either Empty or Not Empty.
6. Then, paste the same formula across the new row from left to right until the end of the column headers.
7. As you keep pasting the formula, Excel will start showing whether the corresponding column is empty or not.

To briefly explain the above function, the COUNTA formula figures out if column A from cell range A5 through A1048576 equals the value of 0 or not. If it’s 0, you get a Yes and for a not 0 value, you get False. You must change the cell range of the formula according to your Excel worksheet. 

I wrapped the COUNTA formula within an IF function to convert True/False values to Empty and Not Empty so that any uninitiated person can easily understand.

Now that you know which are blank columns in the worksheet, let’s figure out how to delete all these columns quickly and not do it manually.       

Remove Empty Columns

1. Click the first cell on the first row of the worksheet and hit the Ctrl + A keys together.
2. Excel will select all the cells within the worksheet.
3. Now, click the Data tab on the Excel ribbon and then select the Sort command.
4. The Sort dialog box will open.

5. There, click the Options button and select the radio button for the Sort left to right feature.
6. Now, click the Sort by drop-down list and select Row 1.
7. Then, simply click the OK button.
8. Excel will sort the database based on the Empty and Not Empty column headers created on the new row.
9. Empty columns will show up at the beginning of the worksheet.

10. So, select the empty column A and drag the selection until you’ve selected all the empty columns.
11. Right-click and choose Delete from the context menu.

That’s it! You’ve successfully removed all true blank columns from your database on the Excel workbook. This method won’t be able to identify those empty columns that don’t contain any visible data but there are non-printing characters, non-breaking spaces, empty strings, or space characters.    

How to Remove Empty Columns in Excel Using a VBA Script

If you love writing automated VBA scripts to pace up your data analysis projects on Excel, you’ll love this method for sure. You just need to create a VBA macro by investing five to six minutes on the target worksheet. Then, you can use the same macro for years to delete empty columns in Excel. Let’s find out the code and the process to set up the macro:

Create and Save a VBA Module

1. On your Excel worksheet, click the File tab from the Excel ribbon.
2. Now, select Save As on the left-side navigation panel that shows up.
3. On the Save As screen, click This PC.
4. Then, hit the Browse button just below This PC.
5. Now, the Save As dialog box shows up.
6. There, click the drop-down list to the right end of the Save as type menu.
7. On the list that pops down, select the Excel Macro-Enabled Workbook option.
8. Click Save to close Save As.

You’ve just saved the worksheet in a format so that it can handle VBA macros. Now, follow these steps:

1. Hit the Alt + F11 keys together to open the VBA Editor tool.
2. There, click the Insert button.
3. On the context menu that pops down, click Module.
4. A blank Module window will open in the grey area of the VBA Editor.
5. Now, copy and paste the following VBA code as is in the new blank Module:

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

6. Click the Save button on the VBA Editor toolbar.

Congratulations! You’ve created VBA-based automation on Excel. Now that you’ve successfully created the VBA script, let’s find out how to use this macro in real-world scenarios to get rid of empty or blank columns on an Excel worksheet below:

Execute the VBA Macro

1. Select the entire worksheet.

2. Hit the Alt + F8 keys simultaneously to open the Macro dialog box.
3. Select the DeleteEmptyColumns dialog box. 
4. Hit the Run button to remove all true empty columns in a flash.
5. If the worksheet is huge containing thousands of columns, it might take a few seconds for the Excel app to find and erase all the empty columns.

Perfect! Your Excel worksheet is now free from unnecessary blank columns. Just like the previous method, this one isn’t able to delete columns that are blank but contains invisible characters or empty strings.

How to Get Rid of Empty Columns Using Office Scripts

Are you using a Microsoft 365 Excel desktop app or a web app with a paid subscription? You can use the Excel Automate or Office Script feature to delete empty columns. Follow these instructions:

1. Click Automate on the Excel ribbon.
2. Hit the New Script button in the top left corner.
3. Now, delete all the codes on the Code Editor.
4. Then, copy and paste the following code into the Code Editor:

function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getActiveWorksheet();
  let range = sheet.getRange();
  let numRows = range.getRowCount();
  let numCols = range.getColumnCount();
  let emptyCols = [];

  for (let j = 0; j < numCols; j++) {
    let isEmpty = true;
    for (let i = 0; i < numRows; i++) {
      if (range.getCell(i,j).getValue() !== "") {
        isEmpty = false;
        break;
      }
    }
    if (isEmpty) {
      emptyCols.push(j+1);
    }
  }

  if (emptyCols.length > 0) {
    sheet.getRangeByIndexes(0,emptyCols[0],numRows,emptyCols.length).delete(ExcelScript.DeleteShiftDirection.left);
  }
}

5. Now, click the Save script button to save the code.
6. Finally, hit the Run button to delete empty columns.

Conclusions

So, now you know the best three methods to delete empty columns on an Excel worksheet. Of these three, the COUNTA and Sort/Filter-based method is suitable for everyone, irrespective of coding knowledge. 

If you’ve got coding knowledge of VBA, you can choose the VBA method to delete blank columns. Finally, when you automate the same task on the Excel web app, you can use Automate or Office Scripts.