Manage large lists and libraries in Office 365

No matter how big or small, lists and libraries are vital to you in many ways. But when a list or library is growing in size and might exceed 5000 items, it's time to carefully plan and organize how the data is accessed. Why is 5,000 such a magic number? Because this is the List View Threshold, which blocks most list and library operations when this limit is exceeded. This blocking operation can be frustrating, but prevents adversely affecting the service performance of other users. Here's some guidance for ensuring that you are not blocked, can fix the problem if you are blocked, and can stay on track.

In this article

The basics of managing large libraries and lists

To give you time to make other plans, you get a message on the list or library settings page when your list has exceeded 3,000 items. This is the time to create filtered views with a column index, which is a primary method for managing large lists and libraries that have exceeded the List View Threshold. However, every situation is unique, you might not be able to plan ahead, or circumstances might be beyond your control. Once any user adds an item to a list or file to a library that exceeds the List View Threshold, then you can get stuck. This puts you in a bit of a catch-22 situation. Because the following operations require access to the entire list or library, you are blocked from doing the very operations that can help you get unstuck.

Critical operations

  • Create or remove an index.

  • Use the Open with Explorer command.

  • Filter and sort based on non-indexed columns.

  • Set permissions.

  • Show totals.

  • Save a list as a template with data.

Other operations

  • Delete a folder or list.

  • Change a column or column type (except a name change).

  • Enable or disable list attachments.

  • Create a lookup column that enforces a relationship.

  • Copy or rename a folder.

Fortunately, there are two quick actions you can take to fix a blocking issue and free yourself from this catch-22 situation. You can:

  • Create an index in a list that has up to 20,000 items.

  • Delete a folder or list (or web site) that contains up to 100,000 items.

Furthermore, there are several additional strategies you can use to either prevent blocking or to fix the blocking issue if you get stuck.

Planning your migration carefully

Before you migrate files and lists to Office 365, it’s important to consider the List View Threshold. Often you might need to reorganize your files or rethink your lists before you migrate to make them work smoothly in Office 365.

  • Libraries    Analyze the files that you currently have. Think about how much of the content is used regularly, and whether you want to store all of it in Office 365. In Windows Explorer, search your files by size and by date to quickly identify the large or recent files. You can reorganize the files into folders so that no single folder exceeds the List View Threshold. Organize content into folders by topics or categories. For example, organize contracts into folders based on the month that the contract was signed, invoices based on the creation date, or files into custom categories that correspond to your business.

  • Lists    It is also possible to create folders in a list, but note that there is no easy way to move list items between folders, as you can with libraries. Consider splitting large lists into multiple lists so that no list exceeds the List View Threshold. In some cases, this strategy works well when there is a practical way to subdivide the data that makes sense to users. If splitting into lists is not practical, migrate a portion of the data to the list below the List View Threshold.

    After you migrate your list or library data, you can now create a filtered view with a column index or adopt other strategies described in the following sections before adding the rest of the data.

For more information and a video on how to move files to a library, see Set up Office 365 file storage and sharing.

Using automatic index management

To help reduce blocked operations, Office 365 automatically identifies and creates an index for a public view by doing the following:

  • Identifies lists and libraries that are approaching or have exceeded the List View Threshold.

  • For each list or library, identifies views that don’t have an index, but would benefit from having one.

  • Creates an index for those views. In some cases, two views might share the same index.

Automatic index management occurs by default but you can turn it on or off by setting the allow automatic management of indices option in the list or library advanced settings page. Changing the setting does not affect an index you have created. But if you delete the index created by automatic index management, it might be automatically created again. Note that it might take up to 24 hours for this index to be created, and that this feature is not designed to work with private views.

Default views

To further help you manage large lists and libraries, the default view of a list or library displays results the following way:

If the list or library does not exceed the List View Threshold:

  • Libraries display folders first, then the files.

  • Lists display items from newest to oldest by the ID column.

If the list or library does exceed the List View Threshold:

  • Libraries display folders and files sorted together in alphabetical order by the name column.

  • Lists display items from oldest to newest by the ID column.

Deleting rows or files

If you have the original or a backup copy of the data, you can delete rows or files (up to 100,000) until you no longer exceed the List View Threshold limit to regain access to the list or library. You can now create a filtered view with a column index or adopt other strategies described in the following sections. Then, you can add back the deleted items from your backup copy. For more information on deleting data, see Manage the Recycle Bin of a SharePoint site collection.

