Are you are having a few hassles when filtering?
Is the filter not working properly or as you would like it to?
Here are some reasons why your Excel filter may not be working.
Excel has an expectation that you have prepared your data to meet some basic layout standards before you use filter. Get these right and you will minimize filtering hassles.
Check that you are following these Filtering rules…
1. Check that you have selected all of the data
If your data has empty rows and/or columns or if you are only wanting to filter a specific range, select the area you want to filter prior to turning Filter on.
Failing to select the area leaves Excel to set the filter area. This could lead to Excel selecting only up to the first empty row or column, excluding additional data past these points.
It’s much better to manually select to be sure you have all of the data included.
Extra Note: if you would like to remove blank rows from the filter area just turn on filter, click a drop-down arrow on any column to display the filter list. Remove the check mark from Select All and then scroll right to the bottom of the filter list. Select Blanks and click OK.
Now only blank rows will be displayed.
You can easily identify the rows as the row number will now be coloured blue.
To delete the blank rows just select them and then right-click over the top of one of the row blue numbers. Select Delete to delete the rows.
Turn filtering off and you will see that the rows have now been removed.
2. Check your column headings
Check your data has just one row of column headings.
If you need multiple lines for a heading, just type the first line into a cell and then press Alt + Enter to type on a new line within the cell.
Formatting the cell using Wrap Text also works.
3. Check for merged cells
Another reason why your Excel filter may not be working may be due to merged cells.
Unmerge any merged cells or so that each row and column has itโs own individual content.
If your column headings are merged, when you filter you may not be able to select items from one of the merged columns.
If you have merged rows, filtering will not pick up all of the merged rows.
4. Check for errors
Check your data doesn’t include errors. For example, if you were trying to filter on the ‘Top 10’, ‘Above Average’ or ‘Below Average’ values in your list (use Number Filters to do this), and error may stop Excel from applying the filter.
To remove errors first use Filter to find them. They are always listed at the bottom of the list so scroll right to the bottom.
If you see one (or many) remove the check mark from Select All at the top of the list first and then scroll back to the bottom of the list.
Select the error and click OK. Once you have located the error, fix it or delete it and then clear the filter. No go ahead and use Top 10, Above or Below Average and you’ll be back in business!
5. Check for hidden rows
Hidden rows arenโt even shown as a filter option on the filter list.
To unhide rows first select the area containing the hidden rows. This may mean you need to select the row above and below the hidden data.
Then either right-click in the row header area (over the row numbers) and select Unhide or from the Home tab select Format, Hide & Unhide, Unhide Rows.
Extra checks and info…
Check for other filters
Check that a filter hasn’t been left on another column.
The best way to clear all of the filters is to click the Clear button on the Ribbon (to the right of the Filter button).
This then leaves Filter turned on, but removes all filter settings allowing you to start again with the full set of your data.
The Filter button is greyed out
If the Filter button is greyed out check that you don’t have your worksheets grouped.
You can tell if they are simply by looking at the title bar where the filename is shown at the top of the screen.
If you can see ‘Your file name’ – Group you currently have worksheets that are grouped.
Just make your way down to one of your worksheets, right-click the sheet tab and then select Ungroup Sheets. The Filter button will now be available.
The ‘Equals’ filter isn’t working
If you’re using the Number Filter or Date Filter, Equals filter and Excel isn’t returning the correct data, check the formats on your data are the same.
For example, if you have 2 cells with 1,000 entered into each and one cell is formatted with the Currency format and one with the Number, when you use the Number Filters, Equals option, Excel will only find matches where you type the format for the number as well.
Just typing 1000 will find a match for the cell formatted with a Number format only. Typing $1,000.00 will find the cell formatted as Currency.
This is the same for dates. 16-Jan-19 will not match 16/01/2019. Therefore, make sure the entire column of data is formatted with the same format to avoid this problem.
My file is really slow to refresh
If you are finding that your file is starting to become very slow at responding, i.e. the ‘whirly-wheel’ is displayed for more than a few seconds every time you update your file, check that you only selected the table area before you applied Filter, not entire rows, entire columns or the entire worksheet.
Also, if you have applied Filter to multiple tables within the file you might like to remove Filter from any tables you aren’t working with. This may also help eliminate ‘whirly-wheel’ moments.
I hope one of these tips has seen you back on track again. There are so many reasons why your Excel filter may not be working. However, these are typically the ones I come across the most.
Watch the Excel fix filtering problems tutorial
Watch on YouTube] / [Subscribe to our YouTube Channel]
Was this blog helpful? Let me know in the comments below.
Was this blog helpful? I’m here to empower your journey with Excel, aiming to make your daily tasks more efficient and boost your potential.
Share your thoughts in the Comments below – your insights not only enrich others, they also help me tailor future content to your needs.
And if you’re looking to take a step further, join our exclusive ‘Insider Group‘. As a member, you’ll receive Weekly Super-Tips, and early access to in-depth tutorials. Sign up Today!”
Happy Excel-ling!!
Thank you so much. The first tip that finishes at 2:30 helped me solve the big issue. Very grateful.
That’s so great to hear Stephane. So happy it helped you ?
The first tip worked to fix that the filters didn’t show anything in the dropdown lists. Thank you!!
Fantastic Amy! So glad it helped ?
My microsoft excel unable to Filter all our data, limited to 10,000 unique items displayed & not all data shown after Filter. How ?
Thank you so much
Thank you for letting us know! So happy it was helpful to you ๐
Hi there. 10,000 is just the limit on the number of unique items that can be shown on the filtering drop-down list limit, not the filtering limit. Just go to the Filter drop-down, and then select Number Filters, Text Filters or Date filters from the list. Then use the logic filters to filter your data. I hope this helps ๐
Thanks! Tip #1 was my issue. Great work!
Fantastic Greg! So happy it helped.
None of these worked. What worked for me is I found that the data was formatted as a Table. I “Converted the Table to Range” and that fixed the filtering.
Fantastic! Thanks for sharing ๐
Awesome! Thanks a lot. The first hint worked and resolved the issue.
Fantastic! Thanks so much for letting me know. Really appreciate it!
Add # 6… spreadsheet is protected ๐
THANK YOU!!!! This was driving me nuts. Ungrouped, and all sense was restored. Very grateful!!
So great we could help Rina! Thanks for letting us know ๐
Yes. Grouped sheets was the issue. Thanks this saved time.
Fantastic Dan! So great. Thank you so much for letting us know our blog helped ๐
AHHHHHHHHH GROUPED TABS JUST SAVED MY LIFE AT 10 PM WORKING ON A HUGE ACCOUNTING SPREADSHEET thank you i love you
Ha ha, I’m so glad the blog helped you out! Thank you so much for letting us know ๐
Yes, you solved my problem. Thanks very much!
Yay! That’s so great Amy. Thanks for letting us know.
Hi, Sharyn,
I could cope with the nightmarish issues I had for a couple of days with my excel file just by applying your tip #1.
Thank you very much for sharing those hints! Much appreciated.
You are so welcome!
Yes, please. I have the same issue.
Hi Liene. You might like to have a read through our post on ‘3 Quick wasy to Unprotect in Excel ‘ https://www.excelatwork.co.nz/2021/08/11/unprotect-excel/ I hope one of these options works for you ๐
Great review of Filters. It did help with the first problem you showed. But after that success I watched the entire video and found it very informing. Thank you!
That’s fantastic James. Thank you so much for your comment! Take care, Sharyn
Tip #1 was my issue… so simple that i had forgotten about it ๐
Thank you very much… it was super helpful!!
Thanks Julie! I’m so glad we could help ๐