When entering data, we leave some slots empty to fill in more information later. And, we almost always end up with too many unused columns. Although deleting an individual column takes you a mere two clicks, the task can be quite tedious if you have a number of such empty columns.
I have seen many users attempt to delete columns using the Find & Select tool. Although it might seem like it would work, it could also go horribly wrong.
How to Delete Unused Columns in Excel
Now that you know what not to do, let’s look more into what you should do. So when you’re deleting multiple columns that are all empty, you will have to take a bit of your time; five minutes tops.
Step 1: Use COUNTA Function to Check if the Column is Empty
The COUNTA function in Excel is used to count cells that are non-empty. We can use this function to check if a column is actually empty or not. Secondly, we will be taking this as a reference point to sort our columns.
- Insert a new row over the existing first row.
- We entered the following formula and use Flash Fill to paste the formula on top of each column:
=IF(COUNTA(A2:A11=0), "0","1")
- Hit Enter.
Depending on if your column is truly unused, you will get “0” while if there is any data in the column, the formula will return “1”. If you see that your column appears empty but the formula is still returning “1”, it’s probably because there is a hidden character in one of the cells.
Step 2: Sort Columns
Now that the first row of your dataset is either 0 or 1, you can sort your columns according to that. What we will be doing now is that we will sort our columns in such a way that the empty columns are pushed to the left while the used columns will be on the right. This will make it easier for us to delete the selected columns.
- Select your data.
- Right-click on it and choose Sort > Custom Sort.
- In the Sort window, select Options.
- Choose Sort left to right > OK.
- Under Sort by, choose Row 1.
- Choose A to Z under the Order section.
- Click OK.
- In the pop-up, select the option Sort anything that looks like a number, as a number
Step 3: Delete the Unused Columns
The Sort utility will now push all of the columns that are unused from your datasheet on the left. We can now delete these columns at the same time using the delete shortcut.
- Select the empty columns on the left. Remember the columns have 0 as their header.
- On your keyboard, use Ctrl + – to delete the columns.
- If prompted, choose the Entire column option.
- You can also now delete the helper row we created to sort our values.