Md. Sourov Hossain Mithun
Tired of trying to delete unnecessary columns in your dataset, but you couldn’t? Well, maybe your workbook has some common issues for which deletion is not working. But don’t worry, we are here to save you! This article will show some common reasons and will give three smart solutions to fix the problem when you can’t delete extra columns in Excel.
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
3 Reasons and Solutions: Can’t Delete Extra Columns in Excel
To show the reasons and solutions, we’ll use the following dataset that represents some salespersons’ sales in different regions.
1. Make the Extra Columns Disappeared If You Can’t Delete Extra Columns in Excel
You cannot delete permanently the empty columns after the last columns in your dataset. Excel only removes contents from rows and columns. It never deletes them permanently.
Have a look that I have deleted Column E and go forward to the next image to see what happens.
The column still exists.
Solution:
So, what you can do is hide all the extra columns from your sheet and it will look like the columns are deleted. At least they will not be visible anymore!
Now let’s see how to do it.
Steps:
- Select the first extra empty column by clicking the column number.
- Then press Ctrl+Shift+Right Arrow key to select till the last column- 16,384th column of Excel.
- After that, right-click on any column.
- Select Hide from the Context menu.
Soon after, you will get all the extra columns hidden and look like deleted.
Read More: How to Delete Extra Columns in Excel (7 Methods)
2. Identify Space Characters and Delete Extra Columns in Excel
If your dataset has space characters in cells in one of its columns, it will look like a blank column.
In this dataset, we have an empty column.
First, let’s check if it is really empty or not.
- Select the Column D.
- Then click as follows: Home > Editing > Find & Select > Go To Special.
- Mark Blanks from the Go To Special dialog box.
- Then press OK.
Have a look that one cell is showing as non-blank that seems blank. So what is the reason?
The reason is there is space character(s) in this cell.
So if you use a tool to select all the extra empty columns in your dataset then that row won’t be selected. So what’s the solution?
Solution:
To solve this, follow the steps below.
Steps:
- Select the whole dataset.
- Later, press Ctrl+H to open the Find and Replace dialog box.
- Type space in the Find what box and keep the Replace with box empty.
- Finally, press Replace All.
Now Excel has removed the spaces and will give you a pop-up message about the result.
And now you will be able to detect all the extra empty columns in your dataset and delete them.
Read More: VBA Macro to Delete Columns Based on Criteria in Excel (8 Examples)
Similar Readings:
- How to Clear Contents in Excel Without Deleting Formulas (3 Ways)
- VBA to Delete Column in Excel (9 Criteria)
- Macro to Delete Columns in Excel (10 Methods)
- How to Delete Multiple Columns in Excel
3. Unprotect Sheet in Excel If You Can’t Delete Extra Columns
Another most common reason is- maybe your sheet is protected with a password and you have forgotten to unprotect it before deleting extra columns. So if you try to delete columns, the delete option with many other options will remain faded which indicates that you can’t use them.
Solution:
Let’s see how to unprotect the sheet.
Steps:
- Click as follows: Home > Cells > Format > Unprotect Sheet.
- Give the password and press OK.
- Select the extra empty column and see that the Delete option is activated.
- Click it to delete.
Yes! it’s deleted successfully.
Read More: How to Delete Infinite Columns in Excel (4 Methods)
Conclusion
I hope the procedures described above will be good enough to fix the problem when you can’t delete extra columns in Excel. Feel free to ask any question in the comment section and please give me feedback.
