Excel Sort and Ignore Blanks (4 Ways)

By default, when we sort data in Excel, it leaves all the blank rows at the end. But we don’t always want this. Sometimes we need to ignore the blanks while applying the sort in Excel. In this article, you will learn 4 ways to sort & ignore blanks in Excel with ease.


Download the Practice Workbook

You can download the Excel file from the following link and practice along with it.


4 Ways to Sort & Ignore Blanks in Excel

Look at the following data table. It has blank rows at numbers 7,9 and 11 positions.

Before applying the sorting command, you need to hide them first.

Dataset: 4 Ways to Sort & Ignore Blanks in Excel


1. Hide Blank Rows to Sort & Ignore Blanks in Excel

To ignore the blank rows while sorting, hiding the blanks is a good option.

To hide all the blanks manually,

❶ Select the entire row first. Click on any cell of the blank row. Then press the SHIFT + SPACE keys together. This will select the entire row at once.

❷ Right-click on the selection area.

❸ Then click on Hide from the list.

Hide Blank Rows to Sort & Ignore Blanks in Excel

❹ Now select the whole data table.

❺ Go to the Data tab from the main ribbon.

❻ Then click on A to Z icon to apply the sorting command.

This command will sort all the data in ascending order.

❼ Now unhide all the hidden rows. To do that first, right-click on the area in between cells 6 and 8. This is where the first hidden cell resides.

❽ After that select Unhide from the pop-up list.

❾ Continue this process to unhide all the hidden blanks of the data table.

Unhide Blank Rows to Sort & Ignore Blanks in Excel

When you are done with all the blank rows unhide, you will see your data is sorted ignoring the blanks like the image below:

Read More: How to Sort Rows in Excel (2 Simple Methods)


2. Use Go To Special to Sort and Ignore Blanks in Excel

You can use the Go To Special feature to select all the blanks of your data table. Based on your blank row selection, you can hide them all and apply the sorting command.

To do that follow the steps below.

❶ First of all, select the entire data table and go to the Home tab from the main ribbon.

❷ Then go to the Editing group.

❸ Under the Find & Select group, you will find the Go To Special option. Just click on it.

Use Go To Special to Sort and Ignore Blanks in Excel

❹ Then the Go To Special dialog box will appear. Select Blanks and hit the OK button.

After that, you will get all the blanks selected. To hide them all,

❺ Right-click on the selection area and then choose Hide from the pop-up list.

hide blanks to Sort and Ignore Blanks in Excel

So you have all the blank rows hidden. Now apply the sorting command to your data. To do that,

❻ Select the entire data table. Then go to the Data tab. Under the Sort & Filter group, you will find the A to Z icon. Just click on it.

This will sort out your data in ascending order.

❼ Again select the entire data table. Right-click on it and choose Unhide to reveal all the hidden blank rows.

So you will have all the sorted data ignoring the blanks as in the image below.

Read More: How to Use Advanced Sorting Options in Excel


Similar Readings:


3. Use Filter to Sort and Ignore Blanks in Excel

You can the Filter feature to sort and ignore blanks in Excel.
To do that,
❶ Select the whole data table.
❷ Go to the Data tab. Choose Filter from the Sort & Filter group.

Use Filter to Sort and Ignore Blanks in Excel

❸ Click on the drop-down icon from the table headers. Uncheck the Blanks and hit Ok.

❸ Again select the whole data table. Go to the Data tab and click on the A to Z icon to sort in ascending order.

❹ After that click on the Filter icon from one of the table headers.
❺ Mark check on the Blanks this time and hit OK.

Use Filter to Sort and Ignore Blanks in Excel

Following the above steps, you will have your data sorted by ignoring the blanks.

Read More: How to Sort and Filter Data in Excel (A Complete Guideline)


4. Use VBA Code to Sort and Ignore Blanks in Excel

You can use VBA code to sort and ignore the blank rows in Excel.

To do that,

❶ Press ALT + F11 to open the VBA editor.

❷ Then go to Insert > Module to create a new module.

Copy the following code and paste it to 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.

Use VBA Code to Sort and Ignore Blanks in Excel

