MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Delete Blank Columns

November 09, 2017 - by Bill Jelen

Delete Blank Columns

You have a data set with 100 columns of data separated by 100 blank columns. How can you quickly delete all of the blank columns?

Watch Video

  • How to delete hundreds of tiny blank columns in Excel
  • 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

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2171 delete the blank columns
  • why was doing somehow 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 my press end home this goes all the
  • way out to let C equal column 730
  • columns of data must be that yeah 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 okay 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 gonna be horrible
  • and Carmela who is in the audience in
  • Green Bay say oh no there's a fast way
  • to do that just choose a whole row and
  • make sure to choose a row that's gonna
  • be filled in all the way not a row
  • that's completely blank or has some
  • blanks right choose the the row that's
  • gonna be completely filled in home find
  • and 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 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
  • control-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 gonna
  • sort left or right and we're I'm base it
  • on Row 1 now those are actual dates up
  • there so they will sort correctly data
  • get sorted let's do format column auto
  • fit selection
  • to make it work 617 tricks like that
  • trick in this book power excel with mr.
  • excel 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 gonna 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 karma and Greenbay select
  • a row go to special blanks and then
  • delete the columns or sort left or right
  • oh hey I want to thank you for stopping
  • by we'll see you next time for another
  • net cast from MrExcel

Download File

Download the sample file here: Podcast2171.xlsm

Title Photo: Alessio Lin / Unsplash

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.