Archive for July 16th, 2013

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.

Continue reading...