Microsoft Excel

Delete Blank Columns in Excel: 3 Easy Ways

Blank columns break sorting, filtering, and PivotTables. Remove them with Go To Special, a COUNTA helper row, or Power Query's Column quality view.

Blank columns and blank rows in your Excel data may look harmless, but they cause real problems. I see this all the time when people send me Excel files or when I download data from another system. I am going to pick on blank columns here, but everything applies to blank rows too.

Below are three ways to get rid of them: Go To Special when the blank columns have no header, a COUNTA helper row when the empty columns do have a header, and Power Query when you keep getting the same messy data over and over.

Why blank columns break your data

Here is my sample order data. Columns C, F, and J are completely empty. They split what should be one data set into four separate blocks.

Excel order data with empty columns C, F, and J outlined in red, splitting the data into separate blocks — Columns C, F, and J are empty. To Excel, this is no longer one data set.

Watch what happens when I sort. I click in column A, go to Sort, and sort the Order IDs Z to A, largest to smallest. Columns A and B sort. Columns D and E, G through I, and K and L do not. Excel stops at the first blank column, so the customers, products, and totals are no longer lined up with the right order IDs.

Excel worksheet after a Z to A sort where the Order ID column is reversed but the customer, product, and total columns stayed in their original order — After sorting Z to A, Order ID starts at ORD-1020, but the rest of the row data did not move with it.

Sorting is not the only thing that gets messed up. Filtering and PivotTables also treat each block as separate data. It is just a disaster, so let's fix it.

Method 1: Delete empty columns with Go To Special

This first method assumes that if a column has no header, there is no data below it. In my example that is true: C1, F1, and J1 are empty.

Select the header row from A1 over to L1. Then press Ctrl + G on the keyboard to bring up the Go To dialog box.

Excel header row A1 to L1 selected with a Ctrl G Go To keyboard callout — Select the header row, then press Ctrl + G to open Go To.

Click the Special button at the bottom of Go To. In Go To Special, select Blanks and click OK. If you have used Go To Special to fix blanks before, this is the same dialog box.

Excel Go To Special dialog box with the Blanks option selected and the mouse pointer on OK — In Go To Special, choose Blanks. Excel selects only the empty cells inside the header row.

Now only C1, F1, and J1 are selected. Go to the Home tab, click the Delete dropdown in the Cells group, and choose Delete Sheet Columns.

Excel Home tab Delete dropdown open with Delete Sheet Columns highlighted while the blank header cells in C, F, and J are selected — Home > Delete > Delete Sheet Columns removes all three blank columns at once.

All three columns are gone, and the data is one continuous set. Let's test it. I right-click in A2, choose Sort, and sort Z to A again. This time every column sorts together. Granite Partners, which was on the first row, now sits at the bottom with its correct order. Press Ctrl + Z to undo the sort if you want the original order back.

Excel order data with no blank columns, sorted Z to A by Order ID, with every column moving together — With the blank columns removed, a Z to A sort keeps every row intact.

Method 2: Check columns with headers using COUNTA

The second example is trickier. Here, the empty columns do have headers. C1, F1, and J1 all say Notes, but nothing was ever typed below them. I have seen data like this a lot, and the header may not be called Notes. Because the header cell is filled in, Go To Special on the header row will not find these columns.

Excel data with three Notes headers in columns C, F, and J outlined in red and no data below them — Each Notes column has a header but no data. Go To Special on the header row will skip these.

The fix is a helper row. Insert a row above the headers, and in A1 use the COUNTA function to count everything in that column, from A2 down to the last row of data:

=COUNTA(A2:A22)

Column A returns 21: the header plus 20 rows of data. Copy the formula across the helper row. COUNTA counts every cell that is not empty, so a column that only has a header returns 1.

Excel helper row with COUNTA results showing 21 for most columns and 1 for the three Notes columns C, F, and J — COUNTA in the helper row. The columns that return 1 have a header and nothing else.

Every column showing a 1 is one you can delete. Here that is C, F, and J. Select those columns, delete them, and then delete the helper row.

Method 3: Load your data into Power Query

If you keep getting data with blank columns, this is the method I would go with. Back to my original data with the empty columns in C, F, and J.

First, select all the data. Click A1, then press Ctrl + Shift + End. The End key is on the keyboard below Home. That jumps to the last cell of your data and selects everything in between, including the blank columns.

Excel order data selected from A1 to L21 with a Ctrl Shift End Select all data keyboard callout — Click A1 and press Ctrl + Shift + End to select everything, blank columns included.

Go to the Data tab and click From Table/Range. The Create Table dialog box opens. Excel now lets you name your table when you create it, so if you have that option, use it. I named mine tbl_orders. Make sure My table has headers is checked, and click OK.

Excel Create Table dialog box with the range A1 to L21, the table name tbl_orders typed in, and My table has headers checked — Name the table in the Create Table dialog box, confirm it has headers, and click OK.

Excel launches the Power Query Editor. You can see the empty columns there, renamed Column1, Column2, and Column3, but on real data you do not want to sit and scroll across looking for them.

Find empty columns with Column quality

In the Power Query Editor, go to the View tab. In the Data Preview group, check Column quality.

Power Query Editor View tab with Column quality checked, showing Valid, Error, and Empty percentages under each column header — View > Column quality adds Valid, Error, and Empty percentages under every column header.

Now every column shows Valid, Error, and Empty percentages right under the header. Order ID is 0% empty. Order Date is 0% empty. Column1 is 100% empty. Hover over the bar and you get the exact count: 20 out of 20 rows are empty.

Power Query Column1 showing 100 percent empty with a hover card reading 0 valid, 0 error, 20 empty — Column1 is 100% empty. The hover card confirms all 20 rows are blank.

Column2 is also 100% empty, and if you scroll a little to the right, so is Column3. Compare that with the Region and Status columns, which are only 10% empty. Those have a couple of missing values, but they are real data and should stay.

Power Query Editor with Column quality on, showing Column2 and Column3 at 100 percent empty while Region and Status are 10 percent empty — Scan the Empty row for 100%. Column2 and Column3 are completely blank; Region and Status are not.

Now delete the columns that are completely empty. Hold Ctrl, click the Column1, Column2, and Column3 headers, then right-click and choose Remove Columns. When you are done, click Close & Load on the Home tab. The next time you get new data, refresh the query instead of cleaning it by hand again.

Which method should you use?

If the blank columns have no header, Go To Special on the header row is the fastest. If the empty columns have headers, a COUNTA helper row tells you exactly which ones are safe to delete. And if this is data you receive regularly, Power Query with Column quality turned on shows you every empty column at a glance, and the clean-up is saved for next time.

If you have another method for removing blank columns that I did not cover, let me know. I am happy to look into it.