Fix an Excel Filter That Does Not Show All Column Values
Question details
The user needs an Excel filter to display all values from a column.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Filtering a column in a dataset that may contain a blank row or gap.
- Observed behavior
- The filter list shows only some column values, possibly because the filter range ends before the remaining data.
Find the first and last rows of the dataset, including the column headers, and check whether any completely blank rows separate the data. Also check for filters already applied to other columns.
Recreate the filter using the complete data range
Use this method when a blank row or an incomplete selection has left some records outside the filter range.
On the Data tab, select Clear in the Sort & Filter group to remove active filter criteria.
Inspect the rows between the header and the last record. If a completely blank row separates the data, right-click its row number and select Delete, provided it does not contain information needed elsewhere on the sheet.
On the Data tab, select Filter to remove the existing filter arrows.
Select the entire range, from the header row through the last data row and across all relevant columns.
Select Data > Filter, then open the affected column's filter arrow and check its value list.
Use a custom filter for a large number of unique values
Use this option if the full range is selected but the column has more unique values than the filter list can display.
Filter spreadsheet data with WPS Office
WPS Office is a free, lightweight alternative for working with spreadsheets. Its familiar interface and Excel format compatibility make it easy to open existing files and filter data in WPS Spreadsheet.
Frequently Asked Questions
Why are rows below a blank row missing from my Excel filter?
Excel may have created the filter for only the contiguous data above the blank row. Select Data > Filter to turn it off, select the complete dataset, and apply the filter again.
How do I include additional rows in an Excel table filter?
Click inside the table, select Table Design > Resize Table, and adjust the range to include the additional rows.
Can another column's filter affect the values I see?
Yes. Active criteria on other columns can restrict the records available. Select Data > Clear to remove the criteria, then check the column filter again.
What if my column has more than 10,000 unique values?
Excel displays up to 10,000 unique entries in the filter list. Use the column's Text Filters, Number Filters, or Date Filters menu to apply a condition instead of relying on the checklist.
