Manage large lists and libraries

You can store up to 30 million items or files in a list or library. Filtered views of large lists have a similar experience to other lists. However, when a list view shows more than 5000 items, you may run into a list view threshold error.

Use modern

Views showing many items work best in the modern experience. To avoid errors, you might see in the classic experience, use the modern experience.

Add indexes

When you filter or sort by a column that doesn't have an index, an error dialog could appear. To correct, add an index manually from List Settings in the settings menu, then Indexed Columns.

Indexes are created automatically in the following two situations:

  1. When saved views have columns used for sorting or filtering.
  2. When sorting in the modern experience.

Note: Automatic index creation when sorting in the modern experience is limited to lists and libraries with less than 20,000 items.

Edit the list view

If an error occurs when working with a large list, edit your list view.

The following four changes remove list view threshold errors. Make all four changes to remove all the errors.

Remove sorting for the list view

  • from both First sort by the column and Then sort by the column,select None.

Note: Columns with column types like people, lookup, or managed metadata can cause list view threshold errors when sorting. However, you can use text, number, date, and other column types in the first sort.

Remove grouping in edit view

  • From both First group by the column and Then group by the column,select None.

Other edits for the list view

  • For all columns in the Totals section, select None.
  • From the Columns section, clear all but one column for display.

Note: Displaying 12 or more columns of the following types can cause a list view threshold error: people, lookup, and managed metadata. Displaying columns of other types will not. 

List Forms

When SharePoint constructs the list forms, all the fields available for the list item are retrieved from the database. Lists with a large number of Lookup columns may result in complex and potentially intensive SQL statements. SharePoint will verify the length of the full SQL statement and will display this error message in case that the maximum allowed length was exceeded:

"The list item could not be displayed/added because the length of the fields was too long. Please shorten the new entries or remove some fields from this list."

To address this issue please reduce the number of columns of the specific list.

Notes: 

  • In addition to standard lookup columns, single-value managed metadata, multiple-value managed metadata, single-value people and group columns, and multiple-value people and group columns count as lookup columns.
  • Each lookup column in a list view causes a join with another table. Each additional lookup column increases the complexity and inherently the size of the back-end SQL query.