Indexed Column in SharePoint Online List

In this SharePoint Online tutorial, I will explain why we use the Indexed column in the SharePoint Online list. And we will see the supported datatypes to create an Indexed column in SharePoint. Finally, I will show the two kinds of indexed columns that can be created in the SharePoint online list.

Moreover, we will cover how to create an Indexed Column in SharePoint Online List.

Indexed Column in SharePoint List

We use the index column because it speeds up data retrieval from SharePoint items, especially when we have a limit of 5000 thresholds in the list view.

An index is a concept of “SQL Server.”

We can add an index up to 20 columns in a SharePoint List or Document library.

Note:

Be aware, before it reaches 5000 items list view threshold by creating an Indexed columns.

Scenario:

1. Assume that you have more than 100k items in your SharePoint list, so you will create a view to avoid the threshold error.
2. But when creating a view with a filter in the SharePoint list/document library, SharePoint needs to organize the data and fetch all the list items that match the filter and sort options.
3. To improve this, we can create an index column on a commonly used column necessary for the SharePoint Online list. Due to this, it speeds up data retrieval from SharePoint List items, sorts the data, and provides the filtered data.

But SharePoint indexed columns are supported by limited data types, which are mentioned below:

Supported Columns	Unsupported Columns
Single line of text	Multiple lines of text
Number	Choice
Currency	Hyperlink or Picture
Date and time	Calculated
Choice	Custom columns
Person or group	Person or group
Managed Metadata	External data
Lookup

• To avoid this threshold, SharePoint list view error “The number of items in this list exceeds the list view threshold, which is 500 items. Tasks that cause excessive server load (such as those involving all list items) are currently prohibited.”, create an indexed column in SharePoint Online.
• There are two kinds of indexed columns we can create, as listed below:

1. Simple index:

To build a simple index, choose a column that is not a lookup column under the Primary column for this index. For example, a simple index.

2. Compound index: To create a compound index, select a lookup column as the primary column. Use a different column that is not a Lookup column in the Secondary Column section under the Secondary column for this index. For Example, a compound index.

Now, we will see how to create an Indexed column in the SharePoint Online list.

Create an Indexed Column in a SharePoint Online List

Here, we will see how to create an indexed column in a SharePoint Online List.

• Open a SharePoint Online List. Then Click on the Settings icon -> Select List settings.

• In the Settings window, scroll down and click on the Indexed columns as shown below:

• In the Indexed Column page -> Click on Create a new index, as shown below

• In the Edit index, two options are there:
  1. Primary column: In this, we can select the primary column created by the user as per their needs, including some of the in-built columns.
  2. Secondary column: The secondary column is enabled when we provide a Lookup column in the Primary column. Also, it helps in creating a compound index. (It will be disabled because we didn’t choose the lookup type column in the Primary column.)
• Then click on the Create button.

• In Indexed Columns, we can see that an indexed column got created.

This is how to create a simple column index in SharePoint Online List.

Create a SharePoint online index lookup column

Here, we will see how to create a SharePoint online index lookup column

• Open a SharePoint Online List.
• Click on the Settings icon -> Select List settings.
• In this Settings window, select the Indexed columns as shown below:

• In the Indexed Columns page, click on Create a new index

• On the Edit index page, there are two options:
  1. Primary column for this index: Select a SharePoint column that you want to make a primary column for this index. For Example, I have selected Phone Number (Lookup Data type).
  2. Secondary column for this index: Select a SharePoint column that you want to make a secondary column for this index. For Example, I have taken the Monthly Revenue (Date and time Data type).
• Click on the Create button.

• Here, we can see how the compound column index appear as shown below.

This is how to create the SharePoint index lookup column.

Delete an Index column from a SharePoint Online List

Here, we will see how to delete an existing index column from a SharePoint Online List.

• Go to the Indexed column page and click on the index you must delete.

• In the Edit index window, click on the ‘Delete’ button -> A dialog box pops up asking, ‘Are you sure you want to delete this index’?
• Click on the OK button as shown below.

This is how to delete an existing index from a SharePoint Online List.

Conclusion

In this SharePoint tutorial, we learned how to create an indexed column in a SharePoint Online list, and also I have explained how to delete an indexed column from a SharePoint Online list. In this way, we can avoid the error, “The number of items in this list exceeds the list view threshold, which is 500 items. Tasks that cause excessive server load (such as those involving all list items) are currently prohibited.“

You may also like the following SharePoint tutorials:

• SharePoint Online List Version History
• Rename a SharePoint Online List
• How to Format a SharePoint Online List Column using JSON?
• SharePoint list calendar view formatting
• Create a Lookup Column in SharePoint Online List