How to Delete All Blank Rows in Excel

In this article, I will explain why removing blank rows in Excel using highlight empty cells > delete line is a bad idea, and I’ll show you 2 quick and correct ways to remove blank lines without destroying the data. All of these methods work in Excel 2013, 2010, and older versions.

If you are reading this article, then most likely you are constantly working in Excel with large tables. You know that blank rows periodically appear among the data, limiting the work of most Excel table tools (sorting, removing duplicates, subtotals, and so on), preventing them from correctly determining the range of data. And each time you have to manually define the boundaries, otherwise the result will be an incorrect result and a lot of time spent on correcting errors.

There are many reasons why blank lines appear. For example, you received an Excel workbook from another person or as a result of an export from a corporate database, or unnecessary data in rows was manually deleted. In any case, if your goal is to remove all those blank lines and have a clean and tidy table, then follow the simple steps below:

• Never delete blank rows with blank cell selection
• Removing empty rows using a key column
• Removing empty rows in a table without a key column

Never delete blank rows with blank cell selection

All over the internet, you’ll find a simple tip that supposedly allows you to remove blank lines:

• Select the data from the first to the last cell.
• Press F5to open the dialog Go to (Transition).
• In the dialog box, click the button Special (Highlight).
• In the dialog box Go to special (Select a group of cells) check the box blanks (Empty cells) and click OK.
• Right-click on any of the selected cells and press Delete (Delete).
• In the dialog box Delete (Delete cells) select Entire row (line) and press OK.

This is a very bad way., only do this with very simple tables with a couple of dozen rows that fit on one screen, or even better – don’t do that at all! The main reason is that if a line with important data contains at least one empty cell, then the whole line will be deleted.

For example, we have a customer table with 6 rows in total. We want to remove lines 3 и 5because they are empty.

Do as suggested above and get the following result:

Line 4 (Roger) also disappeared because the cell D4 in a column Traffic source turned out to be empty

If your table is not large you will notice data loss, but in real tables with thousands of rows you may unknowingly delete dozens of necessary rows. If you’re lucky, you’ll find the loss within a few hours, restore the workbook from a backup, and continue working. What if you’re unlucky and you don’t have a backup?

Later in this article, I will show you 2 quick and reliable ways to remove blank rows from Excel sheets.

Removing empty rows using a key column

This method works if your table has a column that helps determine if the column in question is empty or not (the key column). For example, it could be a customer ID or an order number, or something similar.

It’s important for us to preserve the order of the rows, so we can’t just sort the table by that column to move all empty rows down.

1. Select the entire table, from the first to the last row (press Ctrl + Home, and then Ctrl + Shift + End).
2. Add an autofilter to the table. To do this, on the tab Data (data) click Filter (Filter).
3. Apply a filter to a column Cust#. To do this, click the arrow button in the column heading, uncheck the option Select All (Select All), scroll down to the end of the list (in practice, this list can be quite long) and check the box blanks (Empty) at the very bottom of the list. Click OK.
4. Select all filtered rows: click Ctrl + Home, then the down arrow to move to the first row of data, and then press Ctrl + Shift + End.
5. Right-click on any selected cell and from the context menu select Delete row (Delete line) or just click Ctrl + –(minus sign).
6. In the window that appears with a question Delete entire sheet row? (Delete entire sheet row?) click OK.
7. Clear the applied filter: on the tab Data (data) click Clear (Clear).
8. Excellent! All empty lines are completely removed, and the line 3 (Roger) is still in place (compare with the result of the previous attempt).

Removing empty rows in a table without a key column

Use this method if your table has numerous empty cells scattered across different columns, and you need to delete only those rows that do not have any cells with data.

In this case, we don’t have a key column to help determine if the string is empty or not. Therefore, we add an auxiliary column to the table:

1. At the end of the table, add a column named blanks and paste the following formula in the first cell of the column:

   =COUNTBLANK(A2:C2)

   =СЧИТАТЬПУСТОТЫ(A2:C2)

   This formula, as its name suggests, counts empty cells in a given range. A2 и C2 are the first and last cells of the current row, respectively.

   

2. Copy the formula to the entire column. How to do this – see the step-by-step instruction How to insert the same formula into all selected cells at once.
3. Now our table has a key column! Apply a filter to a column blanks (above is a step by step guide on how to do this) to only show the rows with the maximum value (3). Number 3 means that all cells in this row are empty.
4. Next, select all the filtered rows and delete them entirely. How to do this is described above. As a result, the empty line (line 5) will be deleted, all other lines (with or without empty cells) will remain in their place.
5. Now the auxiliary column can be removed. Or you can apply another filter to show only those cells that have one or more empty cells. To do this, uncheck the line with the value 0 (zero) and press OK.