How to resolve SharePoint list view threshold error: “The view cannot be displayed because it exceeds the list view threshold…”

Ans:- 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.

Supported and unsupported columns for indexing

Supported Column Types Single line of text Choice (single value) Number Currency Date and Time Person or Group (single value) (Lookup) Managed Metadata (Lookup) Yes/No Lookup (Lookup)

Unsupported Column Types Multiple lines of text Choice (multi-valued) Calculated Hyperlink or Picture Custom Columns Person or Group (multi-valued) (Lookup) External data

Solution #1: Index Columns Used as View Filters
(works for both On-Prem/SharePoint Server AND O365/SharePoint Online)
1) 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)

2) 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.

1. Go to List Settings
2. Under “Columns” select “Indexed Columns”
3. Click “Create a new index”
4. Change primary column to the column on which you’re filtering and click Create

   5. 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)

1. Go to Central Admin
2. Under “Application Management” click “Manage Web Applications”
3. Select the web application for which you’re adjusting the list view threshold
4. Select “General Settings” then “Resource Throttling”
5. Change the limit and click OK.
6. 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

Multiple ways to overcome this challenge and retrieve the data from the list.

When we index a column, we are requesting SharePoint to make that column more available and executable in a query when compared to other columns.

We should be aware that this prioritization comes at a cost, which is an increased overhead at the database level. Therefore, it is advisable to index columns that are going to be searched and filtered very frequently.

The concept behind the filtering of views is to exclude irrelevant items within a list. To make a filtered view more relevant, it’s required for the first column within the list to be indexed to keep the returned rows or items below the view threshold.

OData:

In OData queries, generally when querying lists items without filter, then it works fine irrespective of count of items in the list. But the problem arises when we need to add a filter in OData query based on some business requirements and when the item count in the list is more than 5000 records. It gives threshold limit error.

To overcome this, we need to index columns used for filtering the results in the OData query. But, we also need to see that after filtering query over the indexed column, the result should not be greater than 5000 records else, we will have to implement more filters using indexed columns to keep count well below 5000 records. Indexing to be done in the same way as discussed above for the required columns.

Example: /_api/web/lists/getbytitle(‘’)/items?$filter=Column1 eq 2

In the above OData query, Column1 must be indexed column if the total number of items in the list is greater than 5000 records.

Lessons learned: if you want to use indexed columns to overcome the 5000 items list view threshold, you need to keep the following in mind:

• You can only have 20 indexed columns per list. Plan thoughtfully!
• You can't create an indexed column if the list is already hitting the list view threshold.
• If you use additional sorting in a view, the sort column also needs to be an indexed column.
• Review the list items and check if some can be deleted or archived to another list.