How to Solve 'Filter Not Working' or Enable Filter in Microsoft Excel

How to Solve "Filter Not Working" or Enable Filter in Excel
Filter plays an important role in our analysis. But sometimes filter doesn’t work properly. Sometimes it doesn’t work after certain rows and sometime its completely disabled. In this tutorial we are going to fix filter not working issue along with explain some reason of why Excel sort and filter icon can be grayed out.

There are two types of problem you may encounter with excel filter.
1. You have the filter button but it doesn’t work properly.
2. Your filter option is disabled.

Now let’s solve the first option, “Filter not working”.
One of the most occurred reasons for your filter to not work properly or filter not to work after certain row is blank row. If you have blank row in your data there is a strong chance it will not work. But you can fix this. All you have to do is to select the entire data range manually including the blank row. Then you apply the filter and it will work. Usually if we click on filter without selecting the entire range excel will automatically select the data range for us. And it will only select until it finds the blank row. This is why excel cannot filter entire column if it has blank in it.

The second Reason is hidden row. If you have a hidden row in your data your filter will not work. The main problem it occurs is when you try to select the item from the filter but that item row is hidden. You’ll not find it inside the filter item list and therefore you won’t be able to filter data based on that value. So, you mustn’t have hidden row inside your range.

The third reason is merged cell. It could be merged row or merged column. It doesn’t matter and your filter will now work properly if you have those inside your data range. Make sure you unmerge those cells before you apply filter.

Another problem you might encounter is when you search data to filter. Sometimes you write something that is already in the data range but you can’t find it in the filter list. It could happen if you don’t match the formatting. It mainly happens with data and currency. Suppose your date range has a formatting like this 8-OCT and you are searching like this 8/10 it will not work. Also, if you have $10 in your range and you are searching 10 without $ sign, it will not work.

Now let’s address the second problem. Excel filter option is disabled or grayed out. There are three reason that can disable your filter button. Let’s learn how to enable filter button in excel.

The first one is grouped sheet. If your group your worksheet, filter option will be disabled. Usually, we group worksheet by pressing control and clicking on the sheet name. We can also ungroup it in the same way. So, if you have group sheet try ungrouping them to enable filter option.

Sometime we select all data and filter button becomes grayed out or disabled. This could happen if your data is partly in table and partly without table. You can solve this by converting the whole range into table or the whole table into range.

The third reason of disabled filter button is protected worksheet. If your worksheet is protected your filter button will not work. If you unprotect your worksheet your filter button will be enabled again.

All these could lead your shortcut keys for filter also not working. If you solve these you shortcut to filter will start working again. This is how you solve "Filter not working" problem or enable filter in Microsoft Excel.

#Excel #Filter #solved
Thanks for watching.
----------------------------------------------------------------------------------------
Support the channel with as low as $5
----------------------------------------------------------------------------------------
Please subscribe to #excel10tutorial

Here goes the most recent video of the channel:

----------------------------------------------------------------------------------------
Recommend WPS Office-Best FREE alternative to Microsoft Office, Download for Win & Mac & Mobile.
----------------------------------------------------------------------------------------

Playlists:

Social media:

[Exceltutorial]

Recommend WPS Office-Best FREE alternative to Microsoft Office, Download for Win & Mac & Mobile.

[farahramzi]

Keep searching about the filter disabled but didn't get my answer until I found your video. Thank you so much!! you just save my life 😀

[zh]

Extremely helpful. Not sure how I grouped worksheets, but that was my issue, . Thank you for being thorough and covering both reasons for a greyed out Filter Icon!

[EnlightSpokenEnglish]

You saved my time. I was not selecting blank rows. Thank you very much for your valuable video.

[naniki]

thankyou!! i didnt realize i grouped my worksheets, but there it is, grouped AF... you helped me!!

[jessellvillarin]

Thank you so much, it helps a lot. :)

[anupamchakraborty]

This video is of great help !! Thanks a lot !! ❤

[mobarokhossain]

I have been facing a problem on filtering. After watching your video i can solved it now.

[arturoortega]

Amazing! Thanks for the help, crack.

[anoogralol]

Omg thank you so much!! U r my savior haha

[onlyviralvideos]

filter in excell not work because of part of an array.
sir how to solve this?

[rohitrajrajput]

Sir i want to learn excel from basic till advanced. If i start watching your videos (starting from the oldest one) till this one, how much will i be able to cover excel?

[yashpal]

Ek problem aa rhi h excel me pls apna koi number de jisko excel aata ho

[yashpal]

Excel expert apna contact number do.. koi