❺ Now hit the Run Sub button or press F5 to run the above code.

When the VBA code runs, it will sort data and ignore the blanks as in the picture below:

Read More: VBA to Sort Table in Excel (4 Methods)


Sort and Ignore Blanks in Excel Keeping the Unique Items Only

In this section, I’m going to show you to sort data in ascending order ignoring the blanks. If you have blank rows, this method will delete them all. Besides, if you have duplicate values, this method will extract only the unique values and sort them accordingly.

In the following image, under the Before column, we have raw data with duplicate values as well as blanks. In the After column, we will extract only unique values sorted in ascending order ignoring the blank rows.

To do that,

❶ Select cell D5 and insert 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)))

❷ After that hold CTRL and SHIFT buttons.

❸ Then press the ENTER button. This will execute the array formula.

❹ Then drag the Fill Handle icon from cell D5 to D11.

Sort and Ignore Blanks in Excel Keeping the Unique Items Only

Formula Breakdown:

  • COUNTIF($D$4:D4,$B$5:$B$15)=0 returns the count value of the cells, where 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 becomes 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 the range B5 to B15.
  • COUNTIF($B$5:$B$15,”<“&$B$5:$B$15) returns either TRUE or FALSE based on the comparison result between the content of $B$5 and successive cells up to $B$15.
  • ISTEXT($B$5:$B$15) evaluates whether the cell range $B$5:$B$15 contains any text item.
  • IF(ISTEXT($B$5:$B$15),COUNTIF($B$5:$B$15,”<“&$B$5:$B$15),””),0) returns null string if there’s a text from $B$5 to $B$15. If there’s any number then 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 largest number first in the range B5 to B15. After the end of the numbers, it returns the texts in ascending order.

Related Content: How to Sort Unique List in Excel (10 Useful Methods)


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

We have a list of products and their availability is marked with either TRUE or FALSE. We want to get a list of sorted items in ascending order based on the filtering result.

Our main target is to extract all the unique items out of the raw data in ascending order with a condition. To do that,

❶ First of all, select the data table.

❷ Go to the Data tab in the main ribbon.

❸ From the Sort & Filter group choose Filter to apply Filter on the data table.

❹ Then insert the following formula in cell 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)),"")

❺ Now hold the CTRL and SHIFT buttons together.

❻ After that hit the ENTER button to execute the array formula.

Now I’ve input TRUE in the Fact box and the following result came along.

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

If I input FALSE in the Fact box then the Sorted Data column will look like this:

Formula Breakdown:

  • COUNTIF($F$6:F6,Table1[Items])=0 returns a count value if there’s a match in between the contents of the Sorted Data column and Items column of the raw data table.
  • ISBLANK(Table1[Items]) evaluates whether the Items column contains any blank cell.
  • IF(Table1[Fact]=$F$4 processes the input item of the Fact box.
  • SMALL(IF(Table1[Fact]=$F$4,IF(ISBLANK(Table1[Items]),””,IF(COUNTIF($F$6:F6,Table1[Items])=0 picks up the smallest item in the Items column.
  • MATCH(SMALL(IF(Table1[Fact]=$F$4,IF(ISBLANK(Table1[Items]),””,IF(COUNTIF($F$6:F6,Table1[Items]) looks for a match between the content inserted into the Fact box and the contents of the Items column.
  • INDEX(Table1[Items],MATCH(SMALL(IF(Table1[Fact]=$F$4,IF(ISBLANK(Table1[Items]),””,IF(COUNTIF($F$6:F6,Table1[Items]) returns the index number of the cell of the Items column if there’s a match in between the item of the Fact box and that of the Items column.
  • ISNUMBER(Table1[Items]) checks whether the item in the Items column is a number or not.

Related Content: Sort Column by Value in Excel (5 Methods)


Things to Remember

  • You can use CTRL + SHIFT + L to apply the filter.
  • Press F5 to run an Excel VBA code.
  • To execute an array formula, press CTRL + SHIFT + ENTER.

Conclusion

To sum up, we have discussed 4 ways to sort and ignore blanks in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo