Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

SharePoint "The number of items in this list exceeds the list view threshold, which is 5000 items": fix the view, not the list

Technical article
Troubleshooting

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

The message "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." does not say the list is full. Microsoft says a list or library can hold up to 30 million items. The error means the view or query tried to process more than the limit in one database operation.

In SharePoint Online Microsoft says the limit cannot be changed. The fix is to change what the view asks for: filter on an indexed column, remove sorting and grouping, show fewer columns, or split the data into folders. Write down the view's current settings before you edit it.

What the error means

Microsoft's error page for SharePoint Online gives the cause: SharePoint Online uses the Large List Resource Throttling feature, and "By default, the list view threshold is configured at 5,000 items. When the defined item limit in a list is exceeded, the message is displayed." It sends you to Microsoft's guidance on managing large lists and libraries.

The threshold overview page defines the limit: "Specifies the maximum number of list or library items that a database operation, such as a query, can process at one time. Operations that exceed this limit are blocked." It also says that when a list view shows more than 5,000 items you might run into the error, that the limit is "in place to help get consistent performance across all users with queries to the back-end database", and that for SharePoint "the LVT limit can't be changed".

Microsoft's overview explains why 5,000: if a read or write operation causes more than 5,000 rows to be locked at once, SQL Server can temporarily lock the whole table, which blocks other users. It adds a caution: "The actual number is not always 5,000 and can vary depending on your site, the amount of activity in the database, and your site's configuration."

Microsoft also says a warning may appear on the List Settings page when a list passes 3,000 items, to give you time to make other plans. Creating a site template is blocked on sites with lists or libraries that exceed the limit.

Likely causes

Cause What you see How to confirm
The view sorts or filters on a column with no index Error on opening or sorting the view Edit view: the sort or filter column is not on the Indexed Columns page
Sorting or grouping on people, lookup or managed metadata columns Error appears when the sort or group is applied Edit view: First sort by or First group by uses such a column
12 or more people, lookup or managed metadata columns displayed Error on a view that displays many such columns Count those column types in the view
A filter that still touches more than 5,000 items Filtered view still blocked Check that the column used in the filter is indexed
Recycle bin items Filter appears under the limit but is blocked Microsoft says items in the recycle bin still count
A folder holds more than 5,000 items Folder view blocked Item count in the folder, subfolders each count as one

Microsoft's pages do not rank these causes.

Prove the cause before changing anything

All of these read only.

  1. Copy the exact error text and the view name. Note whether the error appears on opening the list, on sorting, on grouping or on a filter.
  2. Note the number of items in the list or library. Microsoft's pages say the limit is not a list size limit.
  3. Open the list settings and read the Indexed Columns page. On SharePoint in Microsoft 365 select Settings, then List settings or Library settings, scroll to Columns and select Indexed columns. Write down the existing indexes.
  4. Open the view in edit mode and write down the current sort, group, Totals, column and folder settings. Do not select OK.
  5. Check which column types the view uses. Microsoft says indexing works for single line of text, single-value choice, number, currency, date and time, yes/no and person or group (single value), managed metadata and lookup columns (Microsoft adds that an indexed lookup column does not prevent the error), and does not work for multiple lines of text, multi-value choice, calculated, hyperlink or picture, custom, multi-value person or group, and external data columns.

Example (illustrative): at contoso.com.au the SharePoint list Purchase orders on the intranet holds 18,200 items. The default view sorts by Modified By, a people column, and groups by Supplier, a lookup column. Users get the threshold error on opening the list. The list is nowhere near the 30 million maximum, so this is the view. Microsoft's page says people and lookup columns can cause threshold errors when sorting, so the plan is to sort on Created, a date column, and to filter on a simple indexed column.

Fix

Work from the least invasive step.

Edit the list view

Microsoft says four changes remove threshold errors, and that you make all four to remove all the errors: remove sorting, remove grouping, set Totals to None and show as few columns as needed.

Before you change the view, write down its sort, group, Totals, column and folder settings (step 4 of Prove the cause) and keep that record with the ticket.

Risk: The next steps change a view that other people use. Anyone who relies on the sort order, grouping or totals will see them go. Consider making a new view for the change and leaving the old one as it is.

  1. Open the view in edit mode.
  2. From both First sort by the column and Then sort by the column, select None.
  3. From both First group by the column and Then group by the column, select None.
  4. For all columns in the Totals section, select None.
  5. In the Columns section, check as few columns as needed for display.
  6. Select OK.

To undo it: edit the view again and set each option back to the value you recorded. Microsoft notes that you can sort on text, number, date and similar columns in the first sort, which may allow a sort to stay.

Index a column and filter on it

Microsoft's page says filtered views help by reducing the possible results below the limit, and that creating a filtered view with an indexed column is a two-step process: create an index for a column, then create a view that uses the indexed column to filter. Microsoft also says an indexed lookup column does not work for staying under the limit.

Before you add an index, open the Indexed Columns page as in step 3 of Prove the cause, write down the existing indexed columns and keep that record with the ticket.

Risk: The next steps add an index to a column of the list. Microsoft says indexes add overhead to the data and that you can have up to 20 on a list or library. Creating an index requires accessing all items in the list, so Microsoft warns you may be blocked from creating one if the whole list exceeds the threshold. In that case it says to do the operation during the Daily Time Window or contact your administrator. Microsoft's index page also says: "In SharePoint, you can manually add an index to a list of any size." Microsoft's threshold page describes the Daily Time Window as a setting for SharePoint Server administrators.

  1. Open the list or library, then select Settings and List settings or Library settings.
  2. Scroll to the Columns section and select Indexed columns.
  3. On the Indexed Columns page, select Create a new index.
  4. For a simple index, select a column that is not a lookup column under Primary column for this index.
  5. Select Create.

The second step Microsoft describes is a view that uses the indexed column to filter. The filter steps are on Microsoft's separate page, "Use filtering to modify a SharePoint view", which the threshold page links to; they are not set out here.

To undo it: Microsoft says to select the index on the Indexed Columns page and then select Delete.

Use folders

Microsoft says a view of a single folder is at least as fast as a view filtered by an indexed column, and in some scenarios all items can be spread across folders so that no folder has more than 5,000 items. It adds that subfolders count as items, though not what is in them, and that folders are enabled by default in document libraries but not in lists. Moving items changes where users find them, and Microsoft's pages give no way to undo a bulk move, so the folder plan is a decision for the list owner.

The recycle bin

Microsoft says items in the SharePoint recycle bin still count, and that when you clear the bin they are no longer counted. Microsoft's page gives no way to bring items back once the bin is cleared, and no steps for clearing it. Ask the site owner whether the items are still needed.

Other ways to reach the data

Microsoft names these for large lists: the list's own Search box, which is not subject to the limit, taking data offline to Excel or Access, and syncing document folders with the OneDrive sync client.

Confirm the fix

  • Open the edited view. It loads with no threshold error.
  • Open the view as an ordinary user, not only as an administrator. Microsoft says, in its SharePoint Server table, that administrators are not subject to the resource throttles it lists, so an admin test can pass when a user's view still fails.
  • Sort and filter once, to prove the indexed column is used.

What to watch afterwards

  • Item counts as the list grows toward the 3,000 warning and the 5,000 threshold.
  • New views that users create. Microsoft suggests that for a large list you might remove the Manage Personal Views permission from contributors, because a view that spans every item can affect the rest of the site.
  • Lookup columns, which Microsoft says do not help as an index to avoid the threshold.

Risks and limits

These steps have not been run or lab-tested.

Microsoft's pages say the limit "can't be changed" in SharePoint, and describe a Daily Time Window and an administrator setting for SharePoint Server versions only. They give the default 5,000 and a general caution that the actual number varies, so treat the figure as approximate. Microsoft's pages give no steps for SharePoint Server administration and no Power Apps or Power Automate limits.

Sources