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