Using Search

An alternative way to find documents or items in a large library or list is to use the Search box to enter a keyword or phrase. Search has its own indexing mechanisms, and it is not subject to the List View Threshold. When you use the Search box, you can progressively expand the scope of the search operation:

  • By default, the search scope is initially based on all the items in the current view and any subfolders. You see the results as columns that you can further filter and sort. If the List View Threshold limit is exceeded, not all results are displayed.

  • If you don’t find what you are looking for, you can expand your search scope to include the entire list including all subfolders, regardless of the current view or List View Threshold limit.

  • Finally, you can expand the scope to search the entire site. In this case, you see all the results in the standard Search site page. You can further narrow the results by using the Refinement panel to filter, for example, by the author of a document or the creation date of a list item. You can even use Boolean syntax and logical operators to formulate more elaborate queries.

Note:  The Search box is only available for lists and libraries that are displayed as client-side rendered views. Although the default behavior is to display the Search box, it can be hidden by the List View Web Part property, Search Box, in the Miscellaneous section of the tool pane.

Using metadata navigation

Metadata navigation is enabled by default on most sites. This feature can automatically select the best index to work every time a view is loaded. When you display new views, apply filters to views, clear filters, or apply a sort on a field, query optimization determines the best way in which to query the database. If the List View Threshold limit is exceeded, you can still see a subset of results.

When you use a view that cannot use an index to query the list or library, then metadata navigation dynamically constructs and executes a fallback query. A fallback query is a modified version of the original query that displays partial results because it only queries a portion of the list instead of the entire list. Up to 1,250 of the newest items are displayed based on when those list items were added to the list.

For more information, see Set up metadata navigation for a list or library.

Top of page Button Top of page

Create a filtered view with a column index

A filtered view with a column index is not only a way to retrieve items more efficiently, but a primary method of working with large lists and libraries without getting blocked. Creating a filtered view with an indexed column is a two-step process: create an index for a column and create a view that uses the indexed column to filter the view:

  • Indexes    An index retrieves items quickly and can improve list and library performance. You can create up to 20 indexes for a list or library. Unique values require an index and the ID column is automatically indexed. Because each index adds some overhead to every database operation to maintain the index, it’s best to only add indexes for the most common or likely columns used to query the list or library.

  • Filtered views   When you create a filtered view, make sure that the first column in the filter expression is indexed and that the single filter expression that refers to the indexed column does not exceed the List View Threshold. Other columns you specify in the view filter may or may not be indexed, but the view does not use those indexes, even if the final result of the filtered view returns less than the List View Threshold. If you use two or more columns in the filter expression, use an AND operator but make sure the first column in the expression returns the lesser amount of data.

    Note: If you move items into the Recycle Bin, those items will still be counted when determining whether the filter expression exceeds the List View Threshold. If you clear the recycle bin they are no longer counted.

Create a filtered view with a column index

  1. Sign into Office 365 and navigate to the site that contains the list or library.

  2. Click its name on the Quick Launch, or click on the Settings menu Office 365 Settings button , click View All Site Content, and then click the name.

To create a simple index

  1. Click List or Library > List Settings or Library Settings.

  2. Under the Columns section, make note of the type for each column. Make sure you select a supported column type in step 5.

    Supported Column Types

    • Single line of text

    • Choice (single value)

    • Number

    • Currency

    • Date and Time

    • Person or Group (single value)

    • Managed Metadata

    • Yes/No

    • Lookup

    Unsupported Column Types

    • Multiple lines of text

    • Choice (multi-valued)

    • Calculated

    • Hyperlink or Picture

    • Custom Columns

    • Person or Group (multi-valued)

    • External data

    Notes: Although you can index a lookup column to improve performance, it does not prevent exceeding the List View Threshold. Also, indexing the "looked up" column in the other list or library does not improve the performance of the lookup operation.

    Each lookup column in a list view causes a join with another table. Each additional lookup column in a view increases the complexity of metadata navigation and list view queries. 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 all count as lookup columns which can lead to the query exceeding the List View threshold.

  3. Click Indexed columns.

  4. On the Indexed Columns page, click Create a new index.

  5. In the Primary Column section, under Primary column for this index, select a supported column type from step 2.

    Note: A compound index is based on two columns, primary and secondary, and can use either column to access the data quickly. To create a compound index, in the Secondary Column section, under Secondary column for this index, select a different supported column type. Compound indexes are not used by filtered views to prevent blocking, but they are used with metadata navigation.

