A quick way to find and delete blank rows in a worksheet is to use Excel's filter feature. Learn how to locate and remove entirely blank rows with this handy tool.
Blank or incomplete data points are not just an eyesore; they can also create inconsistent results in calculations and charts. One option to remove blank rows is sorting the data, so all blank rows appear at the bottom of the data where they are conveniently deleted. However, this solution requires reordering the data, which could also cause problems. A simpler solution involves Excel 2013’s Filter command to display and subsequently delete blank rows in place, so data order doesn’t change.
1. Click the column letter to select the entire column in which blank data appears.
In this example, clicking the D column header highlights the D column, which contains several blank data rows.
2. Select the Data tab and click Filter in the Sort & Filter group.
When you select the Filter option, a drop-down arrow appears at the top of the selected column.
Alternatively, you could have selected any of the column labels, such as Low in this case, and then when you clicked Filter, drop-down arrows would have appeared at the top of each consecutive column of data. It’s not recommended to click just anywhere in the data, however, because gaps in the columns or data could cause unpredictable results.
3. Click the column header’s drop-down arrow and uncheck the Select All option.
Deselecting Select All unchecks all the other options.
4. Scroll to the bottom of the list of check boxes, check (Blanks) and then click OK.
Selecting only (Blanks) filters out all data except those rows containing blank values in the selected column. Be sure to use the scroll bar, because scrolling with your mouse wheel only scrolls the data behind the drop-down menu.
5. Click the row number corresponding to the first blank row and drag your mouse to the last blank row before releasing your mouse button. Doing so selects all blank rows.
In this example, click 3 when your mouse cursor turns into a black, right-pointing arrow and drag down to 19. You’ll notice the numbers are no longer consecutive, but don’t worry; you’ll only be selecting the visible rows.
Alternatively, you could select the data from A3 to F19, but you’ll still end up deleting the entire row and receive a confirmation message to that effect later. To avoid the message and make selecting easier in wide data sets, use the row number selection method.
6. Right-click anywhere in the selection and choose Delete Row to delete all selected rows.
7. Click Filter in the Sort & Filter group to remove the filter option and view the remaining data.
Deselecting the Filter command removes the Filter drop-down arrow and displays the remaining data with the blank rows removed.