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.
Just wanted to say thanks for the instructions here. Used the filter and index method and it worked like a charm.
searched throughout the internet but no success, today your first tip filter the view works the best. GREAT THUMBS UP
What if the webpart of listview filtered by “createdby” = [Me]. does indexing work here. How to overcome in this scenario
Thank you. These are great tips !
hello and sorry for my bad english :-),
i can still ask a question about this ? I have O365 and i use a backup software. The software can’t save librairies who have a lot of documents. The error is : “The requested operation is forbidden beyond the list display threshold by the administrator.” I think it is about this limitation.
If i apply this, do you think it will be good ?
We also use Onedrive synchronisation, on their computers, users will see all files in librairies or like the view (example : last four months of data) ?
it is possible to apply this view only for 1 user, my backup user ?
thanks a lot
This error also occurs with powershell cmdlets. I found a solution with with CSOM and camlQuery ( https://www.sptrenches.com/2016/06/get-all-items-in-5000-large-list-with.html . ) . It works for documents library but is not possible with RecycleBin collection. Particularly in my case I have a OneDrive for Business user with more than 200000 items on RecycleBin , it is very costly in this condition to select items on web interface and restore. And with PowerShell, I have this error: “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator”