The Ultimate Guide: Fix Filter Not Working in Excel Issue in Seconds!

Microsoft Excel offers various outstanding tools and features that help users to organize, analyse, and manipulate data. One of its most commonly used features is “Filter”, It allows users to narrow down large datasets & focus on specific information. However, users sometimes come across the filter not working in Excel, causing frustration and hindering efficiency. In this blog, I will explore why the Excel filter is not working and how to solve it effortlessly.

So, let’s get started…

To fix damaged Excel workbook, we recommend this tool:

This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:

  1. Try Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
  2. Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
  3. Preview the repaired files and click Save File to save the files at desired location.

Importance of Excel Filters

filter not working in excel

Well, Excel filters enable users to show only the data that meets specific criteria, hiding rows that don’t match the filter settings. Filtering the data in the large dataset’s spreadsheet helps to simplify the complex datasets, facilitate quick analysis, and make data-driven decisions.

Why Is My Filter Not Working in Excel?

The filter option not working in Excel mainly happens when applying the filter function on a protected worksheet. Apart from this, some other causes can lead to this problem:

  1. Stipulating incorrect criteria in the columns of the filter.
  2. The blank row within the spreadsheet.
  3. Corrupted Excel workbook.
  4. The data you’re attempting to filter is in merged cells.
  5. Grouped sheets in the file.
  6. Trying to apply the filter on an unacceptable data range.

What to Do If Filter Function Not Working in Excel?

Let me tell you very clearly that the filter option fails to work under two different situations:

All you need to do is to follow the below solutions according to your current situation.

Situation 1- Filter Function Isn’t Valid or Not Working

Way 1- Clearing the Applied Filters

If you have previously applied the filter option in your Excel worksheet but forgot to remove it, then it could be the reason behind this problem. You can simply clear out the applied filters by following these steps to solve the problem:

  • In the Excel document, go to Home tab.
  • Then, click on the ‘Sort & Filter’ option.
  • Now, select the Clear.

Sort & Filter

Way 2- Unmerge Merged Cells

Another reason for your filter in Excel not working is because of the merged cells. So, consider unmerging if you have any merged cells in the spreadsheet.

If the column headings are being merged, then the Excel filter becomes unable to choose the items present from the merged columns. The same thing happens with the merged rows. Excel filter won’t count the merged rows of data.

For unmerging the cells:

  • Go to the Home tab and then go to Alignment group.
  • Now hit the down arrow present across Merge & Center and choose the Unmerge Cells option.

Unmerge Cells

Also Read: Excel Freeze Panes Not Working Fixes- 2024 Guide!

Way 3- Check & Delete Blank Cells

Well, if your spreadsheet contains empty rows or columns or if you only want to filter out the specific range, choose the section that you want to filter before turning on the Filter. If you don’t choose the area then Excel applies the filter on the complete one. This can also result in selection up to the first empty column or rows excluding the data present after the blanks.

So it will be better if you manually select the data in which you want to apply the Excel filter function.

  • To remove the blank rows from the selected filter area.
  • First of all turn on the filter and then click on the drop-down arrow present in any columns to show the filter list.
  • Now remove the check sign across the ‘(Select All)’ and then shift right to the bottom of the filter list.
  • Choose the ‘(Blanks)’ option and tap to the OK.

Excel-Filter-Blanks

  • After this only, the blank rows will appear on your screen.
  • For easy identification, the row number of each blank row appears in blue color.
  • For deleting up these blank rows. You need to select these rows first and then right-click over any one of the blue color row numbers. Choose the Delete option from the listed option to delete the selected blank rows.
  • By turning off the filter you can see the rows that are now been removed.

Way 4- Check For Errors to Fix Filter Not Working in Excel 

Check carefully that your data doesn’t include any errors. Suppose, if you are filtering the ‘Top 10‘, ‘Above Average‘ or ‘Below Average‘ values within your list then using the Number Filters gets compulsory. Otherwise, the error may hinder your Excel application from applying the filter.

  • For removing up the errors use the filters to fetch them. Usually, they get listed at the list’s bottom so scroll down.
  • Choose the error and tap to the OK option. After locating up the error, fix or delete it and then only clear up the filter.

Way 5- Check The Column Headings

Check the data which has only one row of column headings. To get multiple lines for the heading, into the cell, you just need to type the first line. After that press “ALT + ENTER” option to type in the new line of the cell.

In such cases, the Wrap Text function also works great in formatting the cells correctly.

wrap text

Also Read: Resolve Excel Flash Fill Not Working Issue Easily!

Way 6- Repair Corrupted Workbook

The corruption in the workbook is another foremost reason for the filter option not working in Excel. In such a situation, you can use a free built-in Microsoft Open and Repair utility to solve the file corruption issue.

