Excel Filter Not Working? 5 Reasons Why

Excel filter not working? Don’t worry. I’ve got you covered! Filtering is a fantastic tool for analysing data, but it can be frustrating when it doesn’t behave as expected. Here are five common reasons your filter might not work and how to fix them. Additionally, I’ve included bonus tips that could save you even more hassle!

1. You Haven’t Selected All Your Data

One of the most common issues is forgetting to select the full range of data. If your dataset has blank rows [1] or columns, Excel might stop the filter at the first gap. Always check you have included your entire dataset before filtering [2].

How to Fix It:

Step 1: If your data has empty rows or columns, manually select the range before you filter. Once the range is selected, turn on Filter. This will ensure the entire dataset is included in your filter.

Step 2: Remove blank rows before filtering:

• Turn on the filter by clicking the Filter button on the ribbon.
• Click the drop-down arrow in any column header.
• Uncheck Select All.
• Scroll to the bottom of the filter list and select Blanks.
• Click OK to highlight blank rows, then delete them.
• Turn the filter off and then on again. Now that your range is free from empty rows and columns, all data should be included in your Filter.

Tip: To quickly select the dataset, click on the cell in the top left corner, then Shift + click the cell in the bottom right corner of the dataset. Once the data is selected, use the keyboard shortcut Ctrl + Shift + L to quickly turn the Filter on and off. For a full list of Filter shortcuts, check out my blog, ‘6 Filter shortcuts in Excel to save you time’.

Super Tip: Convert your filter dataset into an Excel table to ensure it includes new data. Select your dataset (including empty rows) and press Ctrl + T. Click OK. Your data will now be formatted as a table with filter options already applied. As you add more rows and columns, they will automatically be included in your dataset.

 2. Your Column Headings Aren’t Set Up Properly

Excel filters work best with clear, single-row headings [1]. If your headings span multiple rows, you’ll encounter problems, and your filter may not work.

How to Fix It:

1. Ensure all column headings are in a single row.
2. If you require multi-line headings, place your cursor in the cell and press Alt + Enter to add a new line within the same cell. This solution will allow you to have two lines for a heading inside a single row.
3. Use the Wrap Text feature on the Home tab to wrap longer headings neatly within a cell.
4. Check that each column has a unique heading to avoid filter confusion.

3. There Are Merged Cells in Your Data

While merged cells can look great, they’re often the culprits behind filter issues. Filters struggle to process merged rows and columns.

If your column headings are merged, you may not be able to select items from one of the merged columns when you filter.

If you have merged rows, filtering will not pick up all the merged rows.

How to Fix It:

Step 1: Select the merged cells and unmerge them. On the Home tab, click the Merge & Center button. Click Unmerge Cells.

Step 2: Once the cells are unmerged, copy and paste data into individual cells to maintain consistency if necessary.

Step 3: Reapply the filter and test that all rows and columns are included.

4. Your Data Contains Errors

Errors like #DIV/0! or #VALUE! are a common reason why Filters do not work, especially with advanced filters like “Top 10” or “Above Average.”

How to Fix It:

Step 1: Use the filter to locate errors:

• Click the filter arrow and then uncheck Select All.
• Scroll to the bottom of the filter list and select only the error type (e.g., #DIV/0!).
• Click OK to apply the filter and highlight the errors.

Step 2: Once errors are visible, decide whether to correct or delete them:

• Correct errors by fixing formulas or updating references.
• Delete unnecessary error rows if they’re not needed.

Step 3: Clear the filter, and then reapply your original filter settings, e.g. Top 10.

Note: For your filter to work again, you may need to search each column to find and remove errors.

5. Hidden Rows Are Causing Issues

Hidden rows don’t appear in the filter options, which can be very confusing when trying to find specific data in the filter menu. It may seem that the Filter isn’t working, but it is; it’s just that the data is hidden.

How to Fix It:

Step 1: First, check if your data has hidden rows. To do this, select over the range you want to filter and then press ALT + ; (ALT + semicolon). An on-screen line will depict where rows are hidden.

Step 2: Select the rows above and below the hidden data.

Step 3: Right-click the selected area and choose Unhide. Alternatively, go to Home > Format > Hide & Unhide > Unhide Rows.

Step 4: Check that all rows are visible before applying your filter.

Bonus Tip: Check for Grouping and Protection

Your filter button might sometimes be greyed out because your sheets are grouped or protected.

Here’s how to fix grouped sheets:

1. Check the top of your workbook for “[Group]” in the title bar.
2. Right-click any sheet tab and select Ungroup Sheets.
3. Test the Filter button again; it should now be available.

Here’s how to fix protected sheets: Click the Review tab. Your worksheet is protected if you see the Unprotect Sheet button in the Protect group.

Step 1: Click the Unprotect Sheet button to unprotect the worksheet. If Excel prompts you for a password, enter the password and then click OK. If you don’t have the password, check out my blog, ‘How to Unprotect Excel Workbook & Excel Worksheet (inc Without Password)’.

Step 2: Test the Filter button again; it should now be available.

Bonus Tip: Excel file is slow to refresh when Filtering

If your file slows down and shows the ‘whirly-wheel’ for several seconds, ensure you only selected the table area before applying Filter, not entire rows, columns, or the whole worksheet. If you’ve used Filter on multiple tables, consider removing it from those you aren’t using to avoid slowdowns.

Extra Tips to Keep Your Filter Working

• Check for active filters on other columns: You won’t see all your data if filters are still applied on different columns. The best way to check if this is why your filter is not working is to click the Clear button on the Data tab. This will remove all filters, allowing you to start with the complete data set again.

• Remove Special characters or trailing spaces: Unwanted characters and extra spaces in the data can lead to unexpected filter results. If necessary, clean your data by removing any unwanted characters or spaces using the ‘CLEAN’ and ‘TRIM’ functions.
• Equals Filter isn’t working: If you’re using a Number Filter or Date Filter and the Equals filter isn’t returning the correct data, check that your data format is the same as the one you are entering into the Equals filter query. For example, if your column is formatted “16-Jan-25” and you are filtering using a different format, e.g., “16/01/2025” or “01/16/2025”, the filter may not work.

Still Struggling?

Filter is a helpful tool, and I hope these tips get you back on track. If you still have issues, check out my video for a detailed walkthrough of these troubleshooting steps. Let me know in the comments which tip helped you the most!