Excel Filter Not Working? Fix It Fast (2026 Guide)

My operations dashboard ran clean for eleven months. Then December hit, the Northeast region had zero qualifying orders, and the whole thing broke. Not a data problem. Not a server issue. I'd omitted the third argument in my =FILTER() formula — the if_empty parameter — and Excel threw a #CALC! error instead of a blank. Eleven months of no problems, one edge case, and the whole dashboard looked corrupted. That's the kind of thing you only forget once.

If your Excel filter not working situation looks nothing like that — if you're dealing with AutoFilter, the dropdown arrows under the Data tab — you're in a different place entirely. Here's where most guides fail readers: they treat every filter problem as the same problem. They're not. Let's triage yours properly before touching a single setting.

---

What You'll Fix — and the One Thing to Check Before You Touch Any Excel Filter Settings

A working AutoFilter puts dropdown arrows on your header row, hides non-matching rows when you select criteria, and updates when your data changes. That's it. If any part of that description doesn't match what you're seeing, something structural has broken — and in my experience, it's almost never the filter itself.

Before anything else, check two things: whether your sheet is protected, and whether your worksheets are grouped. Both will disable AutoFilter entirely. Right-click your sheet tab — if you see Ungroup Sheets, that's your issue. For protection, go to Review → Unprotect Sheet. If either of those applies, fix it first and see if your filter comes back before reading further.

Both sheet protection and grouped worksheets disable AutoFilter completely. These two checks take under a minute and resolve the greyed-out button for the majority of users.

AutoFilter vs. the FILTER Function — Make Sure You're Fixing the Right Thing

AutoFilter is the feature under Data → Filter. It's the dropdown arrows. The =FILTER() function is a formula: it lives in a cell and outputs a filtered array dynamically. They share a name and almost nothing else. If you're getting a #CALC! or #VALUE! error in a cell, you're dealing with the FILTER function, not AutoFilter — and the fixes are completely different. Make sure you know which one you're troubleshooting before going further.

---

Step 1: Diagnose Your Filter by What You Actually See on Screen

Once you've confirmed the sheet isn't protected and the worksheets aren't grouped, the next step is reading the symptom correctly. The cause of your Excel filter not working problem is almost always visible in how the filter is misbehaving — you just have to know what to look for.

If the Filter Button Is Greyed Out

A greyed-out filter button points to one of three things: sheet protection (covered above), grouped worksheets (also above), or — and this one catches people — a shared workbook with editing restrictions. In Excel 365 and Excel for Mac, shared workbooks sometimes restrict AutoFilter for non-owners. Check Review → Share Workbook and see whether advanced sharing is limiting what you can do.

If the Filter Runs But Hides Rows It Shouldn't

This is the more frustrating symptom because the filter appears to work, and then doesn't. The usual culprits are blank rows and merged cells. A single blank row inside your data range tells AutoFilter that the data ends there. Everything below that blank row gets ignored — completely. I've seen this catch analysts with hundreds of rows of data that the filter simply never touched.

Merged cells are worse. I have never, in over a decade of working with Excel, seen merged cells improve a data range. Not once. They break sorting, they break filtering, and they do it quietly. The filter just stops behaving predictably the moment merged cells appear anywhere in the range. If your filter only works on some rows, merged cells are the first thing to check.

Never use merged cells in a data range you intend to sort or filter. They break both features silently and are far harder to debug than they are to avoid.

---

Step 2: Apply the Fix That Matches Your Excel Filter Problem

With the symptom identified, the fix is usually faster than the diagnosis. Walk through this with me.

Fix Blank Rows and Merged Cells So AutoFilter Reads the Full Range

1. Select your entire data range and use Go To Special (Ctrl+G → Special → Blanks) to locate blank rows. Delete them — don't just clear the contents.
2. For merged cells, select the range, go to Home → Merge & Center dropdown → Unmerge Cells. Yes, this breaks the visual formatting. That's the point.
3. After clearing both issues, convert your data to an Excel Table with Ctrl+T. This is the step most guides skip, and it's the one that actually prevents the problem from coming back. Excel Tables auto-expand the filter range as new rows are added, so you won't face the "filter not showing all values" problem after the next data update.

If you're working in Excel Online or a shared workbook, some merge-and-unmerge operations behave differently than in the desktop app. Do your structural cleanup in the Excel 365 desktop client before pushing changes to a shared file.

Unprotect the Sheet or Ungroup Worksheets to Re-Enable the Filter Button

1. To unprotect: Review → Unprotect Sheet. If it's password-protected and you don't have the password, that's a separate problem outside the scope of AutoFilter fixes.
2. To ungroup worksheets: right-click any sheet tab and select Ungroup Sheets. Your filter button should become active immediately.
3. Re-apply the filter with Data → Filter (or Ctrl+Shift+L) after making either change. Excel doesn't always restore it automatically.

For Excel filter not working in shared workbook situations specifically, check whether the workbook is using legacy sharing (Review → Share Workbook). Legacy shared workbooks in older Excel versions have significant AutoFilter limitations. Co-authoring in Excel 365 is the cleaner solution if your team is collaborating on the same file.

For more on the structural errors that cause filter and formula failures across workbooks, the Excel errors and troubleshooting examples guide covers the broader pattern.

---

Common Mistakes That Keep the Fix From Working — and What to Check Before You Give Up

Most "the fix didn't work" situations aren't failed fixes — they're incomplete ones. Three mistakes come up constantly.

The first is forgetting to re-apply the filter after cleaning the data. You remove the blank rows, feel good about it, and the filter still looks wrong — because you never turned it back on. Ctrl+Shift+L off, then on again. Two seconds.

The second is applying AutoFilter to a partial selection instead of the full table. If you had a cell in column B selected when you hit Data → Filter, Excel may have guessed the range incorrectly. Click inside the data, press Ctrl+A to select the whole range, then re-apply.

The third — especially common after a data refresh — is mistaking a stale filter for a broken one. If you're seeing Excel filter not working after update behavior, your criteria from the last filter session may still be active. Clear all filters with Data → Clear, then re-apply from scratch.

If your filter still isn't behaving after all of this, the fastest reset is: turn off AutoFilter entirely with Ctrl+Shift+L, select one cell inside the clean data range, then turn it back on. Excel re-detects the range from scratch.

If you're newer to Excel and this is your first encounter with AutoFilter troubleshooting, the Excel for Beginners starter guide covers how AutoFilter is supposed to behave before anything goes wrong — useful as a reference point.

---

Frequently Asked Questions