Excel - How to Quickly Delete All Blank Columns in Excel - Episode 2171

Microsoft Excel Tutorial: How to delete hundreds of tiny blank columns in Excel
Welcome to another episode of the MrExcel Podcast, where we share useful Excel tips and tricks to help you become an Excel pro. In today's episode, we will be discussing a common problem faced by many Excel users - how to quickly delete all blank columns in Excel. This can be a tedious and time-consuming task, especially if you have a large dataset with hundreds of blank columns. But fear not, we have some handy Excel hacks that will make this task a breeze.
During a recent seminar in Green Bay, I came across a dataset with a whopping 730 columns of data, with 365 blank columns in between. As you can imagine, manually deleting each column using the Alt+E, D, Entire Column shortcut and then pressing F4 to repeat the action would have been a nightmare. But then, a member of the audience, Carmela, asked if there was a faster way to delete all the blank columns. And that's when I discovered this amazing trick.
Instead of manually deleting each column, simply select a row that is completely filled in and go to Home ＞ Find & Select ＞ Go to Special. Then, select "Blanks" and all the blank columns will be highlighted. Now, just go to Home ＞ Delete ＞ Delete Cells and choose "Delete Entire Column". Voila! All the blank columns will be deleted in one go, leaving you with a nice, contiguous block of data.
But wait, there's more! Did you know that you can also use the Sort function to quickly get rid of blank rows? Just select all the data and go to Data ＞ A to Z. This will sort all the blank rows to the bottom, making it easier for you to delete them. And if you want to take it a step further, you can use the Sort Left or Right function by selecting all the data and going to Data ＞ Sort. This will allow you to sort the data based on a specific row, making it perfect for sorting dates or other specific data.
And there you have it, three different methods to quickly delete all blank columns in Excel. But that's not all, there are 617 more tricks like this in my book, "Power Excel with MrExcel". So make sure to click the "I" in the top right-hand corner for more information. Thank you for tuning in, and we'll see you next time for another netcast from MrExcel. Don't forget to like, share, and subscribe for more Excel hacks. Happy Excel-ing!
Buy Bill Jelen's latest Excel book: www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: www.mrexcel.com/like-mrexcel-...
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
Join the MrExcel Message Board discussion about this video at www.mrexcel.com/board/threads...
Method 1: Delete first column and then use F4 a lot
Method 2 from Carmella in Green Bay: Select a row, Go To Special Blanks, then Delete columns
Method 3: Sort Left to Right in Excel
This video answers these common search terms:
remove blank columns on excel
get rid of blank columns in excel
how to sort blank columns in excel
how to clean blank column in excel
how to delete blank column excel
how to delete blank columns excel
how to delete blank columns in excel
how to remove blank columns in excel
how to remove blank columns in excel
how to delete blank columns in excel sheet
how to delete blank columns in excel shortcut
how to remove a blank column in excel
how do i remove blank columns in excel
how to delete all blank columns in excel
how to remove all blank columns in excel
how to delete extra blank columns in excel
how to dlete blank unused columns in excel
excel how to delete blank columns
excel how to remove blank columns
how to delete a lot of blank column excel
how to delete multiple blank columns in excel
how to eliminate blank rows and columns in excel spreadsheet
#excel
#microsoft
#exceltutorial
#exceltips
#microsoftexcel
#exceltricks
Table of Contents
(0:00) Problem Statment: Remove hundreds of blank columns in Excel
(0:33) Method 1: Delete one column and then F4 to repeat
(0:56) Method 2: Goto Special Blanks and Delete Entire Column
(1:30) To delete Rows, Sort the Data
(1:55) Method 3: Sort Left to Right to move blank columns to end
(2:05) Buy the book
(2:15) Recap from today
(2:40) Clicking Like really helps the algorithm

[@64mustangfan]

Thanks! Even old time Excel users like myself miss some of the very useful shortcuts, especially if you never needed them before! 👍

[@excelisfun]

Love the Sort to Delete : )

[@DougHExcel]

Thanks for the sorting tips!

[@anoopvasudevan509]

Wonderful, thank you.

[@semperdiscendum7439]

Awesome! Thank you! 👍👍

[@alirezamogharabi8733]

Thanks a lot Bill, please make a video about custom table formatting or modifying existing formats. thanks.

[@lynnmichalski2729]

Saved me so much time!

[@ismailismaili0071]

It's just OMG thank u Mr. Bill love u

[@palakodetibangarurayudu9686]

Thank you

[@scottwilkerson1208]

I've got about thousand columns to the right of my data. Anyway to get rid of those? Your methods above do not work.

[@Videokeys]

how to delete blank columns if its in table format? this only applies if the cells aren't formatted table; please help, thanks in advance!

[@MrXL]

Thanks for posting this comment.
Before this comment, I hated Tables almost as much as I hate merged cells.
I never realized that "Format as Table" turns off Left to Right sorting (from 2:13) in the video and also turns off Delete Columns.
In the lead-up to Office 2007, the Excel team realized that Tables could not be finished in time and wanted to rip them out of the product. But it was too late, there wasn't time to remove all the code. Office 2007 shipped with Tables in their broken state. Then a reorganization sent the Tables team to other departments and now, 16 years later, we are still left with this travesty of a feature.
My only advice is to select a cell in the Table. Go to Table Design tab in the Ribbon. The 3rd command in the second column of icons is "Convert to Range". Use this to convert to a regular range. Use one of the two methods in this video. Then, if you need to, make this into a table again.

[@kiranraika4540]

You talk a lot and but your work is excellent
It would be very nice if you finished your video in 1min instead of 5min ❤ see from the audience perspective as well

[@ashaydwivedi420]

Omg 8th view 1st comment 1st like

[@vfran838]

This is great but goes too fast.

[@krn14242]

WOW... Can't even call that a data set. Mike Girvin is cringing I'm sure. Great trick to delete all columns though.

[@MrsDotWhit]

Way too fast. Sorry

[@solutionsaec9129]

Your speaking too fast for a real novice to follow

[@amirfadhly6608]

True, I dom't like that.