Note: Video showing resolution in SharePoint Online at bottom of post.
SharePoint lists have a default limit of 5,000 items per view. But lists can contain 30 million items (just not all available in one view). Since you’re reading this, perhaps you’ve already learned this from an error message such as:
The view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.
To view items, try selecting another view or creating a new view. If you do not have sufficient permissions to create views for this list, ask your administrator to modify the view so that it conforms to the list view threshold.
Or this one when attempting to filter by column headers:
“Cannot show the value of the filter. The field may not be filterable, or the number of items returned exceeds the list view threshold enforced by the administrator.”
First of all, when in doubt, refer to the documentation provided by Microsoft. Read it carefully to understand limitations in your specific environment, explanations of various actions and rules and the permissions required to correct the issue.
Here’s my summarized version:
Solution #1: Index Columns Used as View Filters
(works for both On-Prem/SharePoint Server AND O365/SharePoint Online)
- Filter all views so that they cannot return more than the threshold limit (such as [Created] is greater than or equal to [Today]-120 to only return the last four months of data)
- Index all columns used in list view filters ([Created], [Department], [Location], [Agent], etc.) You can index up to 20 columns. ShareGate also recommends indexing the first column in each list view.
- Go to List Settings
- Under “Columns” select “Indexed Columns”
- Click “Create a new index”
- Change primary column to the column on which you’re filtering and click Create
- Repeat steps 3-4 for each column used as a view filter on the list
Solution #2: Increase the Threshold
(On-Prem/SharePoint Server Only)
Ask an administrator to raise the threshold or do it yourself. Please know that thresholds exist for a reason, however, and from a scalability viewpoint this may not be the best action to take as it can affect performance. Learn more on scalability and capacity planning concerns here.
- Go to Central Admin
- Under “Application Management” click “Manage Web Applications”
- Select the web application for which you’re adjusting the list view threshold
- Select “General Settings” then “Resource Throttling”
- Change the limit and click OK.
- Check the result by going to “List Settings” for any list on that web application
O365/SharePoint Online Limitations
- Cannot modify list view thresholds
- Cannot create a Daily Time Window
See more ideas and explanations in Microsoft’s support article.