1 Get & Transform Data
The Get & Transform Data tool in Excel allows you to quickly retrieve data. There are several ways to modify the data while doing so, one of which is to eliminate blank rows. Fortunately, it’s simple to use, although it does necessitate the usage of a Table object; if your data is a regular data range, the feature will convert it for you, which you may not want.
- In the Get & Transform Data group, click From Table/Range. At this point, Excel will fail to find the entire data set because of the empty rows.
- Change the default range check the My table has headers option, and click OK.
- Excel will launch the Power Query Editor to retrieve the data.
- In the Reduce Rows group (Home tab for Power Query. Power Query will remove the blank rows but not the rows with blank cells.
- Click Close & Load in the Close group, and Power Query will copy the modified data set to a new sheet in Excel. At this point, the data is a Table object which you can convert to an ordinary range if you prefer.
2 Filter
- Click the Data tab, and then click Filter in the Sort & Filter group.
- Use the new Order ID dropdown to choose your filter: uncheck the (Select All) option and then check (BlanksIf the (Blanks) option isn’t available, start over but select the range first. If the filter returns all empty rows, it’s a simple matter to select the filtered set and press Ctrl+- (the minus key). When Excel prompts you to delete the entire rows, click OK. On the other hand, if you want to delete only the blank rows and keep the incomplete records, you can select a non-contiguous set of rows by holding down the Ctrl key while clicking row headers. Then, press Ctrl+-.
3 Sort
Select the data range
Click Sort in the Sort & Filter group (on the Data tab) to sort the blank records to the bottom.
If you can live with the sorted results, this method is perhaps the simplest. If you need to return the original order and you have a column that will return the data to its original order–leaving the blank rows at the bottom out of the sort–sorting might work for you. It’s still a lot of work and complicated by the incomplete row problem
4 Go To
You can use Excel’s Go To feature to select all the blank cells in a selected range. If the results are empty rows, you can then press Ctrl+- to delete those rows.
- Select the data range
- Press F5 and then click Special in the resulting dialog.
- Select Blanks and click OK.
- Blank cells will be highlighted now While holding down the Ctrl key, you can click the blank cells to remove them from the non-contiguous selection and then press Ctrl+- to delete only the empty rows.