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.

**Table of Contents**hide

## 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.

### 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.

❹ 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.

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.

❹ 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.

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:**

**Sum Using OFFSET and MATCH in Excel (With Alternative Options)****Excel Sort Dates in Chronological Order (6 Effective Ways)****Random Sort in Excel (Formulas + VBA)****[Solved!] Excel Sort Not Working (2 Solutions)****How to Add Sort Button in Excel (7 Methods)**

### 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.

❸ 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**.

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.

❺ 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**.

**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.

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.