Delete Blank Rows

There may be occasions when you have unnecessary blank rows in your data that you want to remove. If you have a large list with a lot of empty rows, it could be time-consuming to manually find and delete the empty rows. Thankfully, there are ways you can do this automatically. We will cover two methods for achieving this task here. The first will be using commands on the Excel Ribbon and the second method involves using the Power Query Editor, which has a command for deleting blank rows.

Method 1

There is no direct command for deleting blank rows on the Ribbon, but you can combine a couple of commands to achieve the task.

To delete blank rows, do the following:

1. Click on the Home tab, and then the Find & Select button in the Editing group.
2. Select Go To Special from the dropdown menu.
3. On the Go To Special screen select Blanks and click OK .
   
   Excel will select the blank cells in the range.
    
4. On the Home tab, in the Cells group, click Delete > Delete Sheet Rows .

And that’s it! This will delete all the rows that were identified and selected using the Go To Special command.

Tip : If you mistakenly deleted rows that you don’t want to delete, you can undo your changes by clicking the Undo button on the Quick Access Toolbar to reverse your changes.

Method 2

The second method involves using the Power Query Editor in Excel 2019.

To delete blank rows using the Power Query Editor, do the following:

1. Select the data list for which you want to remove blank rows.
   Tip : To quickly select a range, click on the top-left cell of the range, hold down the Shift key, and click on the bottom-right cell.
2. On the Data tab, in the Get & Transform Data group, click the From Table/Range command button.
   This will open and display your data in the Power Query Editor , which is a supplementary tool in Excel 2019 with its own user interface and command buttons.
    
3. On the Home tab of the Power Query Editor, click the drop-down arrow on the Remove Rows command button, and select Remove Blank Rows from the drop-down menu.
   Excel deletes all the blank rows identified in the data list.
4. To save your transformed data, on the Home tab of the Power Query Editor, click the Close & Load command button to paste the transformed data in a new sheet in your workbook and close the Power Query Editor.

That’s it! Your data list without the blank rows will now be in a new sheet with the original data list unchanged. Note that, among other commands, you can also use the Power Query Editor to remove duplicate rows.