FILTER Function Not Working in Excel? Here’s How to Fix It

Excel’s FILTER function returns the filtered value from the selected array. Most users especially use this function over the Filter tool because of its wide functionality. For Instance, you could define multiple criteria, complex criteria, sort data, and many more to extract value. But, all of these features will be useless when the FILTER function does not work, right?

The main reason why your FILTER function is not working could be due to errors. You’ll encounter several types of errors when you haven’t met the criteria for the FILTER function. However, if the function does not work without any error, that’s a different case. No matter the reason, the issue is temporary and a few fixes can help troubleshoot it.

Types of Errors When FILTER Function is Not Working

FILTER function takes up an array to filter, a boolean, and an item to return when there is no value in the Array as function arguments. Here, entering the correct array is very important. So, you’ll mostly get these types of errors if there’s an issue in the function arguments.

• #CALC! Error: If you input a wrong array or cell range, it’ll result in #CALC! Error. This type of error will also arise when there is an empty array. 

• #NAME? Error, #VALUE! or #N/A Error: FILTER function will result in #VALUE!, #NAME, or #N/A Error when you have passed down a wrong value in the include argument. Or, when there’s an error in the argument. For example, it could be not specifying the criteria to filter out from the given array.

• #REF! Error: You’ll encounter #REF! Error while using the FILTER function if you’ve entered an invalid array in the formula to return the filtered item. It mostly occurs when you’re nesting the FILTER function with other functions.

To solve these errors, all you need to do is re-check the arguments you’ve entered in the formula. Then, make sure to enter the correct array and references in the arguments. 

Why is FILTER Function Not Working in Excel?

If FILTER Function is not working without any errors in Excel, it’s possibly because of the following reasons. 

• Not available in older Excel versions
• Empty cells in the sheet
• Merged Cells
• Hidden Rows and Columns

How to Fix FILTER Function Not Working in Excel?

According to Microsoft, the FILTER function is available for Excel 365, web, 2021, and 2019 versions. So, when you see only the FILTERXML function while typing FILTER, you might be using an older Excel version. 

For users who cannot access the FILTER function at all, check your Office version. Then, either upgrade the Excel to the latest version or try using the function in the web version. To check, launch Excel and head to the Account menu. Hover over Product Information and see the Office version.

Besides, if you can see the FILTER function but is not working, check out these fixes to troubleshoot the problem. 

Delete Empty Cells

First and foremost, delete any empty cells you have in your data. When there are blank cells in your data, Excel fails to select the values past the empty cells. So, even though you select an array to filter out the item, it might not have been included. It is best to cancel out any blank cells you have. 

1. Select your data.
2. Enter Ctrl + Shift + L shortcut key to turn on the Filter.
3. Click on the Filter button. Untick the box for Blanks and hit OK.
   
4. Try using the FILTER function again.

Unmerge Cells

Do you have merged cells in your data? When you merge cells, you must have noticed Excel discards the rest of the value and keeps only the first item. So, while using the FILTER function, you won’t be able to select all of the values within an array. To solve this, unmerge combined cells and try using the FILTER function again. 

1. Select entire data and go to Home Tab.
2. Hover over the Alignment group. Click on Merge & Center > Unmerge Cells.
   

Alternatively, you can also select the cells and enter the following keyboard shortcut to unmerge cells.  Remember, the following shortcut is an Excel Ribbon Shortcut. So, you do not have to press all keys simultaneously. Just enter the keys one by one. 

Shortcut key: Ctrl + Alt + H + M + U

Unhide Hidden Rows and Columns

If you have hidden rows or columns, Filter tools do not include them as an option in the first place. So, if you’re wondering why the FILTER function isn’t returning certain values, it’s probably because they’re hidden. In order for the FILTER function to work, you need to ensure all columns and rows are shown in your sheet. This is especially important when you select the array to filter values from.

1. On your sheet, click on Select All icon.
   
2. Right-click on the Row header and pick Unhide.
   
3. Again, while all cells are still selected, right-click on Column Header. Then, choose Unhide.
   

Quick Office Repair

In most cases, the FILTER function won’t work due to very minor reasons. So, it should have been solved by now. But, if the problem persists, we recommend you do a quick office repair. It’ll troubleshoot and repair issues within Excel that are interfering with the functions.

1. Enter Windows + I for the Settings window. 
2. Go to Apps > Apps & features.
   
3. Find Microsoft 365 or Microsoft Office in the list. Then, select More icon > Modify.
   
4. To continue, click Yes.
5. On Microsoft Window, pick Quick Repair and hit Repair.
   
6. Click Repair.
   
7. Launch Excel and use the FILTER function to see if it works now.