Excel sheet only shows first 1000 rows when filtering

Phelps-Ward Angela E 0 Reputation points
2026-11-19T19:15:27.39+00:00

In excel web browser on SharePoint my filter is limited. How do I get it to show all unique items in each column? Not just the first 1000.

Microsoft 365 and Office | Excel | For business | Other
0 comments No comments

2 answers

Sort by: Most helpful
  1. Ivory 2,900 Reputation points Independent Advisor
    2026-11-19T19:47:35.5366667+00:00

    Hi,

    Excel for the web appears to show roughly the first 1,000 unique items per column, and the limit cannot be changed in any setting. Desktop Excel shows up to 10,000 unique items, as listed on Microsoft's specifications page: Excel specifications and limits | Microsoft Support

    You can try the following options:

    1. Open the file in desktop Excel: In the browser, choose Open in Desktop App from the ribbon (Editing > Open in Desktop App). The drop-down lists up to 10,000 unique items and includes a Search box for values beyond that.
    2. Use the filter conditions instead of the checkbox list" In the filter menu, choose Text Filters (or Number Filters) and use options such as Contains, Begins with or Equals.
    3. Add a helper column to group the values: For example, add a column that returns the first letter of each name, filter on that column first, then filter on the original column. Each group then stays under the limit, so every item appears in the list.

    If you have any questions or need further assistance, please feel free to share them in the comments on this post.


    If the answer is helpful, please click 'Yes' and kindly upvote it.

    Note: Please follow the steps in the forum documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

    Was this answer helpful?

    0 comments No comments

  2. AI answer

    2026-11-19T19:16:42+00:00

    The normal worksheet filter in Excel for the web has no documented control to expand its checkbox list beyond 1,000 values, so use a criteria-based filter or open the workbook in desktop Excel when an exhaustive value list is required.

    Filter for values not shown in the list

    In Excel for the web:

    1. Select the filter arrow in the column heading.
    2. Choose the filter appropriate to the column:
      • Text Filters → Equals, Contains, Begins With, or another condition.
      • Number Filters → Equals, Between, Greater Than, and so on.
      • Date Filters → the required date condition.
    3. Enter the value or criteria.
    4. Select OK.

    These filters operate by criteria instead of requiring the value to appear as a checkbox. Current Excel for the web guidance documents custom Text, Number, and Date filters.^1^

    Microsoft’s older browser-filter documentation explicitly limits the displayed list to 1,000 items, and the Excel Services protocol also defines a condition for more than 1,000 filter values.^2^^3^ However, those sources do not establish that this is a configurable limit in the current Microsoft 365 web interface.

    If you need every unique value displayed

    Use one of these workarounds:

    • Open in Desktop App: In the SharePoint workbook, select Editing or Open → Open in Desktop App, then inspect or process the column there.
    • Create a separate unique-value list: In desktop Excel, generate or maintain a deduplicated list on another worksheet. This provides an exhaustive reference without depending on the web filter’s checkbox list.
    • Use Power Query, if the data is already handled through Power Query: Open the query editor, select the column filter, and choose Load more when the incomplete-list message appears. Power Query loads distinct values in batches of 1,000.^4^ This applies only inside the Power Query Editor; it does not enlarge the normal worksheet AutoFilter dropdown.

    The SharePoint 5,000-item List View Threshold is relevant when filtering an actual SharePoint list or document library, not an Excel worksheet merely stored in SharePoint.


    References

    1. [Web](https://support.microsoft.com/excel/get-started/filter-data-in-a-range-or-table-in-excel)
    2. Filter data in a workbook in the browser | Microsoft Support
    3. [MS-EXSPWS3]: AutoFilterItemList | Microsoft Learn
    4. Filter by values in a column
    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.