How to remove blank rows in Excel? Let’s look at some examples for simple and complicated tables.

Simple Table

Let’s start with the following table.

The table contains clean empty rows which we would like to remove.

We select these empty cells by using Home > Find & Select > Go To > Special.

A Go To Special menu opens up where we select Blanks.

Confirm with OK.

We now delete the selected empty rows with Delete and –.

Or, we can use Home > Delete > Delete Table Rows.

By using filters

Another way is to use filters. We select the filter on any column, for example column Date.

We then select only Blanks.

We get the filtered blank rows.

We delete the rows by selecting Home > Delete > Delete Table Rows.

In older versions of Excel this may not work. You first need to select visible cells: select Home > Find & Select > Go To > Special.

Select Visible Cells only and confirm with OK.

Then delete rows same as before, using Ctrl and –.

Complicated Table

Let’s look at how to remove blank rows in a complicated table. We now also have some individual blank cells, not just entire blank rows.

Let’s again use Home > Find & Select > Go To > Special. We get all the blank cells selected.

The problem is, if we now delete the selected cells, we lose all rows, except for the first one (which doesn’t contain any blanks).

This is why we’ll use a different approach here.

Solution 1: Create a New Column

One way is to concatenate all the cells into a single value. We do this by using the following formula.

= TEXTJOIN(»«, TRUE, EverydayTable[[@ID]:[Value]])

The new column looks like this.

We can now filter the new column to blanks. Select filter and only check (Blanks).

We’re left with two rows.

We delete the selected rows by using Ctrl and –.

Solution 2: Power Query

Power Query has a built-in command for removing blank rows, which makes it really simple.

Select Home > Remove Rows > Remove Blank Rows.

That’s it! Power Query is the clear winner here, wouldn’t you agree?

Watch the tutorial

Meanwhile, you can also watch the tutorial online on our YouTube channel!

Please leave us a like, comment, and subscribe for more amazing Excel tricks!

Follow us on LinkedIn.

Check out R Academy!