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.
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. 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.
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.
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.
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.
Now we need to configure the Advanced Filter menu. 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
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
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.
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. 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. 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. 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!
