Design a List View That Avoids Threshold Limits: SharePoint Admin Guide

SharePoint list views stop loading or display an error when the list exceeds 5,000 items in a single view. This happens because SharePoint enforces a list view threshold to protect server performance. The threshold blocks any query that returns more than 5,000 items from a list or library. As an admin, you must design list views that respect this limit. This guide explains how to create indexed columns, filter views, and manage large lists so users never hit the threshold error.

Why SharePoint Enforces the 5,000-Item List View Threshold

SharePoint uses a list view threshold to prevent queries from consuming excessive database resources. The default limit is 5,000 items per query for lists and libraries. When a user opens a view that returns more than 5,000 items, SharePoint blocks the query and shows an error message. This behavior applies to all list operations: standard views, datasheet views, and programmatic queries through the SharePoint REST API or CSOM.

The threshold exists because SharePoint stores list data in a SQL Server database. Queries that scan more than 5,000 rows without an index degrade performance for all users on the site. By limiting the size of each query, SharePoint ensures that no single user action locks the database or slows down other requests. The threshold does not limit the total number of items in a list. You can store millions of items in a single list as long as every view and query uses indexed columns to return fewer than 5,000 items at a time.

What Happens When a View Exceeds the Threshold

When a view tries to return more than 5,000 items, SharePoint displays one of these errors:

• “The attempted operation is prohibited because it exceeds the list view threshold.”
• “This view cannot be displayed because it exceeds the list view threshold.”
• “The query cannot be completed because the number of lookup fields exceeds the threshold.”

The same error occurs when you sort or filter a view by a column that does not have an index. SharePoint must scan every row to sort or filter, which exceeds the threshold on large lists. The fix is always the same: add an index to the column used in the filter or sort, then restrict the view to return fewer than 5,000 items.

Steps to Design a List View That Avoids the Threshold

To build a safe list view, you must first identify the columns that users filter or sort by most often. Then add an index to those columns. Finally, create a view that uses a filter on an indexed column. The steps below assume you have at least Edit permissions on the list or library.

1. Identify the columns used in existing views
   Go to the list or library. Click the gear icon and select List Settings or Library Settings. Under Views, click each view name and note which columns appear in the Sort and Filter sections. The most common columns for filtering are Status, Date, Category, and Assigned To.
2. Add an index to a column
   In List Settings, scroll to Columns and click Indexed Columns. Click Create a new index. Choose a column from the Primary Column list. Leave Secondary Column blank unless you need a composite index. Click Create. SharePoint indexes the column immediately. You can index up to 20 columns per list.
3. Create a new view with a filter on the indexed column
   In List Settings, click Create view. Choose Standard View as the view type. Give the view a name, for example, “Active Items by Status.” In the Filter section, select Show items only when the following is true. Choose the indexed column from the first dropdown. Set the condition to equals, is less than, or is greater than. Enter a value that returns fewer than 5,000 items. For example, filter a Status column equals Active if Active items are fewer than 5,000. Click OK to save the view.
4. Set the new view as the default view
   If you want all users to see the safe view first, go back to List Settings. Click the view name you just created. Scroll to the bottom and check Make this the default view. Click OK. Users who open the list will now see the filtered view instead of the full list.
5. Test the view with a large dataset
   Add more than 5,000 items to the list to confirm the view works. Open the list in the browser. The view should load quickly without errors. If you see the threshold error, the filter still returns too many items. Adjust the filter condition to narrow the result set further.

Using Multiple Indexed Columns in a Single View

You can filter by more than one column in a single view. SharePoint uses the first indexed column to narrow the result set. If the first filter returns fewer than 5,000 items, additional filters work without an index. If the first filter returns more than 5,000 items, SharePoint still blocks the query. Always place the most restrictive filter on an indexed column as the first condition.

Common Mistakes When Designing Views for Large Lists

Sorting by a non-indexed column in a large list

Sorting a view by a column that has no index forces SharePoint to scan the entire list. If the list has more than 5,000 items, the sort fails. To fix this, add an index to the column used for sorting. Then create a new view that sorts by that indexed column. You can also sort by the default ID column, which always has an index.

Using a lookup column as the primary filter

Lookup columns reference items in another list. SharePoint cannot index lookup columns directly. If you filter a view by a lookup column, the query exceeds the threshold on lists with more than 5,000 items. Instead, use a choice column or a calculated column that mirrors the lookup value. Index the choice or calculated column and filter by that column.

Setting the list view threshold higher than 5,000

Site collection administrators can increase the list view threshold in SharePoint admin center. Go to Admin centers > SharePoint > Settings > List View Threshold. The maximum value is 50,000. Raising the threshold allows larger queries but increases database load. Microsoft recommends keeping the default value and using indexed columns instead. Only raise the threshold for specific lists that require bulk operations and cannot be indexed.

Forgetting to add an index before creating a view

If you create a view with a filter on a column that has no index, the view works only while the list is small. Once the list grows past 5,000 items, the view breaks. Always add the index to the column before or immediately after creating the view. Check the view after adding 5,000 items to confirm it still loads.

Default View vs Indexed View: Key Differences

Item	Default View	Indexed View
Filter requirement	No filter required	Must filter by an indexed column
Maximum items returned	5,000 items	Unlimited as long as filter returns under 5,000
Index needed	None	At least one indexed column in filter
Sort behavior	Fails on non-indexed column	Works if sorted by indexed column
Performance on large lists	Blocks query after 5,000 items	Loads quickly with proper filter

Designing list views that avoid the threshold requires planning. Index the columns that users filter by most often. Create views that use those indexed columns as the first filter. Test every view after the list exceeds 5,000 items. As an admin, you can also enable the large list mode for document libraries by turning on the “Make this a large list or library” option in List Settings. This setting pre-indexes several default columns for you. Use it alongside custom indexes for the best results.