Is there a sort order that puts blank cells at the top?

**calvin-c** · 12-01-2016, 02:35 PM

One of the columns I sort by contains alphabetic data-when it contains any data at all. I'd like the sort to highlight the rows without data in that column by putting them at the top-but it doesn't seem to matter how I sort that column, rows without any data fall at the bottom. Is there a way to fix this? Thanks.

**shg** · 12-01-2016, 02:38 PM

You could use another column with a formula that tests for blanks in the column of interest, and sort by that first.

**newdoverman** · 12-01-2016, 02:53 PM

You don't say how you are sorting. If you are using Editing, Sort and Filter all the blanks will go to the bottom. The same is true if you use the Filter that is on the Data tab. You can however use the filter to only display the blank cells by deselecting Select All and then select at the bottom of the list "Blanks." You can then do whatever you want to do with these blanks.

If you want the list to be presented in full with the blanks at the top and the rest sorted in order use a helper column (I entered data in Column A with a header and used column B as the helper).
Enter this in B2 and fill down

Formula:

=COUNTIF($A$2:$A$20,"<="&A2)

Now, select the original data with the headers and click on the Data tab and then click on the Filter button.
Click the filter on column B and sort from Smallest to Largest. All the blanks will be at the top and the rest will be in alphabetic order.

If you don't need the rest to be in order enter this in the helper column and fill down to identify the blanks as SHG has suggested. Sort smallest to largest and the blanks will be at the top and the rest will be in the original order.

Formula: