Filtering not showing all the data

[EvansB2]

Hi there,

I'm working in excel 2016 and have a simple spreadsheet with rows and rows of data and have filters on all columns. I noticed that the drop-down box on any of the columns does not list all the data. I have removed blank rows, however the drop-down on the filter is still limited. Can I do something about this?

Regards

Ben

 

[shg]

Excel 2003 and earlier versions displays up to 1000 items. In 2007+, 10000.

 

[Fazza]

If you've removed blank rows, then totally remove the filters (such as when within the range ALT-D-F-F) and re-apply it (ALT-D-F-F).

This will certainly reset the range being filtered: whereas as simply removing blank rows may not add the extra rows to the range that is filtered. If that makes sense?

 

[Peter_SSs]

Also pointing out (if needed) that although your drop-down list is limited to displaying 10,000 items, the AutoFilter will still work for all your values. So, if you are doing a simple filter and you know what you want to filter for you can use the 'Search' box in the filter drop-down (if the column contains text) or the 'Text Filters' or 'Number Filters' options.