Note: If the Auto Fill Options icon is obscuring some other data, it is fairly difficult to dismiss. One method is to resize any column.
4. Next, sort the data based on column A by selecting a single cell in column A and pressing the AZ button on the Data tab.
5. Press the End key and then the Down Arrow key to ride the range down to the last cell in A that contains data. Delete the rows below this last cell by highlighting the row numbers, right-clicking, and choosing Delete. (These are the blank cells. It is important to delete the sequence numbers from B for the blank cells so that they do not sort back into the data in the next step.)
6. Move the cell pointer to any value in column B. Click the AZ button on the Data tab to sort the data into the original sequence, without the blanks.
Figure 689 Sort by B to return A to the original sequence.
7. Delete the temporary column B by selecting Home, Delete, Delete Sheet Columns.
8. Delete the temporary row 1 by moving the cell pointer to A1 and selecting Home, Delete, Delete Sheet Rows.
Results:The blanks will be removed from the list, and the list will retain the original sequence.
Alternate Strategy: The previous steps work particularly well when your data set has many columns and you need to delete based on one column. If you truly have a data set that has a single column, try this faster method:
1. Select the range of data.
2. Select Home, Find & Select, Go To Special and in the Go To Special dialog, select Blanks and click OK.
3. Select Home, Delete, Delete Cells, select Shift Cells Up, and click OK.
Excel will delete all the blanks and move the lower cells up.
Alternate Strategy: You can solve this with the Filter. Follow these steps:
1. Add a heading.
2. Select the entire data set.
3. Select Data, Filter. Open the Filter dropdown for the heading. Uncheck Select All. Scroll all the way to the bottom to choose (Blanks).
4. Re-select the visible rows, excluding the heading.
5. Select Home, Delete, Delete Sheet Rows.
6. Select Clear from the Sort & Filter group on the Data ribbon tab.