Table of Contents
Today, in this article we will show you how to Delete blank columns in Excel. When you copy data from web pages, there may be times when a lot of columns show up even though they aren’t used. This is just as likely to happen with CSV files as it is with.txt files. When this happens, it may not always be easy to delete columns by hand.
Yes, you can delete them by hand if you only have two or three empty sections. When you are working with a lot of data, it is a tedious job to get rid of empty columns and rows. And you can’t even delete things by hand. You can also go to the official Microsoft Excel support site to know more information.
In this piece, you can use the built-in Excel tool Go To Special to delete blank columns in a few easy steps. It is an easy and quick way to get rid of the empty spaces. This makes it easy to use as an alternative, but be aware that it could cause your paper to be out of place.
Ways to Delete blank columns in Excel
Delete Blank Columns Using COUNT Function + Sort/Find and Replace
Excel has a feature that lets you quickly select empty cells (using the Go-To special dialog box, as we’ll see later in this tutorial), but there’s no quick way to pick only empty columns.
So, we’ll have to find a way around it by first finding the columns that only have blank cells and then deleting them. We have a set of sales numbers for different items from different shops in the table . As you can see, some of the columns in the data set below are completely empty.
Manually Deleting Blank Columns
If your data set is small, like the one below, you can directly choose the blank columns and delete them. Here are the steps to personally get rid of empty columns in the above data set:
- Select the blank column that you want to delete by clicking on the column header of that column
- Once the blank column is selected, right-click on the selection
- Click on the ‘Delete’ option
Using the COUNTA formula with FIND and Replace
When working with big datasets, the best way to delete all empty columns is to add a helper row at the top and use a COUNTA formula to find all the empty columns.
Once you’ve done that, you can use this helper row to quickly pick all the empty columns and delete them all at once. We have a set of data with some empty entries that I want to get rid of. Here’s how to do it with the COUNTA function and a “helper row”:
- Select the first row of your data set by clicking on the row header (it’s the row number in grey on the left)
- With the entire first row selected, right-click and then click on Insert. This will insert a new row above the first row of your data set.
- Enter the formula in the first cell of the helper row and copy it for all the cells in the helper row
=IF(COUNTA(A2:A1048576)=0,"Blank","Not Blank")
The above formula uses the COUNTA function to figure out how many cells in the given range are not empty. This formula will return a number bigger than zero for all columns that are not completely empty and 0 for any column that is completely empty. Then, we used the IF function to make a cell return “Blank” if the whole column below it was empty and “Not Blank” if it wasn’t.
Now that we can find all the empty columns by looking at the values in the helper row, we’ll use the Find and Replace dialog box to quickly pick all the cells that have the value “Blank.”
Once these cells have been chosen, we can delete the whole column at once.
- Select all the cells in the helper row (the one where we entered the COUNTA formula)
- Hold the Control key on your keyboard and then press the F key. This will open the Find and Replace dialog box. You can also open the Find and Replace dialog box by going to the ‘Home’ tab and then clicking on the ‘Find & Select’ option and then clicking on the ‘Find’ option
- In the Find and Replace dialog box, enter the text ‘Blank’ in the ‘Find what’ field
- Click the ‘Options’ button
- In the ‘Look in’ drop-down, select ‘Values’
- Check the option, ‘Match entire cell contents’
- Click on the Find All button. This will find and return the cell references of all the cells that contain only the text ‘Blank’
- Hold the Control key and press the A key. This will select all the cells that were given by the Find and Replace option
- Right-click on any of the cells that have been selected (which would be any cell that has the text ‘Blank’ in it), and then click on ‘Delete’
- In the Delete dialog box, select the ‘Entire Column’ option
- Click OK