SharePoint list view threshold [Everything you need to know]

Are you facing the SharePoint list view threshold issue? Here, I am going to explain how to work with a large list and how to overcome list threshold in SharePoint online. We will be able to fix the below queries:

  • The number of items in this list exceeds the list view threshold which is 5000 items
  • The view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.
  • the attempted operation is prohibited because it exceeds the list view threshold sharepoint online
  • PowerApps the attempted operation is prohibited because it exceeds the list view threshold
  • power automate the attempted operation is prohibited because it exceeds the list view threshold
  • the attempted operation is prohibited because it exceeds the list view threshold rest api
  • the attempted operation is prohibited because it exceeds the list view threshold csom
  • 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.
  • You do not have permission to view the entire list because it is larger than the list view threshold
  • How to increase list view threshold in SharePoint 2016/2013

What is list view threshold in SharePoint?

What is list view threshold in SharePoint? In SharePoint, Microsoft put a threshold or a limit to not retrieve more than 5000 items or files at a time in a single view, so that you will not have a performance issue while retrieving items/files from a large SharePoint list or document library.

If you are open a list view and it tries to retrieve more than 5000 items, then you will see an error as, “The number of items in this list exceeds the list view threshold, which is 5000 items. Tasks that cause excessive server load (such as those involving all list items) are currently prohibited.”

the number of items in this list exceeds the list view threshold which is 5000 items
the number of items in this list exceeds the list view threshold which is 5000 items

Once the list threshold issue came, you will not be able to do any operation in the list. It will give an error as: “the attempted operation is prohibited because it exceeds the list view threshold

the attempted operation is prohibited because it exceeds the list view threshold sharepoint online
the attempted operation is prohibited because it exceeds the list view threshold sharepoint online

There is no problem with storing items in the SharePoint list, in a SharePoint list or library, we can store up to 30 million items or files. The problem occurs when you try to display more than 5000 items in a particular view.

Why do we get 5000 threshold error in the SharePoint list view

The SharePoint list view threshold limitation actually comes from the SQL server. SharePoint list data and list column get stored in a single SQL table. When you browse a list in SharePoint, the SharePoint sends the Query to the SQL server, which will return the result requested by the user.

For performance view, if the SQL server executes the query in a single SQL table if that more than 5000 results, then SQL Server will lock the entire table while Query executes. Users who are accessing the content in SharePoint have to wait for a longer time while the SQL table is locked.

Let us say, there are 10,000 items in a SharePoint Online list, and when you try to display in a view, behind the scene there will be a query triggered to the SQL server. And that creates the problem, it temporarily locks the table.

How to overcome list view threshold in SharePoint Online

To overcome list view threshold issue – Do not retrieve or fetch more than 5000 items at a single point of time.

If you are getting “the attempted operation is prohibited because it exceeds the list view threshold sharepoint online” error, then you can try the below thing:

1. Use SharePoint List modern experience:

In SharePoint Online, you can start using the modern experience for the list and libraries. The paging works in a different way compared to the classic SharePoint experience. It will load items as you keep on scrolling in the SharePoint list view.

2. Planning

Apart from this, To overcome the SharePoint list view threshold in SharePoint Online, we will have to plan how to store files in a library.

  • Planning – In SharePoint before we create a list or library, we must analyze the data, and then you can plan accordingly. If the data in the SharePoint list is bigger then try to split the data into multiple lists and libraries otherwise if you view the bigger list, which is more than 5000 items then it will show the threshold error. The same thing applies to the library also, instead of storing all the files inside one document library, store them in different document libraries.

3. Create Filter List Views

  • Create multiple list views– Because each view can display the content in different ways and allows users to choose what they want to rules. So create multiple views, so that we can limit the items to less than 5000 items.
  • Add filter to your list views– In SharePoint list view, we will filter the SharePoint list view item in such a way that any given point of the time, that list view will return the result of fewer than 5000 items

Below are a few examples of filter views that you can create in the list or library:

  • Display Files uploaded Today: Instead of showing all the items, you can display items created today or files uploaded today.
  • Items Created By Me: Display items that are created by the logged-in user.
  • Items Status equal to not completed: Display SharePoint list items that are not completed, etc.
  • Display Item Created in last 7 Days: Instead of displaying all the items, display items that are created in the last 7 days, 30 days, etc. So that we can create more views and ultimately solve the list view threshold in sharepoint 2016 issue.

Also, you can use the SharePoint list Group By and somehow it will display the items.

4. Index

Add an index– Add an index to the SharePoint list column (to add an index to the SharePoint list column, go to the list-> click the gear icon-> list setting-> Click on index column), which is used for sorting and filtering your views.

SharePoint automatically does some magic in the SQL server, when you add an index to the column which allows it to quickly analyze, return values bypassing the threshold. And also we can only add 20 index columns in a given SharePoint list, therefore we need to plant thoroughly.

