Deleting blank rows in Excel

Working with spreadsheets mean inevitably to face with importing data sooner or later. And blank rows or cells are a common problem, especially if we are going to use the filter to order the items in your list, which definitely don't love empty cell/rows (well the Windows version of Excel 2010 seems to manage pretty well them, but this is not happening with Excel 2011 for Mac).

For what sake should I add an empty row if this creates me trouble?

Well, you are probably not adding a blank row if you know this causes problem, but it is likely that before working on the spreadsheet you have used the import tool to get raw data from somewhere else (like a text file or a CSV file). So, today I will show you a very simple trick to delete blank rows from excel spreadsheets. The method applies to both Mac and Windows, but as I don't work on a Windows regularly I'm not entirely sure the Windows version does require some additional steps.

How to delete blank rows

Here the solution, proposed in 5 easy steps.

  1. Press F5
  2. Hit on the “select” on the the “Go to” dialog just appeared
  3. From “select special” screen, select "Blanks" and press Ok
  4. With all the blank cells currently selected, press CTRL and Minus sign (-)
  5. Select “shift cells up” or “entire row” as needed.

That is all. Now you have successfully removed blank rows.

 6 Comments

 Started by Salvis  31 August 2012

ok, but How to remove FULL rows?!
I received a .csv so large, that I can't import in mysql it, and don't know how to split correctly or delete half....stuck with csv

Reply

 Started by Alex  07 October 2013

Hi. Same question - I don't understand your response. How do I delete only full blank rows, not rows with blank cells within the row? What is the row header? I have column headers, not row headers. At what stage of the process do I then select this and press control minus?

Also, F5 does not bring up a dialog box for me.

Thanks.

Reply

 Started by Mary  25 March 2014

Well it works, but lets say you imported data about how many items of something have beend sold. Then, when on eitem wasnt sold (singnaled by empty cell), you would screw up the whole thing by deleting empty cells. I guess you should use an IF function (<a title="http://www.excel-aid.com/the-excel-if-function.html" href="http://www.excel-aid.com/the-excel-if-function.html">http://www.excel-aid.com/the-excel-if-function.html</a> for decription on how to use it), that returns or marks empty cells so you can insert "0" or something? Im sorry, i just started learning excel.

Reply

 Leave a Comment