To create a filtered view

  1. In the ribbon, under List Tools or Library Tools, click the List or Library tab.

  2. In the Managed Views group, click Create View.

  3. Click the option, such as Standard View or an existing view that you want to use to create your view.

  4. In the View Name box, type the name for your view.

  5. Select Make this the default view if you want to make this the default view for the list or library. Only a public view can be the default view for a list or library.

  6. In the Filter section, click Show items only when the following is true, and then choose how you want to filter the items based on one of the columns that is indexed. For example:

    To view

    • Items changed in the past week.

    • Items added before last week

    • My items

    • Items due today

    • Items updated in the past month

    • Items ready for archiving

    • Items from Mexico

    • Items beginning with "D"

    • Items from 2008 and the NorthEast.

    Filter by (Indexed column in bold)

    • Modified is greater than  [Today]-7.

    • Created is less than [Today]-7.

    • Created By is equal to [Me].

    • Due Date is equal to [Today].

    • Last Updated is greater than [Today]-30.

    • Date Modified is less than December 31st 2006.

    • CountryRegion equals Mexico .

    • Name Starts with D.

    • Region equals "NE" AND Year equals 2008.
      Region has less values than Year.

  7. Choose any other options that you want for your view, such as which columns you want to show or hide and how you want to sort the items.

    For more information on views, see Create, change, or delete a view of a list or library.

  8. Click OK.

    Note: If a list or library has RSS support enabled, the RSS Feed uses the default RSS view, which filters the results based on the date values in the Modified column. For a large list or library, it's a good idea to index the Modified column. You can also reduce the number of items retrieved by changing the number of items and number of days for which changes are included in an RSS Feed. For more information, see Manage RSS feeds for a site or site collection.

Top of page Button Top of page

More about managing large libraries

The following sections describe additional ways to manage large libraries.

Organizing libraries into folders

Although folders are not required to use large libraries, they help organize your data and improve the efficiency of your data access. When you create a folder, behind the scenes you are creating an internal index. This internal index is also created for the root folder, or top level of a list or library. When you access items in a folder, you are effectively using this internal index to access the data. Keep in mind that each folder and subfolder is counted as an item for the List View Threshold. It is important to consider the following when you use folders to organize a large library:

  • It is often useful to make the default view show all the available folders first without any filtering so that users can choose the appropriate folder when they insert new items. Displaying all the folders also makes it less likely that items will be incorrectly added outside the folders in the library.

  • If a folder contains more items than the List View Threshold, or you choose the Show all items without folders option in the Folders section when you create a view, then you can be blocked. But you can still create a filtered view with a column index.

Using the OneDrive for Business library

OneDrive for Business is a personal library intended for storing and organizing work files in Office 365. Using the OneDrive for Business sync app, you can also work with files on your local computer and synchronize changes between your local computer and Office 365. Unlike document libraries in a team site, the OneDrive for Business library has a List View Threshold of 20,000 items. For more information, see Move SharePoint Server 2013 OneDrive for Business files to Office 365.

Note: OneDrive for Business is different from OneDrive, which is intended for personal storage separate from your workplace.

Using the Content Organizer

You can use the Content Organizer to route files to specific document libraries, folders, or even other sites. The Content Organizer can automatically create folders based on metadata properties and balance content into different folders to maintain a maximum size for each folder. When a specified size limit is reached, a new subfolder is created to contain additional files. For example, you can route documents and create new folders automatically, such as folders based on day, month, and year. For more information, see Configure the Content Organizer to route documents and Create Content Organizer rules to route documents.

Using the Document Center site

You can use a Document Center site when you want to create, manage, and store large numbers of documents. A Document Center is based on a site template and is designed to serve as a centralized repository for managing many documents. Features, such as metadata and tree view navigation, content types, and web parts, help you organize and retrieve documents in an efficient and meaningful way for your users. You can use a Document Center site as an authoring environment where user activity is high, or a content archive with occasional access. Content stewards can quickly configure metadata-driven navigation to perform well for most libraries without explicitly creating indexes, but also get assisted when creating additional indexes to enhance the performance over a wider range of filters and views. For more information, see Use a Document Center site.

Note: The Document Center site is not available in Office 365 Business Essentials or Office 365 Business Premium. It is also not available in Office 365 Small Business and Office 365 Small Business Premium, which are no longer available for purchase.

