The Filter feature in MS Excel allows users to filter out a specific range of data from a worksheet. Using the Filter feature brings up the data according to the applied filter. When you enable the Filter feature, the given data set can be filtered using various types of filters such as, Filter by Color, Filter by values, and custom filters.

While applying a filter to a large-sized Excel file, users can unexpectedly encounter a scenario where the Excel Filter feature is not working. In such cases, errors in the data list, merged cells, etc. could be preventing you from applying the filter.

Let’s learn more about this problem and look at some possible ways to fix it.

Why is the Excel Filter Not Working? 

There could be many reasons why the Excel filter is not working in your workbook. Some of the common reasons are listed below –

  • Multiple blank rows in between the data
  • Incorrectly formatted column headings
  • Errors in the data list
  • Merged cells
  • Hidden rows or columns
  • Attempting to apply a filter to already filtered data
  • Incorrect usage of data format
  • Grouped worksheets
  • Worksheet protection is enabled
  • Improper data selection

Methods to Fix the ‘Excel Filter Not Working’ Issue

When the Excel Filter is not working, you could encounter two possible scenarios –

  • The Filter button is disabled or grayed out
  • The AutoFilter feature is not working properly due to file corruption.

In both of the above scenarios, you can use the methods mentioned below to resolve the underlying issues preventing you from applying filters to your worksheet.

Method 1: Delete Blank Rows from the Data

While applying a filter to a worksheet, Excel is looking for continuous data. If you apply a filter to a data set with blank cells or rows, it will result in inconsistent data. Therefore, check for blank cells or rows; delete them and try using the filter again (see here).

Method 2: Select the Data Range Properly

If you have selected an incomplete data range, i.e. selected the data from random cells, you will encounter problems while applying a filter. If you need to fix the Excel filter not working issue, apply the filter to a column or the entire worksheet.

select the date range properly

Method 3: Unprotect the Worksheet

If you have locked certain columns, then you will not be able to add a filter to them. A worksheet with locked columns, rows, or cells is known as a protected sheet. It is created to prevent unnecessary data edits or the application of filters.

unprotect the worksheet

Additionally, if the Excel filter is not working in a protected worksheet, then the Use AutoFilter option under the protected worksheet settings would be unchecked. If you are trying to apply a filter to a protected document, unprotect it and try again. To unprotect a worksheet –

  • Open the document.
  • Click on Review in the menu bar.
  • Click on Unprotect Sheet and add password. Click OK

unprotect sheet with password

Method 4: Remove Conflicting Excel Add-Ins

Excel add-ins are extended functionalities that allow users to perform more functions while using MS Excel. In some cases, these add-ins conflict with normal MS Excel functionalities. This can affect certain features like AutoFilter, PivotTable, etc., resulting in issues like the Excel filter not working.

To resolve such conflicts, you will need to launch MS Excel in safe mode and see if the issue persists. After that, manually check the add-ins. Here’s how to do it –

  • Press WINDOWS + R and type excel.exe /safe.

Press WINDOWS + R and type excel.exe /safe

  • Press Enter to launch MS Excel in safe mode.

If you are able to apply filters to your data, check the add-ins manually and remove the faulty ones to fix the Excel filter not working issue.

Method 5: Clear Previously Applied Filters

If there are filters already applied to an Excel worksheet, you will not be able to apply additional filters to it. Clear the existing filters from the document, and then retry using the Excel filter feature. Here’s how to remove the existing filters from an Excel worksheet –

  • Open the Excel document.
  • Click on Sort & Filter in the ribbon.
  • Click on Clear.

Method 6: Unhide Hidden Rows and Columns

If you have certain rows and columns hidden in your Excel worksheet, it could be the reason behind the Excel filter not working issue. You can look for the hidden columns or rows and unhide them. To unhide the hidden rows and columns –

  • Open the affected Excel worksheet.
  • Click on Home.
  • Click on Format in the Ribbon. An extended menu will open.
  • In the Visibility section, go to the Hide & Unhide option.

unhide columns excel

  • A side menu will open. Click on the Unhide Rows and Unhide Columns options (whichever is required).

Method 7: Unmerge Cells and Worksheets

Merged cells can cause issues while using formulas and applying filters. If you are trying to apply filters to a data range with merged cells, Excel will prevent you from doing it. If the Excel filter is not working on such a worksheet or data set, the simplest way to resolve the issue is to locate and unmerge the merged cells. To do this –

  • Select the merged cells.
  • Click on the downward arrow next to the Merge & Center option in the ribbon.
  • Click on Unmerge Cells.

unmerge cells

Sometimes, we group multiple worksheets together as it becomes easier to add headings and formatting to them. However, while applying a filter to grouped sheets, the Excel filter button doesn’t work and becomes gray. As the filter feature is applicable to individual worksheets, you will need to ungroup the worksheets before applying a filter to them. 

Here are the steps to follow–

  • Right-click on the grouped worksheets.
  • Click on Ungroup.

Method 8: Fix Errors

Another reason the Excel filter is not working is incorrect data format. If certain cells in a selected data range contain data in a wrong format, it will show a small green triangle in those cells.

fix errors

Along with inconsistent data formatting, you may also encounter the Excel filter not working issue due to incorrect cell references and formulas. Therefore, before applying filters to your worksheet, make sure that the data formatting, cell references and formulas are correct and that there is no green arrow in the cells.

Method 9: Repair the Corrupted Excel File

MS Excel file corruption could be a reason why the Excel filter is not working on your worksheet. File corruption or errors can be fixed using Excel’s built-in Open and Repair utility. This utility helps you repair Excel files showing signs of corruption. Proceed with the steps mentioned below to fix a corrupted Excel file –

  • Launch MS Excel.
  • Click on File
  • Click on Open > Browse.

open browse excel

  • Locate & select the problematic Excel file.
  • Click on the downward-pointing arrow next to the Open button.
  • Click on Open and Repair.

open and repair excel

  • Excel will show you a dialog box. Click on Repair as shown in the screenshot.

  • Once the file is repaired, click Close.

excel unreadable content

NoteIn cases of severe file corruption, the Open and Repair functionality might fail to repair the corrupt Excel file. In such a case, click on the Extract Data option to recover data and formulas in raw form.

Method 10: Use an Excel Repair Tool

Many times, you will be able to successfully repair a problematic Excel file using the Open and Repair utility. However, if the underlying issues are beyond the scope of this tool, you can try to repair corrupted Excel files using an advanced Excel repair tool like Stellar Repair for Excel.

An Excel repair tool is a specialized utility designed to repair corrupt, inaccessible, damaged, or unsupported Excel files. Using such a tool allows easy repair of Excel files of various file formats including .xls, .xlsx, .xltm, .xltx, and .xlsm. It also allows users to run multiple file repair processes simultaneously.

Additionally, Excel repair software also helps you recover data, objects and information from a severely corrupt or unreadable Excel file.

Wrapping it Up

Numerous reasons could account for the Excel filter not working issue in MS Excel. A filter issue could be caused by improper data selection, existing filters, empty cells, or file corruption. Such problems result in a non-clickable or disabled filter feature. However, in cases of file corruption, the filter might not work as intended, thereby preventing you from applying it.

A corrupted Excel file could also become inaccessible and cause data loss. Hence, if manual methods don’t seem to resolve the lingering issue, opt for Stellar Repair for Excel to repair problematic workbooks or extract data from them.