none
cannot filter, lose data in filter RRS feed

  • Question

  • I tried to filter data about 8000+ rows in Worksheet, but not all data listed in filter drop-down list. I select the first Column and under Home and select Filter, did I miss any steps?

    Please help!

    Thursday, October 31, 2019 5:41 AM

Answers

  • Hi,

    I suggest you confirm if there are any blank rows in the range, if yes please remove these blank rows.

    You can also try to convert the Range to Table and check the result, select the whole range in the Worksheet- go to Insert- Table:

    Please try this method and let me know the result, I'm glad to help you.

    Regards,

    Emi Zhang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Thursday, October 31, 2019 8:44 AM
    Moderator

All replies

  • Hi,

    I'm afraid you need to provide more detailed information.
    e.g. Excel version, build, architecture (32-bit, 64-bit), and about Windows


    Regards,


    Ashidacchi -- http://hokusosha.com

    Thursday, October 31, 2019 6:09 AM
  • AFAIK the maximum number of unique values that appear in the DropDown is 10000 unique values. Are you sure that the problem occurs with 8000 rows because if you have only 8000 rows then I should not think that you could have more than 10000 unique options.

    However, bearing in mind the maximum of 10000 unique values, if not sufficient then you can still use the custom filter option by selecting the required option above the dropdown list. The screen shot below is for numeric data but if the data is text then where I have marked  "Number filters" is will display "Text filters". Select the option and follow the prompts to apply the filter.

    This method does not work if you want to select multiple criteria so it all depends on what filters you want to apply. The other option is to look at Advanced filter and see if that helps. More information on Advanced Filter at the following link.

    https://support.office.com/en-us/article/filter-by-using-advanced-criteria-4c9222fe-8529-4cd7-a898-3f16abdff32b?ocmsassetID=HP010073942&CorrelationId=5883b293-50a9-44aa-bfe8-800475f1ec97&ui=en-US&rs=en-US&ad=US


    Regards, OssieMac

    Thursday, October 31, 2019 6:10 AM
  • Hi,

    I suggest you confirm if there are any blank rows in the range, if yes please remove these blank rows.

    You can also try to convert the Range to Table and check the result, select the whole range in the Worksheet- go to Insert- Table:

    Please try this method and let me know the result, I'm glad to help you.

    Regards,

    Emi Zhang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Thursday, October 31, 2019 8:44 AM
    Moderator
  • Hi,

    Thanks for marking my reply as solution here, it's pleasure that the information will help you. Here I will provide a brief summary of this post for your information.

    ==================

    Issue Symptom:

    Cannot filter with large data in Excel.

    Solution:

    I suggest you confirm if there are any blank rows in the range, if yes please remove these blank rows.

    You can also try to convert the Range to Table and check the result, select the whole range in the Worksheet- go to Insert- Table:

    Reference: None.

    Regards,

    Emi Zhang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Tuesday, November 5, 2019 9:10 AM
    Moderator