← Al Davies, 250 High Street

SharePoint list over 5,000 items: what breaks and the fix

Short answer: the 5,000 limit is not a cap on list size. It blocks any one view or query that has to process more than about 5,000 items at once, and Microsoft says the real number can vary by site. Add an index to the column you filter on, then build the view to filter on that indexed column first. A list can keep growing; folders and separate views keep each request small.

What the list view threshold is

Microsoft calls it the list view threshold. Microsoft describes it as the maximum number of list or library items that a database operation, such as a query, can process at one time. Operations that go over the limit are blocked.

The default number is about 5,000. Microsoft adds that the actual number is not always 5,000 and can vary depending on your site. For SharePoint, Microsoft says this limit can't be changed. So plan around it. Do not plan to raise it.

The key word is "operation". The threshold is about the work SharePoint does to answer one request. It is not about how much your list stores.

What it is NOT

It is not a cap on total items. Microsoft states that a list can have up to 30 million items and a library can have up to 30 million files and folders. Microsoft's large lists page says the same: you can store up to 30 million items or files in a list or library.

So a list with 8,000 items is fine. A list with 200,000 items can also work. What matters is whether the view you open asks SharePoint to process too many items at once.

Two other limits get mixed up with this one. They are separate:

What you see when you hit it

Here is only what Microsoft documents:

The exact wording of the message and the screen where you see it can differ by version. Exact names may differ in your tenant.

Why a big list can still work

Microsoft says that filtered views with column indexes create queries that return only a subset of the rows. The idea is simple. If SharePoint can use an index to find the 200 items you want, it does not have to process the whole list.

Think of a book. Without an index, you read every page to find a word. With an index, you jump to the right page. An indexed column does that for your list.

Fix 1. Add an index to the columns you filter on

Microsoft says adding an index to a list or library column increases performance when you use filters. It also says to use indexes only where they make sense, because indexes add overhead to the data.

One way to start: pick the one or two columns that people filter on. Status, Owner, Department and a date column are examples. Then add an index to those.

Steps Microsoft describes (exact names may differ in your version):

  1. Open the list.
  2. Open Settings, then List settings.
  3. In the Columns section, select Indexed columns.
  4. Select Create a new index.
  5. Under Primary column for this index, pick a column.
  6. Select Create.

To remove an index, open it on the Indexed columns page and select Delete.

Things Microsoft documents about indexes:

That last point is the most useful one. Do not wait for the error. Add the index when the list has a few hundred items.

Fix 2. Build views that filter on an indexed column

An index only helps when the view uses it. A view with no filter still tries to process every item. Build the view so the first filter is on an indexed column.

Examples of views built on a filter:

Set filters in the view editor, in the Filters section. Exact names may differ.

Also keep the number of columns in a view reasonable. Fewer columns means less work per request. Microsoft also notes that views showing many items work best in the modern experience.

Fix 3. Use folders and separate views

Microsoft lists folders among the ways to manage the number of items returned, along with indexing and filtering. A folder holds a smaller group of items, so opening it asks for fewer items at once.

A folder by year or by region can keep each group small. This is one way to start. Folders have a cost: people must pick the right folder when they add an item. If your team will not do that, use filtered views on an indexed column instead.

Microsoft's large lists page also mentions other tools to limit what is returned, such as personal views and the Search box. Read that page for the current list.

Fix 4. Avoid unindexed filters and sorts

A filter or a sort on a column with no index can make SharePoint look at every item. The result can be a blocked view.

Habits that help:

Microsoft's large lists page explains which view settings can trigger the limit. Read it before you build a view for a large list.

When to rethink the design

Indexes and filters are the first tools. Sometimes the better move is a different design. Some signs:

For a comparison of when a list or a spreadsheet fits, see "SharePoint list vs Excel for team tracking: which one fits?". If your tracker started life as a spreadsheet, see "Replace a spreadsheet tracker with a SharePoint list".

Which fix fits which problem

Our advice, not a Microsoft rule: match the symptom to the fix.

A simple plan for a list that is growing

  1. Check the item count in the list now.
  2. Pick the one or two columns people filter on.
  3. Add an index to each of them while the list is still small.
  4. Build the main views so the first filter is on an indexed column.
  5. Open each view and confirm it loads.
  6. Set a yearly reminder to archive closed items.

Honest limits

Microsoft says the number can vary by site and that limits can change. Read the source pages before you plan around any number in this guide. Exact names may differ on your screen.

This guide covers SharePoint in Microsoft 365. Older server versions have other settings that this guide does not cover. If your list is already over the threshold and you cannot add an index, ask your SharePoint administrator. Microsoft's index page points to that route.

When to get help

You can build all of this yourself in Microsoft 365. If you would rather have it built and tested for your team, I do that. A tracker like this is a fixed price: $5,000 for one, built in 2 weeks. See "What a SharePoint tracker costs to build". Bring one example of the problem to a free call.

Tell me what's still manual

I am Al Davies. I run 250 High Street LLC and build SharePoint trackers and Power Automate flows for operations teams. I work remotely. The first call is free, and I will not try to sell you anything.

See trackers I built: Tools I built

FAQ

Why is the SharePoint list view threshold 5,000?

Microsoft says SQL Server switches from row-level to table-level locking when one operation locks more than about 5,000 rows. It also says the exact number can vary by site.

How do I fix the list view threshold error?

Microsoft says that when a list view shows more than 5,000 items, you might run into a list view threshold error. Add an index to the filter column, filter the view on it, or use folders.

Is the SharePoint list limit 20,000 or 30 million?

A list in Microsoft 365 can hold up to 30 million items. The 20,000 on Microsoft's pages is for SharePoint Server, not Microsoft 365: the threshold for auditors and administrators, and the default for adding indexes in SharePoint Server 2016 and 2019. The 5,000 figure limits one operation, such as a view, not the total.

Can I raise the threshold in SharePoint?

Microsoft says that for SharePoint the limit can't be changed. Use indexes, filtered views and folders instead.

Can I add an index to a list that is already over 5,000 items?

It may be blocked, because creating an index needs access to all items. Microsoft suggests asking your administrator. Add indexes before the list gets large.

Sources