Excel Filter Not Working? Try These Fixes

Excel Filter Not Working? Try These Fixes.

If you’re coping with a moderately giant database, then the filter perform on Excel can turn out to be useful. This perform lets you type the dataset in accordance with your set situations. As a outcome, it makes analyzing and summarising knowledge a lot simpler. 

However, there may be cases the place the filter perform may not be working correctly. Usually, such an error pops up when there are clean cells inside the dataset. So, a fast repair to the difficulty is to delete these cells. 

But, what if the difficulty persists? In this text, let’s be taught more concerning the potential causes and the fixes for the filter perform not engaged on Excel. 

Why is My Excel Filter Not Working?

Here is a listing of the potential causes of why the filter perform may not be working in your Excel utility. 

  • Blank or Hidden Rows between the dataset
  • Vertical Merged Cells
  • Data Value Error
  • Selected a couple of worksheet (grouped)
  • The worksheet is protected

How to Fix Excel Filter Not Working?

Now that we all know have listed out the causes, let’s bounce proper in with making use of the troubleshooting strategies for this problem.

Select All Data From Sheet 

If your dataset has clean rows or columns, it may not choose the realm (row or column) beneath or previous these cells. As a outcome, Excel will fail to filter out all the information in your dataset. 

To be on the protected finish, we propose that you simply manually choose the information. Now, click on on the Filter ribbon and select the way you want to prepare it. This methodology ought to have the ability to embody all of the datasets.

Remove Blank Rows/Columns 

Alternatively, you may as well take away the clean rows by configuring the information in your Filter space. Here is how you are able to do so. 

  1. Head over to the column that showcases the filter checklist. 
  2. Click on the drop-down arrow and uncheck the field for Select All.
  3. Scroll additional down, and examine the choice for Blanks.
  4. Hit the OK button to avoid wasting filter modifications.Exclude blanks   

You may also manually delete these cells. To achieve this, right-click on the row adjoining to the clean cell. Select the Delete choice to take away these clean rows/columns. 

Unhide Rows or Columns 

If you’ve gotten hidden rows or columns in your dataset, then Excel received’t apply the Filter perform on these knowledge cells. You should unhide any hidden rows or columns out of your sheet. 

Here is how you are able to do it.

  1. Spot the hidden row/column.
  2. It ought to signify a double line contained in the row headings. Skipped order of quantity or alphabets is one other signal of how to acknowledge hidden rows/columns.  
  3. Select each the adjoining row/columns.
  4. Navigate to the Home tab and click on on Format. 
  5. From the drop-down menu, choose Hide & Unhide.
  6. Finally, click on on Unhide Rows or Unhide Columns. Unhide Rows and Columns    

Unmerge Cells 

Merged cells in your dataset may also create issues when you’re making an attempt to filter your desk. Excel tends to ignore the information on merged rows/columns. So, we propose that you simply first unmerge all knowledge cells earlier than using the filter possibility.

 Here is a step-by-step information on how you are able to do it. 

  1. Click on the Merged Cell in your sheet. 
  2. Under the Home tab, navigate to the Alignment part. 
  3. Click on Merge and Center, and from the drop-down menu, choose the Unmerge cells possibility.Unmerge Cells  

Remove Data Errors 

If there are knowledge errors in your desk, then the filter perform may not work in your knowledge set. So, It is best to take away these knowledge errors from the desk.

 Here is a step-by-step information on how you are able to do it. 

  1. Head over to the column that showcases the filter checklist. 
  2. Click on the drop-down arrow and uncheck the field for Select All.
  3. Scroll additional down, and examine the choice for #Value!
  4. Hit the OK button to avoid wasting filter modifications.  

Clear and Add Filter

In some uncommon circumstances, the present filter you’re establishing may not work in your knowledge set. So, a fast troubleshooting methodology you possibly can apply is to clear the earlier filter and arrange a brand new one. 

Here is how you are able to do it.  

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

How to Fix Filter Grayed Out in Excel?

Here are just a few efficient strategies you possibly can apply when the filter possibility is grayed out in your Excel Application.  

Ungroup Sheets 

If you’ve gotten chosen a couple of sheet, then Excel is not going to offer you entry to the Filter perform. First and foremost, you’ll have to ungroup the sheets. To achieve this, right-click on a sheet from the underside bar, and click on on the Ungroup Sheets possibility.

Unprotect the Sheet 

Another issue it is best to look out for is to examine whether or not your sheet is protected or not. When you’re sheet is protected, Excel mechanically greys out the Filter possibility. So, to unprotect the sheet, 

  1. Open up the Excel doc.
  2. Head over to the Review tab on Excel.
  3. Click on the Unprotect Sheet possibility.Unprotect Sheet  
  4. Now, a pop window will seem, sort in your password for the sheet. 
  5. Finally, click on on the OK button to substantiate your motion.

Related Questions 

Why is my Filter by shade not Working?

If your filtering by shade isn’t working in your spreadsheet, then there are two explanation why it’d happen. The first is that if there is just one particular shade in all of your cells or if in case you have shared the workbook. To unshare, head over to Review > Unshare Workbook.  

How to repair Excel Not Grouping Dates in Filters?

In some cases, the grouping dates in filters may get disabled. So, here’s a step-by-step information on how one can allow it. 

  1. Head over to the File menu.
  2. Select Options.
    ExcelOptions
  3. From the left panel, head over to the Advanced tab. 
  4. Under the Display Option for this Workbook part, examine the choice for Group Dates within the AutoFilter menu.Group Dates in the Auto Filter Menu  


Check out more article on – How-To tutorial and latest highlights on – Technical News