Excel Advanced Filter: Display Rows with Non-Blank Values


You can use the following syntax to only display rows that are not blank in an Excel Advanced Filter:

<>

The following examples show how to use this syntax in two different scenarios:

  • Filter for rows that do not have blank values in one specific column
  • Filter for rows that do not have blank values in any column

Example 1: Filter for Rows that Do Not Have Blank Values in One Specific Column

Suppose we have the following dataset that shows information for various basketball teams:

Now suppose we’d like to filter for rows where the Team column is not blank.

To do so, we can define a criteria range in column E:

Next, we can click the Data tab and then click the Advanced Filter button:

We’ll choose A1:C11 as the list range and E1:E2 as the criteria range:

Once we click OK, the dataset will be filtered to only show rows where the Team column is not blank:

Example 2: Filter for Rows that Do Not Have Blank Values in Any Column

Suppose we have the following dataset that shows information for various basketball teams:

Now suppose we’d like to filter for rows where there is not a blank value in any column.

To do so, we can define a criteria range:

Next, we can click the Data tab and then click the Advanced Filter button.

We’ll choose A1:C11 as the list range and E1:G2 as the criteria range:

Once we click OK, the dataset will be filtered to only show rows where there is no blank value in any column:

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

Excel Advanced Filter: How to Use “Contains”
Excel Advanced Filter: How to Use “Does Not Contain”
How to Filter Multiple Columns in Excel

Leave a Reply

Your email address will not be published.