How to Sort and Ignore Blanks - 4 Methods

The following data table has blank rows.

Before applying the sort command, hide the rows first.

Method 1 – Hide Blank Rows to Sort & Ignore Blanks in Excel

Click any cell of the blank row. Press SHIFT + SPACE to select the entire row at once.
Right-click the selected area.
Click Hide.

Select the whole data table.
Go to the Data tab.
Click A to Z to apply the sort command.

It will sort data in ascending order.

Right-click the area in between cells 6 and 8.
Select Unhide.
Proceed and unhide all hidden blanks in the data table.

Data is sorted ignoring the blanks:

Read More: How to Add Sort Button in Excel

Method 2 – Use the Go To Special Feature to Sort and Ignore Blanks in Excel

Select the entire data table and go to the Home tab.
Select Editing.
In Find & Select, click Go To Special.

In the Go To Special dialog box, select Blanks and click OK.

All blanks are selected. To hide them:

Right-click the selected area and choose Hide.

All blank rows are hidden. To apply the sort command:

Select the entire data table. Go to the Data tab. In Sort & Filter, click A to Z.

Data will be sorted in ascending order.

Select the entire data table. Right-click it and choose Unhide to reveal the hidden blank rows.

Data is sorted, ignoring the blanks:

Method 3 – Using a Filter to Sort and Ignore Blanks in Excel

Select the whole data table.
Go to the Data tab. Choose Filter in Sort & Filter.

Click the drop-down icon in the table headers. Uncheck Blanks and click OK.

Select the whole data table. Go to the Data tab and click A to Z to sort data in ascending order.

Click Filter in one of the table headers.
Check Blanks and click OK.

Read More: How to Sort Data by Value in Excel

Method 4 – Using a VBA Code to Sort and Ignore Blanks in Excel

Press ALT + F11 to open the VBA editor.
Go to Insert > Module to create a new module.

Enter the following code in the VBA editor.

Private Sub IgnoreBlanks(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
With Sheets("POS Tracker")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("B5:E13"), SortOn:=xlSortOnValues, _
Order:=xlDescending, DataOption:=xlSortNormal
With .Sort
.SetRange Range("B5:B13")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Press CTRL + S to save the VBA code.

Click Run Sub or press F5 to run the code.

The VBA code will sort data and ignore the blanks:

Read More: How to Undo Sort in Excel

Sort and Ignore Blanks in Excel Keeping the Unique Items Only

In the dataset below, in the Before column, there’s raw data with duplicate values and blanks. Extract unique values sorted in ascending order and ignoring the blank rows to the After column.

Select D5 and enter the following array formula:

=IFERROR(SMALL(IF((COUNTIF($D$4:D4,$B$5:$B$15)=0)*ISNUMBER($B$5:$B$15),$B$5:$B$15,"A"),1),INDEX($B$5:$B$15,MATCH(SMALL(IF(ISTEXT($B$5:$B$15)*(COUNTIF(D4:$D$4,$B$5:$B$15)=0),COUNTIF($B$5:$B$15,"<"&$B$5:$B$15),""),1),IF(ISTEXT($B$5:$B$15),COUNTIF($B$5:$B$15,"<"&$B$5:$B$15),""),0)))

Hold CTRL and SHIFT.
Press ENTER.
Drag the Fill Handle to D11.

Formula Breakdown:

COUNTIF($D$4:D4,$B$5:$B$15)=0 returns the count value of the cells, in which there is a match between $D$4:D4 and $B$5:$B$15.
ISNUMBER($B$5:$B$15) returns turn if the value in the cell is a number.
COUNTIF($D$4:D4,$B$5:$B$15)=0)*ISNUMBER($B$5:$B$15) returns either true or false based on the logical multiplication of the COUNTIF($D$4:D4,$B$5:$B$15)=0 and ISNUMBER($B$5:$B$15).
IF((COUNTIF($D$4:D4,$B$5:$B$15)=0)*ISNUMBER($B$5:$B$15),$B$5:$B$15,”A”),1) returns A if the logical expression is false.
SMALL(IF((COUNTIF($D$4:D4,$B$5:$B$15)=0)*ISNUMBER($B$5:$B$15),$B$5:$B$15,”A”),1) returns the smallest number in B5:B15.
COUNTIF($B$5:$B$15,”<“&$B$5:$B$15) returns either TRUE or FALSE based on the comparison between the content of $B$5 and the successive cells up to $B$15.
ISTEXT($B$5:$B$15) evaluates whether the cell range $B$5:$B$15 contains text.
IF(ISTEXT($B$5:$B$15),COUNTIF($B$5:$B$15,”<“&$B$5:$B$15),””),0) returns a null string if there’s text in $B$5:$B$15. If there’s a number, it returns the smallest number.
SMALL(IF(ISTEXT($B$5:$B$15)*(COUNTIF(D4:$D$4,$B$5:$B$15)=0),COUNTIF($B$5:$B$15,”<“&$B$5:$B$15),””),1),IF(ISTEXT($B$5:$B$15),COUNTIF($B$5:$B$15,”<“&$B$5:$B$15),””),0) returns the smallest to the largest number in the range B5:B15 and the text in ascending order.

Sort Data and Extract Unique Values Ignoring Blanks in Excel with a Condition

In a list of products, their availability is marked either TRUE or FALSE. To get a list of sorted items in ascending order based on the filtering result:

Select the data table.
Go to the Data tab.
In Sort & Filter, choose Filter.
Enter the following formula in F7.

=IFERROR(INDEX(Table1[Items],MATCH(SMALL(IF(Table1[Fact]=$F$4,IF(ISBLANK(Table1[Items]),"",IF(COUNTIF($F$6:F6,Table1[Items])=0,IF(ISNUMBER(Table1[Items]),COUNTIF(Table1[Items],"<"&Table1[Items]),COUNTIF(Table1[Items],"<"&Table1[Items])+SUM(1*ISNUMBER(Table1[Items]))+1),"")),""),1),IF(Table1[Fact]=$F$4,IF(ISBLANK(Table1[Items]),"",IF(COUNTIF($F$6:F6,Table1[Items])=0,IF(ISNUMBER(Table1[Items]),COUNTIF(Table1[Items],"<"&Table1[Items]),COUNTIF(Table1[Items],"<"&Table1[Items])+SUM(1*ISNUMBER(Table1[Items]))+1),"")),""),0)),"")

Hold CTRL and SHIFT.
Press ENTER.

TRUE is entered in the Fact box. This is the output.

If FALSE is entered in the Fact box, the Sorted Data column will be: