Remove Blank Rows with an Excel Power Query

In this guide we will look at how to remove blank rows in an Excel spreadsheet with a Power Query.

The Power Query is available Excel 2016 and above. In Excel 2010 it is available as an add-in only. If you’re working in an older version, please see my previous article.

Please read: There is a drawback with the Power Query method – it will knock out all your original formatting including the numbering, for example, currency will just be a general number. You can always fix it back to the way it was. This should still be quicker than deleting the rows individually.

The steps…

Let’s look at the steps to delete blank rows using the Microsoft Excel Power Query.

First select all your data in the spreadsheet to ensure the Power Query picks up everything. To do this, select the first cell in your spreadsheet and then using your keyboard, press Ctrl, Shift and End.

Under the Data tab, in the Get & Transform Data group, click From Table/Range

Click OK to the Create Table box

The Power Query Editor will then open.

Click Remove Rows and select Remove Blank Rows

This will remove the Blank Rows only.

To return to Excel, click Close and Load

A new sheet with the rows removed will be created in your Excel Workbook. You will still have the original worksheet before the update but without the original formatting.

Formatting

When you go through the steps above to remove the blank rows, your Worksheet will become what is call a Table. For more information on Format as Table, visit the Microsoft website.

If you would like to convert your worksheet back, click Table Design and select Convert Range. You will still need to amend the formatting like the currency. As I said earlier in this article, this option my be easier than deleting the rows individually.

Suggestions

I'm always looking for new ideas for topics. If you have a suggestion you would like me to cover or think would be useful, then please email: Suggestions@ReadySteadyXL.com