Excel Filter Not Working? Try These Fixes

If you’re dealing with a rather large database, then the filter function on Excel can come in handy. This function allows you to sort the dataset according to your set conditions. As a result, it makes analyzing and summarising data much easier. 

However, there might be instances where the filter function might not be working properly. Usually, such an error pops up when there are blank cells within the dataset. So, a quick fix to the issue is to delete these cells. 

But, what if the issue persists? In this article, let’s learn more about the potential causes and the fixes for the filter function not working on Excel. 

Why is My Excel Filter Not Working?

Here is a list of the potential causes of why the filter function might not be working in your Excel application. 

How to Fix Excel Filter Not Working?

Now that we know have listed out the causes, let’s jump right in with applying the troubleshooting methods for this issue.

Select All Data From Sheet 

If your dataset has blank rows or columns, it might not select the area (row or column) below or past these cells. As a result, Excel will fail to filter out all the data in your dataset. 

To be on the safe end, we suggest that you manually select the data. Now, click on the Filter ribbon and choose how you would like to arrange it. This method should be able to include all the datasets.

Remove Blank Rows/Columns 

Alternatively, you can also remove the blank rows by configuring the data on your Filter area. Here is how you can do so. 

1. Head over to the column that showcases the filter list. 
2. Click on the drop-down arrow and uncheck the box for Select All.
3. Scroll further down, and check the option for Blanks.
4. Hit the OK button to save filter changes.  

You can also manually delete these cells. To do so, right-click on the row adjacent to the blank cell. Select the Delete option to remove these blank rows/columns. 

Unhide Rows or Columns 

If you have hidden rows or columns on your dataset, then Excel won’t apply the Filter function on these data cells. You will have to unhide any hidden rows or columns from your sheet. 

Here is how you can do it.

1. Spot the hidden row/column.
2. It should represent a double line inside the row headings. Skipped order of number or alphabets is another sign of how to recognize hidden rows/columns.  
3. Select both the adjacent row/columns.
4. Navigate to the Home tab and click on Format. 
5. From the drop-down menu, select Hide & Unhide.
6. Finally, click on Unhide Rows or Unhide Columns.    

Unmerge Cells 

Merged cells in your dataset can also create problems while you’re trying to filter your table. Excel tends to disregard the data on merged rows/columns. So, we suggest that you first unmerge all data cells before using the filter option.

 Here is a step-by-step guide on how you can do it. 

1. Click on the Merged Cell on your sheet. 
2. Under the Home tab, navigate to the Alignment section. 
3. Click on Merge and Center, and from the drop-down menu, select the Unmerge cells option. 

Remove Data Errors 

If there are data errors in your table, then the filter function might not work on your data set. So, It is best to remove these data errors from the table.

 Here is a step-by-step guide on how you can do it. 

1. Head over to the column that showcases the filter list. 
2. Click on the drop-down arrow and uncheck the box for Select All.
3. Scroll further down, and check the option for #Value!
4. Hit the OK button to save filter changes.  

Clear and Add Filter

In some rare cases, the current filter you’re setting up might not work on your data set. So, a quick troubleshooting method you can apply is to clear the previous filter and set up a new one. 

Here is how you can do it.  

1. Head over to the Data menu. 
2. Click on the Clear icon right next to the Filter button. 
3. Select the column heading or the dataset.
4. Now, click on the Filter button. 

How to Fix Filter Grayed Out in Excel?

Here are a few effective methods you can apply when the filter option is grayed out on your Excel Application.  

Ungroup Sheets 

If you have selected more than one sheet, then Excel will not give you access to the Filter function. First and foremost, you will have to ungroup the sheets. To do so, right-click on a sheet from the bottom bar, and click on the Ungroup Sheets option.

Unprotect the Sheet 

Another factor you should look out for is to check whether your sheet is protected or not. When you’re sheet is protected, Excel automatically greys out the Filter option. So, to unprotect the sheet, 

1. Open up the Excel document.
2. Head over to the Review tab on Excel.
3. Click on the Unprotect Sheet option. 
4. Now, a pop window will appear, type in your password for the sheet. 
5. Finally, click on the OK button to confirm your action.

Related Questions 

Why is my Filter by color not Working?

If your filtering by color is not working on your spreadsheet, then there are two reasons why it might occur. The first is if there is only one specific color in all your cells or if you have shared the workbook. To unshare, head over to Review > Unshare Workbook.  

How to fix Excel Not Grouping Dates in Filters?

In some instances, the grouping dates in filters might get disabled. So, here is a step-by-step guide on how you can enable it. 

1. Head over to the File menu.
2. Select Options.
   
3. From the left panel, head over to the Advanced tab. 
4. Under the Display Option for this Workbook section, check the option for Group Dates in the AutoFilter menu.