Getting rid of blank rows in your data can be cumbersome, especially when working with large data sets. To add to your problems, Excel uses blanks as limits for ranges when you add tables or sort data, or try to use shortcuts. There are various methods to do this, but in this article we're going to focus on how to delete blank rows in Excel using Power Query.

How to delete blank rows in Excel

1. Convert your data into a Table if this feature isn't already activated. To do this:
   1. Select all your data containing blank rows
   2. Press Ctrl + T and click OK to create a Table
2. Select any cell in your table.
3. Click From Table/Range in the DATA tab to add your data into the Power Query window.
4. Go to Home > Remove Rows > Remove Blank Rows to delete blank rows.
5. Click Load & Close to let Power Query create a new table without blank rows.
6. (Optional) Replace your original data with the new table.

Power Query method is slightly different than other approaches we've covered before. See alternative methods:

• How to remove blank rows by filtering
• How to remove blank rows by sorting
• How to remove blank rows by Go To

This method doesn't modify the original data unless you copy-paste over the existing table on the last step. However, if you need to deal with blank rows with each new data entry, you can use this method to automate the process of removing blank rows. The only thing you need to do is to replace the original data while keeping the Table structure, and refresh the Power Query connection. For more information about Power Query, please see Power Query 101.