Top of page Button Top of page

More about managing large lists

The following sections describe additional ways to manage large lists.

Synchronizing large lists

Taking data offline is often more convenient and efficient for you. You can make changes on your local device, and then synchronize changes and resolve conflicts in an efficient way. Working with large lists, by using offline synchronization to "scrub", analyze, or report data, helps off-load database activity and minimize use of online resources.

If the Access Services feature is activated, then you can work with considerably more data than the List View Threshold, up to 50,000 items by default. Both Access 2013 and Excel 2013 automatically process list data in small batches and then reassemble the data, a technique that enables working with substantially more data than the List View Threshold, and without adversely impacting the service performance of other users.

Using Microsoft Access 2013       You can read and write most lists from Microsoft Access 2013 by linking to them. Access 2013 works well with virtually all SharePoint data types. Linking lets you connect to data in a list, so that you create a two-way connection to view and edit the latest data both in the list and your Access database. Access creates a copy of the list in an Access table., caches the list data on the client, uses an efficient in-memory, write-through cache, and only transfers changed list items, all of which makes queries and updates perform much faster. A conflict resolution dialog box also helps to manage conflicting updates in a smooth way. For more information, see Import from or link data to a SharePoint list.

Using Microsoft Excel 2013        You can export a list to an Excel Table, which creates a one-way data connection between the Excel table and the list. When you update your data from the list and refresh the Excel table, Excel 2013 replaces the Excel data with the latest list data, overwriting any changes that you made to that Excel table. Once the data is in the Excel table, you can take advantage of the many data analysis features of Excel 2013, such as PivotTable reports, professional-looking charts and spark lines, conditional formatting with icons, data bars, and color scales, and what-if analysis operations. For more information, see Synchronize a list with a spreadsheet program.

Using an Access app

An Access app is a SharePoint app that you can create without using code. In general, an app is a focused, point-solution that is easily distributed and available in a marketplace. Think of an app as a convenient, alternative way to package, distribute, run, monitor, and retire a solution. Instead of storing data in lists, an Access App uses a SQL Server database to store data, and is therefore not subject to the List View Threshold. The database is an SQL Server Azure database enabled with a free account and free storage. This significantly improves the scalability of Access solutions and makes for efficient handling of potentially millions of records. For more information, see Create an Access app.

Top of page Button Top of page

Improving page performance

As you increase the size of the HTML source code behind any web page, there is usually a corresponding increase in time to render the page. The larger the page, the longer it takes to display. For example, the default style for a list or library view is the table style, which displays all the data, one item per table row, on one page. Therefore the more rows you display, the longer it takes to display the data. Once you create a filtered view with a column index and adopt the right strategy for managing large lists and libraries, there are additional ways that you can use to speed up the display of your results in your browser:

Setting an item limit on a page

By setting an item limit on a page, you can directly control how many items display at one time by using a specific number, the default being 30.

Feature image

There are two options you can choose when setting an item limit on a page:

  • If you select Display items in batches of the specified size (for paging), then you are creating a page-by-page display of the data which is useful when you are browsing items.

  • If you select Limit the total number of items returned to the specified amount, then you are creating a hard limit that may or may not return the entire results of your filter operation, but might be useful when testing your view, creating a prototype, or retrieving the top values in a view.

Using the Preview Pane view style

To see all of the information from items in your list in a vertical pane format, use the Preview Pane style. You can more quickly navigate the data by hovering over the item title in a scrolling region on the left side of the page, to see all the column values of the current item vertically listed on the right side of the page. Less initial data is displayed, and this helps display the view faster. This style is also very useful when your list is wide or has many columns and would require horizontal scrolling to see the data.

Feature image

Using the Group By view style

The Group By view style groups the data by a category (up to two levels) that you choose when defining the view, which makes the initial display of the list view much smaller. You click a Plus/Minus button to expand/collapse a specific group of data and only load the subset of data that is needed onto the current page.

Feature image

This view style not only limits the display of the data, but also the retrieval of the data. Make sure that the default Show Grouping option is set to Collapsed. You can also control the paging of the grouped data in the same way as data that is not grouped. In some situations, such as simple reporting or drilling down and drilling up to find and update data, this view style can be much more efficient to use.

For more information on views, see Create, change, or delete a view of a list or library

Top of page Button Top of page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×