Excel Filter Not Working? 12 Fixes for Disabled or Gray Filters
Summary: Eighty percent of filter issues come from headers, blanks, merged cells, or mixed data formats. Apply the checklist below from top to bottom, and most issues can be resolved in 5–10 minutes.
- One header row with unique titles
- Remove blank rows in the middle
- Unmerge cells
- Fix mixed numbers and text
- Convert to a table
- Clear filters
- Unhide rows and columns and expand groups
- Reset a corrupted used range
- Turn off worksheet protection or sharing
- Deselect objects
- Advanced Filter
- Slicers and timelines
1) Use one header row and unique titles for each column
If headers span two rows or contain merged cells, Excel may not recognize the range correctly. Use a single header row, and give each column a clear, unique title.
2) Remove blank rows in the middle
Blank rows between data blocks break the filter range. Use Ctrl+G → Special → Blanks → Delete Entire Row to remove them at once.
3) Unmerge cells
Merged cells can cause filters to behave unexpectedly. Turn off Merge & Center, and use Center for visual alignment instead.
4) Fix mixed numbers and text
If numbers and text-formatted numbers are mixed in the same column, criteria filters may not work correctly. Convert them in a helper column, then paste values:
=NUMBERVALUE(A2) ' Locale can be specified (decimal/group separators)
=VALUE(A2) ' Simple conversion
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) ' Remove spaces and control characters
5) Convert the range to a table
Creating a table with Ctrl+T automatically supports filtering, sorting, styles, and structured references, and the range expands automatically as new rows are added.
6) Clear filters
When conditions accumulate across columns, it can look as though values have disappeared. Use Data → Clear to reset filters, then apply them again.
7) Unhide rows and columns and expand groups
Hidden rows or columns and collapsed outline groups can make filter results confusing. Select all cells (Ctrl+A) → Unhide to reset them.
8) Reset a corrupted used range
If Ctrl+End goes beyond the end of your data, the used range may be corrupted. Use Clear All on unnecessary areas and save, or copy and paste values and formatting into a new worksheet.
9) Turn off worksheet protection or shared mode
Use Review → Unprotect Sheet. If the legacy Shared Workbook feature is enabled, filtering may be limited.
10) Deselect objects or charts
The Filter command on the ribbon may be grayed out while a chart or shape is selected. Click a blank cell to deselect it.
11) Extract results with Advanced Filter
Create a separate criteria range, then choose Data → Advanced and Copy to another location. This handles complex OR/AND criteria cleanly.
12) Filter intuitively with slicers or timelines
Select a table or PivotTable and add a Slicer (or Timeline) to combine conditions and explore data quickly with clicks.