Delete Blank Columns

Learn Excel from MrExcel Podcast, Episode 2171: Delete the Blank Columns.

Well, I was doing a seminar in Green Bay and this horrible dataset someone had, and they said they wanted to delete all the blank columns. And check this out, this goes-- if I press End+Home-- this goes all the way out to-- let's see, equal column-- 730 columns of data! Must be that you had the whole year with a blank column between each one-- so 365 blank columns.

And of course, you know, I tried this last Thursday, you could delete the first one-- so Alt+E, D, Entire Column, OK, and then it's just a simple Right Arrow, press F4 to repeat, Right Arrow, F4. I just do that over and over and over again, but there's too many columns it's going to be horrible.

And Carmela, who is in the audience in Green Bay, said, "I want to know if there's a fast way to do that." Just choose a whole row and make sure to choose a row that's going to be filled in all the way; not a row that's completely blank, or has some blanks, alright? Choose the the row that's going to be completely filled in, Home, Find & Select, Go to Special, select the "Blanks", and that will very quickly select all of the blank columns. And then under Home, Delete, Delete cells, and we're going to say Delete the Entire Column, and BAM! We end up with a nice contiguous block of data. At least, the columns are gone.

Now, once you have the columns gone, then it's easy enough to select-- to get the rows. The fastest way to delete the rows is not to delete them at all-- it's just simply to Sort. So we select all of that data and then Data, A to Z, and all of the blanks will sort to the bottom. So, easier to get rid of blank rows than blank columns.

Although, there is a third method: Ctrl+Shift+N, to select that data and then we're going to go into the Sort dialog box. Most of the time we're sorting by rows, but there's a secret choice here under Options. We're going to Sort Left or Right, and we're going to base it on Row 1. Now, those are actual dates up there, so they will sort correctly. Data gets sorted, let's do Format, Column, Auto Fit Selection, to make it work.

617 tricks like that trick in this book, Power Excel with MrExcel. Click the "I" in the top right-hand corner for more information.

Alright, so today's problem: How to delete hundreds of tiny blank columns in Excel. The first method, which is going to take way too long-- delete the first column then use F4, Right Arrow, F4, Right Arrow, F4, Right Arrow, F4 being the command to repeat; or from Carmela in Greenbay, select a row, go to Special Blanks, and then delete the columns or Sort Left or Right.

Well, hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.