SharePoint large list handling — Solving Microsoft 365

SharePoint lists have a famous 5,000-item view threshold — list views that would return more than 5,000 items fail with a throttling error. For lists growing past that threshold (common in real-world scenarios), specific techniques unlock continued usability without throwing errors.

What the threshold actually means

The threshold is per view, not per list:

A list can have 30 million items — that's the actual list limit.
Individual views can return up to 5,000 items by default.
Beyond that, the view is throttled — error to the user.

So a 50,000-item list is fine; viewing all 50,000 in one view isn't.

Indexed columns

The most important tool: indexed columns. When a column is indexed:

Filters and sorts on that column become fast — even on huge lists.
Views that filter by indexed columns can return up to 20,000 items.
The list can be queried efficiently via REST / Graph.

Add indexes via List Settings → Indexed columns. Up to 20 single-column indexes per list, plus compound indexes.

Index the columns you filter / sort by:

Status — filter to "Active" only.
Department — filter by department.
Modified date — filter to recent items.
Person columns — filter by assignee.

For large lists, indexing isn't optional — it's how you make them usable.

View design

For lists exceeding the threshold:

Filter views aggressively — return small subsets, not "all items."
Sort by indexed columns — sorting on unindexed columns triggers throttling.
Limit columns shown — fewer columns = less work per row.
Item limit — explicitly set the page size (50, 100, 200 items).

Default views shipped with SharePoint often don't follow these patterns; create your own.

Compound indexes

For queries with multiple filters, compound indexes (multi-column) speed them up:

Compound index on Status + Modified Date — fast for "active items modified this month."
Compound index on Department + Status — fast for "my department's active items."

Compound indexes are more powerful than two separate single-column indexes for the specific combination they cover.

Folders for partitioning

For lists exceeding the threshold, folders partition content:

Folder per region, department, year, etc.
Each folder contains under 5,000 items.
Views can navigate to specific folders.

Folder structure has UX costs (users navigating folders); modern SharePoint discourages deep folders. But for very large lists, folder-based partitioning is sometimes the right pragmatic answer.

Power Automate large-list handling

When Power Automate flows query large SharePoint lists:

Use OData filters matching indexed columns.
Page through results with $top and $skiptoken.
Avoid Apply to each over huge sets — refactor to bulk operations where possible.
Use delta queries when only changes matter.

Without these patterns, flows on large lists fail with throttling.

When to graduate to Dataverse

Past a certain scale, SharePoint lists aren't the right answer:

Millions of items at scale — Dataverse handles this gracefully; SharePoint struggles.
Relational integrity required — SharePoint lookups are limited.
Transactional semantics — multi-table updates with atomicity.
Row-level security beyond what SharePoint can provide.
Heavy concurrency — many simultaneous editors.

For these, Dataverse is the right destination. Power Apps and Power Automate work seamlessly on either; the data layer is the difference.

Operational considerations

Monitor list size — track approach to 5,000, 20,000, 30,000 thresholds.
Plan indexes before hitting limits — adding indexes to already-throttled lists is harder than preventing.
Test views during development — verify they work past expected scale.
Communicate to users — train them not to "show all items" on large lists.
Periodic data archival — old items that can be moved out keep active lists fast.

For SharePoint-using teams whose lists are growing, knowing these patterns lets you keep working in SharePoint past the points many think require migration. The 5,000-item threshold is a constraint, not a hard limit on usability.