Note: SharePoint list Column with column types such as people, lookup or managed metadata can cause the List view threshold error. And you can use text, number, date, and another column type in the first sort in the SharePoint list.

5. Enable Automatic Index Management for List or Library

To overcome the list view threshold in sharepoint 2016 or SharePoint Online, enable automatic index management in the list or document library.

Open SharePoint list settings page, go to Advanced Settings, and then choose Yes to Allow automatic management of indices?.

The view cannot be displayed because it exceeds the list view threshold
The view cannot be displayed because it exceeds the list view threshold

The good thing is by default it is enabled for list and libraries in SharePoint 2016 and SharePoint Online. What it does is, behind there is a timer job “Large list column index management Timer Job” associated in SharePoint 2016 and if it is set to daily then when the list item crossed 2500, it will create indexes automatically.

6. Use Metadata Navigation and Filtering

Another thing you can try to resolve sharepoint list view threshold error is to use the Metadata Navigation and Filtering for the list and libraries in SharePoint. Once you enable this option, it will allow you to configure key filters using various columns in the list, check out the SharePoint Metadata Navigation Settings tutorial for more information.

The above are a few things you can try to overcome the SharePoint list threshold issue or the number of items in this list exceeds the list view threshold which is 5000 items issue.

How to increase list view threshold in SharePoint online

There is no way we can increase list view threshold in sharepoint online. For SharePoint Online, you have to follow the above approach to overcome the SharePoint 5000 item limit threshold.

How to increase list view threshold in SharePoint 2016

Microsoft allows us to increase the list threshold limit in the on-premise versions like SharePoint 2013, SharePoint 2016 or SharePoint 2019. We can increase the list threshold in sharepoint 2016 from the SharePoint central administration.

Follow the blow steps:

Step-1: Open SharePoint 2016 central administration, click on Manage web application which is under Application Management.

Step-2: Select the particular SharePoint web application for which you want to change the list view threshold. Then click on General Settings -> Resouce Throttling.

Step-3: There you will see the option of 5000 in the List view threshold, change it according to the requirement. There also you can change the List view threshold for auditors and administrators which is by default set to 20000.

This is how to increase list view threshold in SharePoint 2016. This applies to only SharePoint on-premise versions.

We can also use PowerShell to disable the SharePoint list view threshold limit for a particular list or document library by using PowerShell.

Add-PSSnapin Microsoft.SharePoint.PowerShell
$web = Get-SPWeb http://SiteURL/
$web.AllowUnsafeUpdates = $True
$list = $web.Lists["YourListName"]
$list.EnableThrottling = $false
$list.Update()
$web.AllowUnsafeUpdates = $False
$web.Update()
$web.Dispose()

PowerApps the attempted operation is prohibited because it exceeds the list view threshold

There will be a number of times while working with SharePoint from PowerApps app, you might receive the PowerApps the attempted operation is prohibited because it exceeds the list view threshold error.

Follow the above approaches to fix first the list view threshold issue.

Power Automate the attempted operation is prohibited because it exceeds the list view threshold

Also, if you are doing any operation with a large list from Power Automate, you can also get the error. To fix the error follow all the best practices above.

the attempted operation is prohibited because it exceeds the list view threshold csom

If you are working with a large SharePoint list using SharePoint CSOM or SharePoint Rest API, then you might get the error as the attempted operation is prohibited because it exceeds the list view threshold csom. It looks like below:

the attempted operation is prohibited because it exceeds the list view threshold csom
the attempted operation is prohibited because it exceeds the list view threshold csom

To fix, this issue, follow the above approaches, if you are retrieving list items, then you can get in batches rather more than 5000 items at once.

The same way you can fix the Rest API error also, the attempted operation is prohibited because it exceeds the list view threshold rest api.

You may like the following SharePoint tutorials:

I hope now you got an idea of the SharePoint list view threshold and you will be able to overcome list threshold in SharePoint Online or in SharePoint 2013/2016/2019. And also, it will help to fix the below error those appear while working with large list in SharePoint.

  • The view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.
  • 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.
  • the attempted operation is prohibited because it exceeds the list view threshold sharepoint online
  • PowerApps the attempted operation is prohibited because it exceeds the list view threshold
  • power automate the attempted operation is prohibited because it exceeds the list view threshold
  • the attempted operation is prohibited because it exceeds the list view threshold rest api
  • the attempted operation is prohibited because it exceeds the list view threshold csom
  • you do not have permission to view the entire list because it is larger than the list view threshold
  • the number of items in this list exceeds the list view threshold which is 5000 items
  • the number of items in this list exceeds the list view threshold when you view lists in office 365
  • the number of items in this list exceeds the list view threshold sharepoint 2013
>