Excel Tip: Remove All blanks from a Excel table July 16th, 2013

Vinod Kumar

This is one of the simplest tips among those I have shared over this blog till date. Let me explain you the scenario. We have the following table / range / data set inside Excel and the problem with these dataset is that, we have spaces in between and this is causing us problems for filter, moving between cells and many more. So our objective is to remove the rows having space. I have seen people use “CTRL+Down Arrow” to goto a row with space, delete that row – press CRTL+Down Arrow and repeat this process till you reach the last row.

This for sure is manual and intensive repetitive activity. In this blog post we will do the same operation efficiently in few clicks. Quite an handy option to know.

When you are at in this screen, Select the range on “B column” just like shown below. And now hit the “CTRL+G”. This bring the dialog as below. Select “Special…

In the following dialog select “Blanks” and press “OK”.

Voila, now you can see that from our dataset the Blank rows are highlighted as shown below.

Just right Click and select “Delete” –> “Table Rows”. Now that is as simple as it gets.

We have successfully deleted all the rows with blanks and our final dataset looks like:

Excel is an awesome tool and there are many such functions that make it interesting. Hope you will use this shortcut in your day-today work and hope you found this trick useful. Do let me know if you want me to write on anything specific and I would love to explore and show you few tricks.

Tags: , , , , , , , , , ,

This entry was posted on Tuesday, July 16th, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


6 Responses to “Excel Tip: Remove All blanks from a Excel table”

  1. Beena says:

    Learning of the day for me. Thanks Vinod.

    I struggle with VLookUp and Pivot table, could you please write 2 examples for each, one simple, one complex query.

    I really need help to understand this.

  2. avinash reddy says:

    wow another superb tip done by you am very glad to learn something new for today thank u so much vinod sir

  3. Joseph Flesche says:

    When I come across this scenario, I typically apply a filter (ctrl + shift + L), then filter the specified field for blanks then deleted the rows. But I typically have my Excel sheets have the header row in row 1.

    This method will work for any selected data set. I will certainly use this in the future!

    Thanks for sharing!

Leave a Reply