How to Delete Blank Columns in Excel (4 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn about how you can delete blank columns in Excel. Blank columns can create problems while formula calculation or exporting data. Blank columns also increase the file size significantly. So, deleting blank columns is important in Excel.

In this article, we will learn 3 ways to delete blank columns in Excel by selecting and deleting columns, using Excel editing tools, using a formula, and applying VBA code. We will use the following dataset to demonstrate the whole process. Here we have three blank columns (columns D, E, and G) in our working range that we need to delete.

4 Ways to Delete Blank Columns in Excel

1. Deleting Blank Columns in Excel After Selecting Manually Using Ctrl Key

The simplest way to delete blank columns in Excel. If there are a few blank columns that are in the working range, we can use this method.

🔶 Steps:

  • First, select the empty columns. If we have more than one column, then press Ctrl and then select them.

  • Then right-click on one of these column headers and select Delete.

Selecting and Deleting Blank Columns in Excel

  • As a result, we will see those blank columns are deleted.

Keyboard Shortcut to Delete Blank Columns:

Select the blank column/columns and then press Ctrl-. This will delete the blank columns.

Read more: How to Delete Unused Columns in Excel

2. Finding All Blank Columns Within Selection Using Excel Go To Special Command and Deleting Them

When we have a large number of columns that are not selectable by the method mentioned above, we will go for this method. The steps are described below.

🔶 Steps:

  • First, select the dataset.

  • We will go to the Find and Select option under the Editing section in the Home tab of the Ribbon and select Go To Special.
  • Go To Special dialog box will appear.

Using Excel Editing Tools

  • Select Blanks in the Go To Special window and press OK.

Using Excel Editing Tools

We can see all the blank boxes have been selected in that workspace.

  • Then, right-click on the selection and select Delete. A small dialog box will appear.

  • Now in the selection box, select Shift cells left.

  • This will shift the columns to the left since there is no blank column in between.

Using Excel Editing Tools to delete blank columns in Excel

Read more: How to Delete Empty Columns with Header in Excel

3. Removing Blank Columns After Finding Through COUNTA Function

You can use the COUNTA function to delete blank columns in Excel. The COUNTA function first identifies the blank and non-blank columns in your dataset in Excel, and then selects and deletes the blank columns.

🔶 Steps:

  • Insert a row on top of the dataset by selecting the row >> Right-click on it >> Select Insert.

  • Then in the leftmost working cell of that blank row (in our case it’s B4), write the following formula and press Enter.

Using “COUNTA” Formula to Delete Blank Columns in Excel

Here, B5 is the starting cell of our dataset. B1048576 is the maximum cell number possible in Excel (applicable for Excel 365). The =0 checks the blanks, meaning, if these cells in that range are all blanks or not. If blank or the condition is true, then it’ll show True in the cell, otherwise False.

  • Next pressing Enter will give us this result.

Using “COUNTA” Formula to Delete Blank Columns in Excel

  • We will drag the Fill Handle to the right side to see the status of the rest of the columns.

The result will be like the image below.

  • Select all the data and go to the Home tab in the ribbon.
  • Select Sort & Filter > Custom Sort > Sort dialog box will appear.

Using Custom Sort to delete blank columns in Excel

  • From the pop-up Sort window, click the Options.
  • Select Sort left to right from the Sort Options pop-up window and click OK.

  • Again, click the drop-down box under the Sort on label and select the option Cell Values from there.
  • Click the drop-down box under the Order label and select the option Largest to Smallest and click OK.

  • As a result, clicking OK will give us results like the following.

Using “COUNTA” Formula to Delete Blank Columns in Excel

  • You can also delete the True and False row if it’s no longer necessary.

Read more: How to Delete Every Other Column in Excel

4. Applying a VBA Code to Delete Blank Columns

If you are an experienced Excel user, then this method is especially for you. Implementing VBA code is the safest way to delete blank columns in Excel. It deletes the absolutely empty columns only. If any column contains a single cell value, even if any cell returns an empty string, still that entire column will be completely intact. The steps of this method are below.

🔶 Steps:

  • Go to the Developer tab in the ribbon and click on Visual Basic.
  • A window named Microsoft Visual Basic for Application will appear. You can do the same by pressing Alt + F11 as well.

  • In the window, select Insert > Module.

  • In that window, copy and paste the following code.
Sub del_blank_col()
Dim SrcRng As Range
Dim FullCol As Range

On Error Resume Next

Set SrcRng = Application.InputBox("Source Range:", "Delete Blank Columns!", Application.Selection.Address, Type:=8)

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

For i = SrcRng.Columns.Count To 1 Step -1
Set FullCol = SrcRng.Cells(1, i).FullCol
If Application.WorksheetFunction.CountA(FullCol) = 0 Then
End If

Application.ScreenUpdating = True
End If
End Sub

Applying VBA Code to Delete Blank Columns in Excel

  • Next, run the VBA by selecting the Run button from the menu.

  • Select the cell range after running the VBA.

Applying VBA Code

  • Pressing OK will immediately delete the blank rows.

Things to Remember

  • The select and delete feature is not suitable for large Excel files containing large amounts of data.
  • The editing tool is actually a special selection tool. So, we need to delete the blank columns separately after selection.
  • The COUNTA formula scans all the rows. So For bigger files, it may take some time.
  • The VBA process is irreversible. We can’t undo the process. So keeping a backup is recommended.

Download Practice Workbook


These are all the methods to delete blank columns in Excel. Hope it’ll help you. If you’re still having trouble with any of these methods, let us know in the comments. Our team is ready to answer all of your questions. For any Excel-related problems, you can visit our website for solutions.

Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel projects.
Sanjida Ahmed
Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF