SOLVED
Home

Excel filter issues

Highlighted
P DS
New Contributor

Excel filter issues

Hi

I have an excel file with dates (month) from 01/07/2016 to 30/05/2017.

Excel is only filtering the top 4 months on the list. (everytime I sort the date, either from oldest to newest or vice versa) There are no spaces, I've removed and added the filter, copied and pasted the data etc. and tried and the filter doesn't seem to work properly. The file is attached. I have Excel 2016 and Office 365. If someone can help me resolve this please, would really appreciate it!

Many thanks..

13 Replies

Re: Excel filter issues

Hi

 

There is a limit.

Items displayed in filter drop-down lists

10,000

 

 

Re: Excel filter issues

There is a known issue in Excel 1706 with date filters not showing all the dates of the data in the column.

 

Currently, the only workaround is to roll back to Excel 1705, where the problem does not occur. This article explains how to roll back to a previous version. 

Re: Excel filter issues

Another couple workarounds could be

1) Use search filter

 

SearchFilter.JPG

SearchFilter1.JPG

 SearchFilter2.JPG

2) Convert data to table and use the slicer, for this particular case it's not too big

Slicer.JPG

Solution

Re: Excel filter issues

Sergei, the Excel version 1706 has a known problem with filters. It has been reported to the Excel developers. Search filters will not help in this scenario, because Excel does not include some date ranges in the filter in the first place. The search only searches in what the filter already includes. If the filter does not include it, it cannot be discovered by search. 

Re: Excel filter issues

Ingeborg, perhaps on some concrete builds. I'm on 1706 and search workaround works with me.

 

Version.JPG

And for 10,000 limit same Microsoft gives https://support.microsoft.com/en-us/help/295971/not-all-items-are-displayed-in-the-autofilter-pivott...

 

Re: Excel filter issues

We're still gathering reports on which versions and builds show the problem.

 

It may be just a few builds of the 1706 version, not all of them.

Re: Excel filter issues

Sergei, I can confirm that the workaround you posted does not resolve the issue when it occurs. You can see in the attached screenshot that the filter does not find the dates, even though they are present in the column.  You may not be able to reproduce the issue, but that does not mean that it doesn't exist for others. 

 

2017-07-10_22-24-15.jpg

Re: Excel filter issues

Ingeborg, please try 06 instead of June. Search filter for such cases works with date format string.

 

Apr01.JPG

Apr02.JPG

 

 

 

Re: Excel filter issues

@Sergei Baklan Yes, it finds the dates. Still, the list not showing all the dates is a bug.

Re: Excel filter issues

@Ingeborg Hawighorst, I agree that's a bug, all dates in the list are calculated as unique ones, thus have 10000 limit. I only gave possible workaround.

Re: Excel filter issues

Thank you!

Re: Excel filter issues

Thank you!! Yes I think it's a bug as before the upgrade to the new version of excel, we didn't come across such an issue

Re: Excel filter issues

Thank you
Related Conversations
Filter a list in place
Peter Rosen  in  Excel  on
2 Replies
Filtering on more than one #hashtag
Ken Nyren  in  Microsoft Stream Forum  on
1 Replies
EXCEL DO NOT SHOW GRAPH MAP CHART
Mark 777  in  Excel  on
43 Replies
Complex INDEX MATCH - Need assistance
Monica Bossert  in  Formulas and Functions  on
30 Replies
Missing Excel 2013 worksheet tabs
Mike Hill  in  Excel  on
7 Replies