Excel Filter Not Working: 10 Fixes for AutoFilter and Advanced Filter
To fix an Excel filter that is not working as quickly as possible, make the data range a single continuous block (remove blank rows and columns, and unmerge cells), organize the headers and data formats, and then convert the range to a Table. Work through the 10-item checklist below in order.
Quick Fix (3 Minutes)
- Select blank cells, then delete the rows or columns.
- Unmerge merged cells.
- Standardize formats with Text to Columns or VALUE.
- Reset the headers and reapply the filter.
- Convert the range to a Table (Ctrl+T).
How It Works
Filters work based on a continuous data block and valid headers. Mixed data formats, merged cells, and spill conflicts are the main causes of filter problems.
10 Fixes
1) Remove Blank Columns and Rows
Home → Find & Select → Go To Special (F5) → Blanks.
2) Unmerge Cells
Ctrl+1 → Alignment → clear Merge cells. To fill from the cell above, use =the cell above and then press Ctrl+Enter.
3) Fix Mixed Number and Text Formats
Data → Text to Columns → Finish, or use =VALUE(A2). Remove spaces and control characters with TRIM/CLEAN.
4) Unhide Rows and Remove Subtotals
Data → Outline → Ungroup, then remove subtotals. Select the entire sheet and unhide rows and columns.
5) Convert to a Table
Press Ctrl+T and select My table has headers. Tables lock in the range and headers, greatly improving filter reliability.
6) Reset the Header Row
If headers are recognized as data, the filter can malfunction. Change them to text headers and reapply the filter.
7) Reset Filters and Sorting
Data → Clear (Clear Filter). Use Alt+D,F,F to toggle the filter.
8) Resolve #SPILL!
Clear the spill range or paste values. If there is a conflict, filter results may not work correctly.
9) Follow Advanced Filter Criteria Range Rules
Headers must match the source exactly. Use the same row for AND conditions and rows below for OR conditions. Copying to another location is recommended.
10) Check the File and Options
Save as .xlsx, set Formulas → Calculation Options → Automatic, test with add-ins disabled, and update Office.
Practical Example
Convert an online order list to a Table, use Advanced Filter to copy only September 2025 orders to another location, then sort by amount in descending order to extract the top 10 records.
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
| Filter button is grayed out | Protected or shared mode | Review → Unprotect Sheet, then unprotect the workbook |
| Row count stays the same | Blank rows or merged cells | Remove blank rows, unmerge cells, and convert to a Table |
| Some values are missing | Mixed data formats | Use Text to Columns, TRIM/CLEAN, and VALUE |
| Advanced Filter error | Criteria header does not match | Make headers identical and copy to another location |
| Slow performance | Large dataset plus spill ranges | Convert to values, remove merged cells, and reduce the range |
Related Posts
- Data Cleaning: TRIM/CLEAN
- Table Basics and Benefits
- Complete Guide to Advanced Filter
- SUMIFS Multiple-Criteria Best Practices
- XLOOKUP Basics
Reference: Microsoft Support – official documentation for filters and Tables