Can’t Delete Blank Columns in Excel (3 Problems & Solutions)

Excel is a part and parcel when it comes to working with large datasets. Sometimes, we can’t delete blank columns in Excel. This occurs because of some circumstances. In this article, I will discuss 3 such issues where you can’t delete blank columns in Excel and show you how to deal with those problems.


Download Practice Workbook

The password to unprotect the sheet is 1234

This is the dataset that I am going to use. I have some student Name along with their marks in Physics, Chemistry, Biology, and Mathematics. The column for Biology is blank. I will delete the column.

Can't Delete Blank Columns in Excel


3 Problems and Solutions of Can’t Delete Blank Columns in Excel

1. Can’t Delete Blank Columns in Excel When the Sheet is Protected

In Excel, you often fail to delete blank columns if the sheet you are working with is protected. I am explaining this with an example.

STEPS:

Suppose you want to delete the Biology column from your worksheet which is blank. To do so,
➤ First, select the range E5:E9.
➤ Then right click your mouse. Context Bar will pop up. But you will notice that you cannot select the Delete command.

can't delete blank columns in excel

That is because someone may protect the sheet. First, check from the Review tab Protect group to see whether the sheet is protected or not!
Someone has protected the sheet as it is showing Unprotect Sheet in the image below.

can't delete blank columns in excel

You have to first unprotect it to delete the column.
To unprotect a sheet, go to Review tab >> from Protect >> select Unprotect Sheet. (Here you will see Unprotect Sheet because someone has already protected the sheet. It would have been Protect Sheet if no one has protected the sheet earlier.)

Unprotect Sheet dialog box will appear. It will require you a password to unprotect the sheet.

can't delete blank columns in excel

This is the password that someone has set earlier to protect the sheet. You have to put the password. In our case, the password is 1234.
Collect the password and type it. Then, select OK.

can't delete blank columns in excel

Excel will unprotect the sheet.
Now try to delete the column. You can use any Delete option.
➤ First, select the range E5:E9.
➤ Then right click your mouse. Context Bar will pop up Select Delete.

Delete dialog box will appear. Select Entire column. Then select OK.

can't delete blank columns in excel

Excel will delete the column for you.

can't delete blank columns in excel

Read More: Delete Blank Columns in Excel (3 Ways)


2. How to Delete Blank Columns with Hidden Characters

Sometimes in Excel, we may encounter a column that seems blank but actually not. Because the cells in the column contain some hidden characters like a space ( ). For example, in the dataset, I have the History and Biology column. Both of them seem blank.

Now, select the range B4:G9. open Home tab >> go to Editing >> from Find and Select >> select Go To Special.

can't delete blank columns in excel

Go To Special dialog box will appear. Select Blanks and click OK.

Excel will find and select all the blank cells in the range B4:G9. You will notice that Excel has not selected the Biology column.

The reason behind this is the cells under the Biology column have hidden spaces ( ). They are not blank actually. For example, you can see the hidden spaces in cell F5 in the image below.

can't delete blank columns in excel

I will show you how to delete columns that contain hidden characters.

STEPS:

➤ Select the range F5:F9. Then press the DELETE from your keyboard.
Excel will delete all the hidden characters.

can't delete blank columns in excel

➤ Then select the blank cells from Go To Special like previously.
Now you will see that Excel has selected both the History and Biology columns.

can't delete blank columns in excel

Delete the columns following method-1. The output will be like this.

can't delete blank columns in excel

Read More: VBA Macro to Delete Columns Based on Criteria in Excel (8 Examples)


Similar Readings:


3. Can’t Delete Additional Blank Columns

In Excel, there are inbuilt columns with names A, B, C, D, etc.

 

You cannot break the serial by deleting a column. For example, in the image below, I have deleted the Biology column, but the serial is the same.

can't delete blank columns in excel

Actually, you cannot delete them. The best way to deal with them is to hide them.

STEPS:

➤ Select the E column.

can't delete blank columns in excel

➤ Then right click your mouse to bring the Context Bar. Select Hide.

Excel will hide column E for you.

can't delete blank columns in excel

Read More: VBA to Delete Column in Excel (9 Criteria)


Practice Workbook

It is important to practice if you can’t delete blank columns in Excel. That’s why I have attached a practice sheet for you.

can't delete blank columns in excel


Conclusion

In this article, I have mentioned 3 ways to solve the problem if you can’t delete blank columns in Excel. I hope you will find this article helpful. Lastly, if you have any queries or suggestions, leave them in the comment box below.


Related Articles

Akib

Akib

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo