Table of Contents
Excel’s advanced filter feature allows users to filter data in a more precise manner, including displaying only rows with non-blank values. This can be useful for organizing and analyzing data without clutter from empty cells. To use this feature, users can select the data range, go to the “Data” tab, and click on “Advanced” in the “Sort & Filter” group. In the advanced filter dialog box, users can choose to filter by specific criteria, such as non-blank values. This will result in a display of only rows with data in the selected columns, making it easier to view and work with relevant data.
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
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:
Cite this article
stats writer (2024). How can I use Excel’s advanced filter to display only rows with non-blank values?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-excels-advanced-filter-to-display-only-rows-with-non-blank-values/
stats writer. "How can I use Excel’s advanced filter to display only rows with non-blank values?." PSYCHOLOGICAL SCALES, 30 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-excels-advanced-filter-to-display-only-rows-with-non-blank-values/.
stats writer. "How can I use Excel’s advanced filter to display only rows with non-blank values?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-excels-advanced-filter-to-display-only-rows-with-non-blank-values/.
stats writer (2024) 'How can I use Excel’s advanced filter to display only rows with non-blank values?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-excels-advanced-filter-to-display-only-rows-with-non-blank-values/.
[1] stats writer, "How can I use Excel’s advanced filter to display only rows with non-blank values?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use Excel’s advanced filter to display only rows with non-blank values?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.