Top 9 ways to remove blank rows from Excel data

Top 9 ways to remove blank rows from Excel data

Blank rows of data can be a big annoyance.

They’ll make certain things like navigating around our data much more difficult.

But the good news is there are lots of ways to get rid of these unwanted rows and it can be pretty easy to do it.

In this post, we’re going to take a look at 9 ways to remove blank rows from our Excel data.

EVBA.info

Delete Blank Rows Manually

The first method is the manual way.

Don’t worry, we’ll get to the easier methods after. But if we only have a couple rows then the manual way can be quicker.

Select the blank rows we want to delete. Hold Ctrl key and click on a row to select it.

When the rows we want to delete are selected then we can right click and choose Delete from the menu.

We can also delete rows using a ribbon command. Go to the Home tab ➜ click on the Delete command ➜ then choose Delete Sheet Rows.

There is also a very handy keyboard shortcut to delete rows (columns or cells). Press Ctrl + – on the keyboard.

That’s it! Our blank rows are gone now.

Delete Blank Rows Using Go To Special

Selecting and deleting rows manually is OK if we only have a couple rows to delete.

What if there are many blank rows spread across our data? Manual selection would be a pain!

Don’t worry, there is a command in Excel to select all the blank cells for us.

First, we need to select a column of our data including all the blank rows. The easiest way to do this will be to select the first cell (A1 in this example) then hold the Shift key and select the last cell (A14 in this example).

Now we can use the Go To Special command to select only the blank cells. Go to the Home tab ➜ press the Find & Select command ➜ choose Go To Special from the menu.

There’s also a handy keyboard shortcut for the Go To menu. Press Ctrl + G to open up the Go To menu then click on the Special button to open up the Go To Special menu.

Whether we open up the Go To menu then click Special or we go directly to the Go To Special menu, we will arrive at the same Go To Special menu.

Now all we need to do is select Blanks from the options and press the OK button.  This will select only the blank cells from our initial column selection.

Now we need to delete those selected rows.

1. Use any delete rows method from the Delete Blank Rows Manually section.
   • Right click ➜ Delete
   • Home tab ➜ Delete ➜ Delete Sheet Rows
   • Ctrl + – keyboard shortcut
2. In the Delete menu select Entire row and press the OK button.

Like magic, we can find and delete hundreds of blank rows in our data within a few seconds. This is especially nice when we have a lot of blank rows scattered across a long set of data.

Delete Blank Rows Using Find Command

This method is going to be very similar to the above Delete Blank Rows Using Go To Special method. The only difference is we will select our blank cells using the Find command.

Just like before, we need to select a column in our data.

Go to the Home tab ➜ press the Find & Select command ➜ choose Find from the menu.

There is also a keyboard shortcut we can use to open the Find menu. Press Ctrl + F on the keyboard.

Either way, this will open up the Find & Replace menu for us.

1. Expand the Advanced options in the Find menu.
2. Leave the Find what input box blank.
3. Select the Match entire cell contents option.
4. Search Within the Sheet.
5. Look in the Values.
6. Press the Find All button to return all the blank cells.

This will bring up a list of all the blank cells found in the selected range at the bottom of the Find menu.

We can select them all by pressing Ctrl + A. Then we can close the Find menu by pressing the Close button. Now we can delete all the blank cells like before.

Delete Blank Rows Using Filters

We can also use filters to find blank rows and delete them from our data.

First, we need to add filters to our data.

1. Select the entire range of data including the blank rows.
2. Go to the Data tab.
3. Press the Filter button in the Sort & Filter section.

We can also add filters to a range by using the Ctrl + Shift + L keyboard shortcut.

This will add sort and filter toggles to each of the column headings and we can now use these to filter out the blank.

1. Click on the filter toggle on one of the columns.
2. Use the Select All toggle to de-select all items.
3. Check the Blanks.
4. Press the OK button.

When our data is filtered, the row numbers appear in blue and filtered rows are numbers are missing.

We can now select these blank rows with the blue row numbering and delete them using any of the manual methods.

We can then press the OK button when Excel asks us if we want to Delete entire sheet row.

When we clear the filters, all our data will still be there but without the blank rows!

We can use filters in a slightly different way to get rid of the blank rows. This time we will filter out the blanks. Click on the filter toggle on one of the columns ➜ uncheck the Blanks ➜ press the OK button.

