How can I delete all empty rows in Excel automatically

The skill of working with the Excel program is definitely useful for representatives of many professions. In this article, we will consider a number of issues – in particular, why removing rows in Excel using the “select empty cells -> delete row” scheme is not the best idea. We will also analyze 3 Fast and Reliable Methods to Remove Blank Lines thus, so that it does not damage the information in other cells in any way. All solutions apply to Excel 2019, 2016, 2013 and earlier.

3 Fast and Reliable Methods to Remove Blank Lines 

Since you are currently viewing this article, it is logical to assume that you, like many others, have to regularly deal with excel spreadsheets of substantial size. Chances are you’ve come across situations where blank lines, preventing most of the built-in tools from correctly recognizing the range of data. Thus, each time, you have to specify the boundaries manually – otherwise you will get the wrong result, and it will take you more than one hour of time to detect and correct such errors. 

There can be various reasons why blank lines appear – for example, you got an Excel file from someone else, or the file was exported from some database, or you accidentally erased information in unwanted lines. Under any circumstances, if you require remove blank lines and as a result to have a clean and beautiful table, you need to execute a series of simple steps. Let’s take a look at a few specific situations on the following topics:

• Why you should not remove empty rows by selecting empty cells.
• How to eliminate all empty rows when there is a key column.
• How to eliminate all empty rows when there is no key column.
• How the Delete Empty Lines tool is used, and why it is the most convenient, and most importantly, the fastest method.

Don’t remove empty rows by selecting empty cells  

On the Internet, you can often find the following advice:

• Select all cells containing information, from 1st to last.
• Press the key F5 – As a result, the dialog box “The transition».
• Inside the window that opens, click “Highlight».
• In the window “Selecting a group of cells» select option «empty cells“, then “OK».
• Right-click on any selected cell, then select “Delete…».
• Inside the window that opensDeleting cells» click on the option «string». 

Unfortunately, this is not the best method – use it only for small tables where rows are displayed within the screen, or even better – don’t use it at all.

This is due to the fact that if there is only one empty cell in the line containing important information, the entire line is deleted. 

Let’s look at an example. Before us is a table of customers, in it only 6 term. We want delete third and fifth lines, since they are empty.

Use the above method and you will get the following:

Line 4 (Rojer) is also missing, because in the “Traffic source” column  cell D4 is empty.

Since you have a small table, you will be able to find lack of data, but in large tables that contain thousands of rows, you can delete dozens of necessary rows without even knowing it. If you’re lucky you’ll notice it’s missing in a few hours, restore your file from backupand then do it again. But what if you’re out of luck or don’t have a backup? 

Let’s take a look 3 Fast and Reliable Ways to Remove Blank Lines from your Excel spreadsheets. And if you want to save your time – go directly to 3rd method.

Eliminate all empty rows when there is a key column

The method works provided you have a column to help you figure out if a string is empty or not (so-called key column). For example, this could be an order number, or a customer ID, or something similar.

We need to leave string sequence unchanged, therefore, simply sorting by this column and shifting all empty rows to the end of the table will not work. So what need to do.

1. Select the table completely, from the 1st to the last row (to do this, you can simultaneously hold down Ctrl + Home, Further – Ctrl + Shift + End).

2. Set Autofilter: go to the tab “Data” and click on the button “Filter».

3. Next, you need to apply a filter to the “Cust #” (“Customer No.”) column: click on the drop-down arrow “Autofilter” in the column name, uncheck (select all), scroll down to the end (actually the list is quite long), then check the box “Empty”… Click OK.

4. Combine all filtered rows: for this you can hold down at the same time Ctrl + Home, then the down arrow button to return to the first line again, then hold Ctrl + Shift + End.

5. Right-click on any selected cell and click on “Delete line» or just press Ctrl + – (minus sign).

6. Click on the button OK when answering the questionDelete the entire term of the letter?»

7. Then you need to clear the applied filter: to do this, go to the “Data” and click on the button “Clean».

8. Good work! All empty lines are gone, and the third line (Rojer) is still there (for comparison, you can refer to the previous version).

Eliminate all empty rows when there is no key column    

Use this wayif you have a table in your work with a large number of empty cells placed in various columns, and you want to delete exactly those rows that absolutely empty.

key column, which could be used to find out if the string is empty or not, is missing in our example. What to do? We ourselves create an extra column:

1. Create column “Blanks” (“Empty cells”) at the very end of your table, then write in the 1st cell of this column the formula: = COUNTBLANK (A2: C2).

This formula counts empty cells in the prescribed range, where A2 is the 1st cell, C2 is the last cell.

2. Copy the formula for all cells in the column.

3. Now we have key column. Then use filter in the “Blanks” column (detailed instructions above) to display the rows with the maximum value (3). “3” means the following: all cells in the row are empty.

4. Then select all filtered rows and remove completely emptyusing the instructions previously described.

Thus the empty line (line 5) removed, and the lines with the necessary information remain in place.

5. Next, delete additional column. It won’t be needed anymore. Or you can put another filter and display rows where there is one or more empty cells.

To do this, uncheck the0“, then click “OK».

Using the Remove Empty Lines tool is the most convenient and fastest method  

The fastest and most flawless method to remove blank lines is a toolRemove blank lines”, included in the kit Ultimate Suite for Excel.

Among other useful functions it contains several utilities, which allow one click to move columns by dragging; delete all empty cells, rows and columns, as well as filter by the selected value, calculate percentages, apply any basic mathematical operation to a range, copy cell addresses to the clipboard and much more.

How to remove blank lines in 4 easy steps

Using Ultimate Suite, additionally installed to the Excel program, that’s what you need do:

1. Click on any cell in the table.
2. Click the tab Tools Ablebits > Transform Group.
3. Press Remove blank lines > Blank lines.

4. Нажмите кнопку OKto confirm that you really want remove empty lines.

That’s all! Just a few clicks and you will get clean table, all empty lines are gone, and the order of the lines is not distorted!