Table of Contents
The Excel Advanced Filter can be used to display only those rows that have non-blank values in a dataset. This is useful for quickly identifying entries that have been filled out in the spreadsheet, and also for quickly finding any entries that may be missing values. This is done by setting the criteria range to include only a single field, and then setting the criteria for that field to “not blank”. This will then display only the rows with non-blank values in the specified field.
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
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:
