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 of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.