And one of the things I had to do while cleaning the data was to delete any blank columns in the data set. While you can always manually select columns and delete them one by one, doing so in a large data set, where you have tens or hundreds of columns in every data set, would be inefficient and error-prone. While there is no inbuilt functionality in Excel to date blank columns in one go, this can be achieved by using a combination of different functionalities. In this tutorial, I will show you how to delete empty columns in Excel using a couple of different methods (including a simple VBA code).
Manually Deleting Blank Columns (Best with Small Datasets)
If you have a small data set such as the one shown below, it’s possible to manually select the blank columns, and delete them. Below are the steps to delete blank columns manually in the above data set: The above steps would instantly delete the selected blank column, and shift the remaining data set to the left. Pro Tip: You can also select multiple blank columns in one go by holding the Control key on your keyboard (or the Command key if you’re using a Mac OS), and then manually clicking on the column headers of all the blank columns that you want to select. Once all the blank columns are selected, you can right-click and then click on the Delete option to delete all blank columns in one go The biggest drawback of this method is that it is manual and inefficient (however, it could be a preferred method with small datasets). In case you have a large data set with lots of blank columns, it’s better to use the methods covered next
Delete Blank Columns Using COUNT Function + Sort/Find and Replace
Excel has an inbuilt functionality that allows you to quickly select blank cells (using the Go-To special dialog box as we will see later in this tutorial), but there is no way to quickly select only those columns that are empty. So we will have to use a workaround to first identify those columns that have only blank cells in them and then delete these blank columns. Below I have a data set where I have the sales figures of different stores for different items. As you can see there are some columns that are completely empty in the below data set. Now there are a couple of methods you can use to remove blank columns from the dataset. Let’s have a look at each of these methods in detail.
Using the COUNTA formula with FIND and Replace
With large datasets, a better way to delete all blank columns is by inserting a helper row at the top and using a COUNTA formula to identify all the columns that are empty. Once you have done that, you can use this helper row to quickly select all the blank columns and delete them in one go. Below I have a data set where I have some blank columns that I want to remove. Here are the steps to do that using the COUNTA function with a helper row: The above formula uses the COUNTA function and calculates the total number of cells that are not empty in the specified range. This formula is going to return a value greater than zero for all the columns that are not completely empty and zero for any column that is completely empty. And then I used the IF function to return “Blank” in a cell if the entire column below it is empty, and “Not Blank” if it is not empty. Now that I can identify all the empty columns by looking at the values in the helper row, I’m going to use the Find and Replace dialog box to quickly select all the cells that have the value “Blank”. Once I have these cells selected, I can delete the entire column in one go. Below are the steps two now select all the empty columns in one go: The above options would instantly delete all the blank columns in your data set. Important Notes:
COUNTA function would only return 0 if all the cells in the column are blank. in case there is a header in the column that has no values in it and needs to be removed, you need to adjust the formula so that the header row is not used. For example, you can use the formula =IF(COUNTA(A3:A1048576)=0,”Blank”,”Not Blank”) in case even blank columns have headers For this formula to work, the columns need to actually be blank. For example, if there are space characters in the cells in the blank column, while they might appear to be empty, the COUNTA function would not consider it as blank.
Using the COUNTA Formula with Sort Option
Let me show you another smart way you can use to quickly delete all the empty columns in Excel. In this method, we will still be using the COUNTA function to get ‘Blank’ or ‘Not Blank’ in the helper row based on whether the column is empty or not. But instead of using the Find and Replace dialog box, we will use the Sort option Below I have the same data set and I want to remove the blank columns. Below are the steps to insert a helper row to identify columns that are empty: The above formula would return the text “Blank” in cells where the column below it is empty and “Not Blank” when the column below it is not empty. Now I can sort the entire data set using the helper row so that I get all the blank columns together and all the non-blank columns together Below are the steps to do this: The above steps would sort the data based on the helper row and bring all the blank columns together and non-blank columns together (as shown below). Once you have all the blank columns together, you can select these in one go, and then delete them. And once you have deleted the blank columns, you can remove the helper row as well.
Delete Blank Columns Using VBA
While the methods covered above work great, they do require a little bit of a setup using a helper row. In case you are comfortable with VBA, you may find it a little easier to use as compared with the two helper row methods covered above. Below is the VBA code that will do this for you: The above VBA code uses a simple For-Next loop to go through each column in the selection, and check whether the COUNTA value for all the cells in that column is zero or not. In case the COUNTA function value is 0, it means that the column is empty and the VBA macro code deletes that column. And in case the value of the COUNTA function is more than 0, it means that the column is not empty and it is not removed. How to Use the above VBA Macro Code? Below are the steps to use the above VBA code to delete empty columns in Excel: The above steps would instantly run the code which would remove all the empty columns from the selected data set. Caution: The changes done by the VBA macro code in your worksheet cannot be undone. So it’s always a good idea to create a backup copy of your original data before using the VBA code to delete empty columns
Delete Blank Columns Using Go-To Special
One final method that I want to show you to delete empty columns in Excel is by using the Go To Special dialog box. While this method is the fastest among all the methods covered in this tutorial so far, you need to be extremely cautious when using this method (especially if you’re working with large datasets). This is because it can be error-prone and can lead to deleting columns that are not completely blank. So while I’m covering this method in this tutorial, I would not recommend you use this method, instead use the other methods covered in this tutorial. If you decide to use this method , you should be absolutely sure that your data set has no blank cells in the columns that are not completely blank Let’s see how this method works. Below I have a data set where I have some columns that are empty and I want to remove these columns. Here are the steps to do this using go to special dialog box: The above steps would delete all the columns where there are blank cells The Drawback of this method? One big drawback of this method is that in case you have a couple of blank cells in otherwise filled columns, these would also be selected and these non-blank columns would also be deleted. Below is an example where I have a couple of blank cells in the Printer and Scanner columns, and these were also selected when I use the ‘Go To Special’ dialog box. Now if I go ahead with the above ‘Go To Special’ method and delete the empty columns, even the Printer and the Scanner columns would be deleted, which is not what I want. This is why it is best to avoid this method to delete empty columns as it could also end up deleting some columns just because they had a few blank cells in them. So these are some of the methods you can use to delete blank columns in Excel. If you have a small data set and you only have a couple of blank columns, it is better to delete them manually. And in case you have a large data set, you can either use the VBA method (which is fast and efficient), or use the helper row along with the sort feature or the find and replace feature to quickly select blank columns and delete them. Note: The methods I have covered in this tutorial can also be used to delete blank rows in Excel. You would have to adjust the methods and the VBA code accordingly Other Excel tutorials you may also like:
Delete Blank Rows in Excel (with and without VBA) Fill Down Blank Cells Until the Next Value in Excel (3 Easy Ways) Insert a Blank Row after Every Row in Excel (or Every Nth Row) How to Highlight Blank Cells in Excel (in less than 10 seconds) How to Replace Blank Cells with Zeros in Excel Pivot Tables
