Why Your Excel Filter Is Not Working (and the Fix)

When an Excel filter is not working, the filter itself is rarely broken. The cause is almost always the data or the range: a blank row that cuts the list short, new rows added after the filter was set, values with hidden spaces, or a sheet that is protected or grouped. This guide goes through each symptom, explains why it happens and shows the fix. It is for anyone whose filter hides the wrong rows, misses rows, or won't turn on at all.

Quick checklist: find your symptom#

Match what you see to the likely cause, then jump to the fix below.

Rows are missing from the filter#

Excel decides which rows to filter by looking for the edge of your data. A fully blank row looks like the end of the list, so everything below it is left out. The rows below don't hide when you filter, and their values don't appear in the filter list.

Fix it in one of three ways:

1. Delete the blank rows. The guide to deleting blank rows shows a quick way to do it for many rows at once.
2. Select the whole range first. Select every row, including the ones below the gap, then turn the filter off and on again with Ctrl+Shift+L (Mac: ⌘⇧F).
3. Make it a table. Click in the data and press Ctrl+T (Mac: ⌘T). A table knows its own size, so filters always cover every row.

Merged cells cause similar trouble: a filter may show only the first row of a merged block. Unmerge them and fill the value into each row.

The filter doesn't update after you add or edit rows#

A filter is a snapshot. It checks the rows when you set it, not every time the data changes. Two things follow:

• Edited rows stay visible. If you change an amount so it no longer matches, the row stays on screen until you reapply.
• New rows may be left out. Rows typed directly under a plain range are often included, but rows pasted further down, or after a blank row, are not.

To reapply, press Ctrl+Alt+L on Windows, or click Data > Reapply on a Mac. For lists that grow, an Excel table is the best fix, because new rows join the table and its filter automatically. The guide to Excel tables with Ctrl+T explains how, and the filter shortcut guide lists the other keys.

The same value appears twice in the filter list#

If "North" appears twice in the filter list, the two values are not the same to Excel. The usual cause is an extra space: "North" and "North " (with a space at the end) are different text. Ticking one hides the other.

To see how big the problem is, count the real matches. =COUNTIF(A2:A5,"North") returns 1, but this formula trims each cell first:

=SUMPRODUCT(--(TRIM(A2:A5)="North"))

It returns 3. The -- turns TRUE and FALSE into 1 and 0, and SUMPRODUCT adds them. To fix the data, add a helper column with =TRIM(A2), fill it down, and paste it over the original as values. Then reopen the filter: only one North remains. The TRIM function guide covers the details, and you can practise cleaning text in a live sheet. For other invisible characters, see how to clean messy data.

Case doesn't cause this problem: the filter treats "north" and "North" as the same item.

Number or date filters are missing#

When the filter menu shows Text Filters for a column of numbers or dates, Excel sees text, not numbers. This happens with imported data, where 120 is stored as the text "120" and 03/04/2026 is text, not a date. Number filters such as "Greater Than" won't work, and dates don't group into years and months.

Signs of text: the values sit on the left of the cell, a small green triangle appears in the corner, or the status bar shows a Count but no Sum when you select them.

To fix numbers, select the column, click the warning icon and choose Convert to Number, or use =VALUE(A2) in a helper column. For dates, Data > Text to Columns > Finish often converts them in place. Then reapply the filter.

The Filter button is greyed out#

A greyed-out Filter button means Excel won't let you filter right now. Check these in order:

1. You are editing a cell. Press Enter or Esc to leave edit mode.
2. Several sheets are grouped. If the title bar shows [Group], right-click a sheet tab and choose Ungroup Sheets.
3. The sheet is protected. Go to Review > Unprotect Sheet. If you need protection, protect it again and tick Use AutoFilter in the list of allowed actions. That option lets people use filter arrows that are already on, but they can't add new ones.
4. The file is open in a limited view, such as Protected View or a read-only preview. Click Enable Editing.

Your totals include hidden rows#

Filtering hides rows, but SUM still adds them. If your total doesn't change when you filter, that is why. Use SUBTOTAL instead:

=SUBTOTAL(109,C2:C7)

The 109 means "sum, ignoring hidden rows". Function 9 also sums and also skips rows hidden by a filter; the difference is that 109 skips rows hidden by hand too. Other useful codes are 103 for counting non-blank cells and 101 for an average. The SUBTOTAL function guide lists them all.

If rows are hidden but no filter is showing, check every header for a filter icon: another column may still be filtered. Clear all filters with Data > Clear (Windows keys: Alt, A, C). Rows hidden by hand need Home > Format > Hide & Unhide > Unhide Rows.

Practise it#

Filters behave well on clean data with no gaps, so the real skill is preparing the data. Practise filtering a table in a live spreadsheet, then practise cleaning text with TRIM: you type each step and ExcelReady checks it instantly. The full guide to filtering data in Excel covers every filter option once your data is clean.