Now all our blank rows are hidden and we can copy and paste our data to a new location without all the blank rows.

Delete Blank Rows Using Advanced Filters

Similar to filter method, we can use the Advanced Filters option to get a copy of our data minus any blank rows.

To use the Advanced Filters feature, we’re going to need to do a bit of setup work.

1. We need to set up a filter criteria range. We are only going to filter based on one column, so we need one column heading from our data (in F1 in this example). Below the column heading we need our criteria (in F2 in this example), we need to enter ="" into this cell as our criteria.
2. Select the range of data to filter.
3. Go to the Data tab.
4. Select Advanced in the Sort & Filter section.

Now we need to configure the Advanced Filter menu.

1. Select Copy to another location.
2. Select the range of data to be filtered. This should already be populated if the range was selected before opening the advanced filters menu.
3. Add the criteria to the Criteria range (F1:F2 in this example).
4. Select where in the sheet to copy the filtered data.
5. Press the OK button.

We now get a copy of our data in its new location without the blanks.

Delete Blank Rows Using The Filter Function

If we are using Excel online or Excel for Office 365, then we can use one of the new dynamic array functions to filter out our blank rows.

In fact, there is a dynamic array FILTER function we can use.

FILTER Function Syntax

= FILTER ( Array, Include, [Empty] )

• Array is the range of data to filter.
• Include is a logical expression indicating what to include in the filtered results.
• Empty is the results to display if no results based on the Include argument are found.

FILTER Function To Filter Blanks

= FILTER ( CarData, CarData[Make]<>"" )

The above function needs to be entered in only one cell and the results will spill into the remaining cells as needed. The function will filter the CarData on the Make column and filter out any blanks.

It’s easy and the great part is it’s dynamic. Because our data is in an Excel table, when we add new data into the CarData table, it will appear in our filtered results.

Delete Blank Rows By Sorting

In addition to all the filtering techniques, we can sort our data to get all the blank rows.

1. Select the range of data.
2. Go to the Data tab.
3. Press the sort command. Either the ascending or descending order will work.

Now all our blank rows will appear at the bottom and we can ignore them.

If we need the original sort order of our data, we can add an index column before sorting. Then we can sort to get the blank rows at the bottom and delete them. Then we sort our data back to the original order based on the index column.

Delete Blank Rows Using Power Query

Power can easily remove blank rows in our data.

This is great is we keep getting updated data with blanks in it and need to include this in our data preparation steps.

Once our data is inside the power query editor, we can easily remove our blank data. Notice, these appear as null values inside the editor.

1. Go to the Home tab in the power query editor.
2. Press the Remove Rows button.
3. Select the Remove Blank Rows option from the menu.

= Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))

This will generate the above M code using the Table.SelectRows function to select the non-null rows. This will only remove rows where the entire record has null values.

We could also get the same result by filtering out the null values in our data. Right click on any of the sort and filter toggles then uncheck the null value and press the OK button.

= Table.SelectRows(#"Changed Type", each ([Model] <> null))

Power query will again generate a step with the Table.SelectRows function, returning non-null values in a specific column.

Delete Blank Rows Using Power Automate

This one might not be as quick, easy and practical as the other methods but it can be done.

We can use Power Automate to delete blank rows in our Excel tables.

In order to do this with Power Automate, we will need to have our data in an Excel table and it will need an ID column that uniquely identifies each row.

We can set up a small Flow automation to do this.

1. We will use a manual button to trigger our flow, but we could use any number of triggers.
2. We then need to List rows present in a table to get all the rows of data from our Excel table. The best option would also be to use the Odata filters in the Show advanced options section to filter on the blank rows, but this is currently not possible to filter on blanks.
3. Because we can’t filter on the blank values with Odata filters, we need to use a Filter array data operation action to do this. We can filter on the values from the List rows present in a table action and set the condition as Make is equal to blank (leave the value empty). This will get us all the rows with blank cells.
4. We can now use the Delete a row action to delete these blank rows. We can select our ID column as the Key Column and then add the ID field from the Filter array action. This should wrap the action in an Apply to each step to delete all the blanks.

When this automation runs, it will delete any blank rows the table has.

Conclusions

Blank rows in our data can be a nuisance.

Removing them is easy and we have lots of options.

My favourite way is probably the Go To keyboard shortcut method. It’s quick, easy and does the job.

Did I miss any methods? Let me know in the comments below!

#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1