Here are the complete steps to use this tool:

  • Go to the File Open.
  • Navigate to the folder to choose the corrupted workbook.
  • Under the Open dialog box, choose the corrupted XLS/XLSX file.
  • Then, click the arrow next to an Open button >> click Open and Repair.

filter not working in excel

  • Finally, click on “Repair” button to recover as much of the workbook data as possible.

Open and Repair

Important Note: If your Excel file is severely corrupted, then Open and Repair tool can’t fix it. In such a case, you should immediately opt for an advanced Excel Repair Tool. It can easily repair severely damaged or corrupted workbook and recover all the data saved within it.

Way 7- Check for The Hidden Rows & Column

On the Excel filter, list hidden rows won’t appear as the filter option. For unhiding the rows or columns firstly, you need to choose the area having the hidden rows/columns. This means you have to choose the cells present below or above the hidden data.

After that, you can make a right-click over the rows header area. Or you can choose the Unhide option. OR just go to the Home tab > cells group> FormatHide & Unhide> Unhide Rows and Unhide Column option.


Situation 2- Filter Option Greyed Out in Excel

Way 1- Unprotect the Worksheet

Many users have reported that greyed-out filter in Excel happens due to protected worksheets. And they have even fixed their issue by unprotecting the sheet. You can also do so to overcome this situation.

Follow these steps:

  1. Go to the Review tab.
  2. Then select Unprotect Sheet.

Unprotect Your Excel Worksheet

Also Read: Fix Arrow Keys Not Working in Excel [2024]

Way 2- Ungroup Sheets

If your Filter function button is appearing gray then you can check out whether your worksheets are grouped or not.

You can easily identify it by viewing the title bar where generally the filename appears at the screen top. If the ‘Your file name’ – Group is appearing, it means that your worksheet is grouped and needs to be ungrouped.

  • So come down to your worksheet and make a right-click on the sheet tab.
  • After that choose the Ungroup Sheets option.

Ungroup Sheets

  • You will see, after making these changes again the filter option starts appearing.

Way 3- Disable the Faulty Add-ins

Sometimes, faulty add-ins can also contribute to such issues in the spreadsheets. In that case, disabling or removing the add-ins from Excel can assist you in tackling the problem efficiently.

To know how to disable or remove Excel add-ins, Click Here.

What if The ‘Equals’ Filter Isn’t Working?

While using the Equals filter, Number Filter, or Date Filter, if your Excel is not showing the right data then check whether the format of your data is the same or not.

Suppose, if you have 2 cells and in each cell, you have entered 1000 as data. One cell is formatted in “currency” format and another one is formatted in “number” format. So when you make use of the “Number FiltersEquals” option, then Excel will only fetch the matches in which you have typed the number format.

Only typing the text 1000 will search the match for the cells only in the number format. Whereas typing the text $1,000.00 will look for the cells which are formatted as ‘Currency’. The same rule is also applicable in the case of dates. 16-Jan-19 and 16/01/2019 both are different. Thus make sure that the complete data column is formatted in the same format to avoid filter function not working in Excel issue.

Related FAQs:

Why Is My Filter Option Grayed Out in Excel?

Your filter option might grayed out in Excel spreadsheet due to the absence of active cells within a suitable data range for filtering.

How Do I Reset Filters in Excel?

To reset applied filters in the Excel worksheet, you have to navigate to the Home tab >> under the Editing group, simply click on Sort & Filter >> Clear.

Can Complex Formulas Impact Excel Filter Performance?

Yes, using complex formulas within the spreadsheet can slow down filter responsiveness.

What Is the Formula for Filter in Excel?

The formula for filter in Microsoft Excel =FILTER(A5:D20,C5:C20=H2,’)

Are There Any Alternative Filtering Methods in Excel?

Yes, some alternative filtering methods that can be used in Excel are- ‘FILTER,’ ‘UNIQUE,’ and ‘SORT’.

Does Excel Filter Have a Limit?

Yes, Excel Filter only for works 10000 unique values.

Can Hidden Rows/Columns Affect Excel's Filter Function?

Absolutely, hidden rows/columns within the dataset can hinder the filter function, triggering issues with filtering accuracy.

Final Verdict

As already said, Excel’s filter option is a valuable tool, but it’s not protected from issues. However, by going through this blog, you must have learned to fix the Excel filter function grayed out or filter not working in Excel issues and optimize your workflow efficiently.

Furthermore, if you have any queries, you can ask on our Facebook and Twitter pages.



Priyanka is a content marketing expert. She writes tech blogs and has expertise in MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in the easy-to-understand language is very impressive. When not writing, she loves unplanned travels.