Never remove blank rows by selecting blank cells
All over the Internet you can see the following tip to remove blank lines:
- Highlight your data from the 1st to the last cell.
- Press F5 to bring the "Go to" dialog.
- In the dialog box click the Special… button.
- In the "Go to special" dialog, choose "Blanks" radio button and click OK.
- Right-click on any selected cell and select "Delete…".
- In the "Delete" dialog box, choose "Entire row" and click Entire row.
This is a very bad way, use it only for simple tables with a couple of dozens of rows that fit within one screen, or better yet - do not use it at all. The main reason is that if a row with important data contains just one blank cell, the entire row will be deleted.
For example, we have a table of customers, 6 rows altogether. We want to remove rows 3 and 5 because they are empty.
Do as suggested above and you get the following:
Row 4 (Roger) is also gone because cell D4 in the "Traffic source" column is empty: (
If you have a small table, you will notice a loss of data, but in real tables with thousands of rows you can unconsciously delete dozens of good rows. If you are lucky, you will discover the loss in a few hours, restore your workbook from a backup, and will do the job again. What if you are not so lucky or you do not have a backup copy?
Further in this article I will show you 3 fast and reliable ways to remove empty rows from your Excel worksheets. If you want to save your time - go straight to the 3rd way.