Deleting Rows where a Column is Empty

Fran has a worksheet that contains a large amount of data. She needs to delete any row in which column F is empty. She wonders if there is an easy way to do this that doesn't involve the use of a macro.

Actually, there are several ways that you could approach this task, and none of them require a macro. Each of the following sections examines one of three different approaches.

Sorting the Data

If you sort your data based on the contents of column F, then Excel groups all the blanks together. If you sort in ascending order based on the contents of column F, then Excel uses the following sorting order:

1. Number values, from least to greatest
2. Date and time values, from earliest to latest
3. Text values, punctuation first, numbers second, then alphabetically (1, 2, 3, a, b, c, and so on)
4. Logical values, FALSE, then TRUE
5. Error values
6. Blanks

Notice that blanks appear last in the sorted data. So, after the sort, all you need to do is select a cell in column F, press Ctrl+Down Arrow, and you'll be at the last row where there is a value of any type in column F. You can then delete all the rows from that point downward.

You should note that if the cell appearing blank is the result of a formula return a blank string (what I call an "apparent blank"), then the cell is actually considered a text value (#3 in the above list). After the sorting you'll need to determine if you really want to delete these rows or not.

Of course, sorting changes the order of the remaining data rows. If you don't want the order changed, then you can simply re-sort the list (after deleting the desired rows) according to how the data was previously sorted.

If you are unsure of how the data is sorted, then add a helper column before doing the sorting and add sequential numbers in the column. Sort and delete the desired rows, then re-sort based on the helper column. At this point you can delete the helper column as it is no longer needed.

Filtering the Data

You can add a filter to your data to show only those rows where column F is blank. Follow these steps, provided you have a header row at the beginning of your data:

1. Select a cell within your data.
2. Press Ctrl+Shift+L. Excel turns on AutoFilter and displays a drop-down icon at the right side of each cell in the header row.
3. Click the drop-down icon at the right side of column F's header cell. Excel displays some filtering options.
4. Clear the Select All check box.
5. Select the (Blanks) check box. Excel hides all the rows except those in which column F is empty.
6. Select all the visible rows.
7. Delete the rows.
8. Again press Ctrl+Shift+L. Excel turns off AutoFilter and displays all the remaining rows.

When you apply the filter in step 5, it is not just "true blanks" that are visible, but also "apparent blanks," meaning those rows where the blank in column F is the result of a formula that returns an empty string. If you don't want to delete both types of blanks, then you'll want to use a different selection technique for your blanks.

Selecting Blanks

Perhaps the easiest approach is to use the Go To capabilities of Excel. Follow these steps:

1. Select the entire column F. (Just click in the column header to select the column.)
2. Press F5 or Ctrl+G. Excel displays the Go To dialog box.
3. Click the Special button. Excel displays the Go To Special dialog box.
4. Click the Blanks radio button.
5. Click OK. Excel has modified what is selected to only those cells that contain blanks.
6. Right-click on one of the selected cells. Excel displays a Context menu.
7. Choose Delete from the Context menu. Excel displays the Delete dialog box.
8. Choose the Entire Row radio button.
9. Click OK. Excel deletes the rows.

When you use Go To to select blank cells (steps 4-5), what Excel actually selects is "true blanks," meaning those cells that actually contain nothing. Any cells that contain "apparent blanks" resulting from a formula are not selected and, therefore, those rows are not deleted.

If you prefer using shortcut keys, you can get to the Delete dialog box quicker (steps 6-7) by pressing Ctrl+- (that's Ctrl plus the minus sign).

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13993) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.