Akib Bin Rashid
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.
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.
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.
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.
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.
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.
Excel will delete the column for you.
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.
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.
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.
➤ 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.
➤ Delete the columns following method-1. The output will be like this.
Read More: VBA Macro to Delete Columns Based on Criteria in Excel (8 Examples)
Similar Readings:
- How to Delete Columns without Losing Formula in Excel (3 Easy Steps)
- Delete Column and Shift Left Using VBA in Excel (5 Methods)
- [Solved!] Can’t Delete Extra Columns in Excel (3 Solutions)
- How to Delete Infinite Columns in Excel (4 Methods)
- Delete Columns in Excel That Go on Forever (6 Ways)
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.
Actually, you cannot delete them. The best way to deal with them is to hide them.
STEPS:
➤ Select the E column.
➤ Then right click your mouse to bring the Context Bar. Select Hide.
Excel will hide column E for you.
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.
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.
