Applies to: SharePoint Online, SharePoint Server 2016, SharePoint Server 2013 Enterprise, SharePoint Server 2013, SharePoint Server 2010, SharePoint Online Small Business.
Filtering minimises the total number of items shown in a view to merely the data most significant to you. A filter chooses items that meet specific criteria, like location, type of item, or a range of prices.
Using indexes and filters can also assist you with regaining access into your data or preclude passing the List View Threshold. A filter can return data alone or within a group or sorted for efficient formatting.
Indexes work with filters to increase their performance. If the number of items in your list or library exceeds the List View Threshold, you can add indexes to existing columns. You can then apply filters to get the displayed data under the List view Threshold of 5000 items.
Using filtered views
Filters reduce the amount of data returned by selecting items based on operations (such as greater than or less than) and matching data. When you create a filtered view, your first indexed column in the filter expression should reduce the data set the greatest amount.
For example, filtering on a state or date field can often reduce a data set considerably. If you’ve got 15,000 items, you’ll want to have the first index return less than 5000 items. If your data set doesn’t have over 5000 items, it’s still a good practice.
SharePoint selects the first indexed column in a query, and then uses the rest of the columns to select your data. Other columns you specify in the view filter may or may not be indexed. The view does not use those indexes, even if the result of the filtered view returns less than the List View Threshold.
For example, you have a query: size = large AND colour = red. In the list, size is not indexed, but colour is. As long as there are fewer than 5000 “red” items in the list, the query succeeds in a large list. However, if you have a query size = large OR colour = red, though database may find all the red items, it must scan the complete list to find all the large items. If there are more than 5000 items returned, the query is throttled.
If you use two or more columns in the filter expression, the determining index or indexes should use an AND operator. For example, if you want to return Dogs from a large list of animals. You have an unindexed column called Species where you have Dog as a value. If you just query for Species = Dog, your query will be throttled.
However, if you have an indexed column called Class, your query becomes Class = Mammals AND Species = Dog. You could also search for cats and dogs with the query Class = Mammals AND (Species = DOG OR Species = Cats). The second query selects all Mammals, and then filters to Dogs and Cats.
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. For more info, see Empty the recycle bin or restore your files.
Create or modify a filtered view based on column indexes
If the columns that you’ll be using to filter don’t have indexes, you’ll want to start by creating indexes. For more info, see Add an index to a SharePoint column.
After you index a column, you can add it when you create or modify a view, and then use it to filter the view. Before creating a view, you may want to add more columns to the list to enable more flexibility for sorting, grouping, and filtering. For sorting and filtering, you can choose columns that are in the data set, but not displayed.
Create a view
- Open the List or Library where you want to create a view.
- Depending on your version, do one of the following:
- In SharePoint 2016, 2013, 2010, or SharePoint Online Classic experience, press the List or Library tab, and then choose Create View.
- In SharePoint Online or group-connected SharePoint, select the down arrow on the View Options button, and then pick Manage views or Edit current view.
- Scroll down to the Views section, and then click Create view.
- On the View Type page, select your preferred view. If unsure, click Standard View.
- Write a View Name.
- Change the web address for the view, or accept the default. You can return back and modify this later.
- In the Filter section, choose Show items only when the following is true, and then decide how you want to filter the items based on one of the columns that is indexed. For example, to create a view of only items that changed today, pick the Modified (Indexed) column and the is equal to condition, and then enter [Today].
Note: If you see no columns that state (Indexed) after the name, you do not have any indexed columns available for that list or library. You must first Create a simple or compound index.
- To cap the number of items listed on each page, scroll down to Item Limit and set the Number of items to display value. When you design a view, the automatic number of items to be shown is 30.
- There are two options you can select setting an item limit on a page:
- Display items in batches of the specified size which forms a page-by-page display of the data that is helpful when you are searching items in a non-interactive way. When the total items exceed your limit setting, the display will be paged.
- Limit the total number of items returned to the specified amount produces a hard limit that could or could not return the complete results of your filter operation. This can be valuable when experimenting with your view, designing a prototype, or you just want to retrieve the highest values in a view.
- Under Style, you can click Preview Pane to view all the information from items in your list in a vertical pane format. The preview pane is one of the View Styles you can select under Style when altering or creating a view.
You can rapidly navigate the data by hovering over the item title in a scrolling region on the left side of the page, to check all the column values of the current item vertically listed on the right side of the page. Less initial data is presented, and this facilitates quicker viewing. This style is also very resourceful when your list is broad or has several columns and would demand horizontal scrolling to view the data.
- Press OK.
There are additional steps you can do with a view, but this will suffice to view data that has surpassed the List View Threshold if you’re blocked. You can also sort, or group by, set totals, expand folders, and optimise the view displayed with a mobile device.
To Modify a view
- Open the specific List or Library for making a view.
- Do one of the following:
- In SharePoint 2016, 2013, 2010, or SharePoint Online Classic experience, select the List or Library tab, click the view under Current View, and then press Modify View.
- In SharePoint Online, press the down arrow on the View Options button, and then pick Edit current view.
- Scroll down to the Views section, and then select a view.
- You can then edit settings as outlined in steps 5 through 9 in the Create a view procedure above.
- Press OK.
Setting up filters
For a view to swiftly filter through vast items, the first column in the filter has to be indexed. Other columns you define in the view filter may or may not be indexed, but the view does not apply those indexes. The first column of the filter should yield fewer items than the List View Threshold.
If the first column of the filter returns greater items than the List View Threshold, you can set a filter with two or more columns. When you impose a filtered view that utilises two or more columns, use an AND operator to restrict the total number of items captured. However, even in this case, you still must specify as the first column in the filter the column that has the greatest chance of collecting the lesser amount of data. Using an OR filter nearly consistently raises the amount of items returned and will be ineffective under these conditions.
For more info on views, see Create, change, or delete a view of a list or library.
Important: Although you can index a single lookup column to enhance performance, using an indexed lookup column to avoid exceeding the List View Threshold is ineffective. Use a different kind of column as the primary or secondary index.
Important: If you’re filtering a list by an indexed field, inspect the recycle bin for deleted items from the list or library. Items in the recycle bin count against the List View Threshold, and aren’t erased from the back-end database until they are deleted from the recycle bin. If the total number of filtered items in the list and in the recycle bin exceeds the List Threshold limit, then you may receive a partial set of results, or be blocked. For further info, see Empty the recycle bin or restore your files.
Filter on dynamic dates or current user
There are two keywords that you can employ to filter in relation to the current day [Today], or the current user [Me]. These are dynamic as they vary with the user or the date.
Using [Me] can be helpful when you just want to view your documents in a huge library. [Me] operates on people based columns such as Created by, or Modified by. For example, to check all documents you’ve created, set a filter with the Created by column equal to [Me]. If you’re an editor and want to view the last files you were working on, set a filter on the Modified by column to is equal to [Me]. To view both, create two filters connected by Or.
The [Today] keyword runs on date based columns. You can use maths to capture ranges that differ with today’s date. For example, to view all documents that were edited in the last 30 days, you can produce a filter on the Date column that’s greater than or equal to [Today] – 30. To skip the current day, define a second filter on the Date column to is less than [Today] and connect them with And.
Quick filtering from your list or library
You can also rapidly filter straight from your list or library, without inserting the overhead of another view. There is no capability of setting operators such as greater than or equal to. The list of filters is sourced from the data in the column. For example, with a column for departments, you could filter on the HR or IT department.
- In your list or library, hover over the relevant column header you want to filter, and press the down arrow.
- In the lower part of the drop-down box, select to the left/right of the desired values you want to filter on. A check box will emerge with your choice if they’re not displaying at first.
- When you’re finished, and want to return to display all values, press the down arrow, and then choose Clear filters [from column name], and then select Close.
Typical filtered views
The following are some suggestions for typical views that would complement effectively with indexed columns:
|To filter by:||Index the:||For example:|
|Recently changed items||Modified column||To view only items that have changed in the past week, apply the filter Modified is greater than[Today]-7.|
|New items||Created column||To create a view of only those items that were added in the past week, apply the filter Created is greater than [Today]-7.|
|My items||Created By column||To create a view of only those items that you added, apply the filter Created By is equal to [Me].|
|Items due today||Due Date column (created by you in a list or library)||To create a view of only those items with a due date of today, apply the filter Due Date is equal to [Today].|
|Discussion board updates||Last Updated column||To create a view of only the discussions that were updated in the past month, apply the filter Last Updated is greater than [Today]-30.|
|Archive files in a document library||Date Modified||To create a view of documents not changed since the year 2016 (that you then want to archive), apply the filter Date Modified is less than December 31st, 2016.|
|Find a subset of financial data||Region, Year (as two simple indexes)||To create a view of financial data for the Northeast region in 2015, apply the filter Region equals “NE” AND Year equals 2015 because the Region column is likely to have less values than the Year column.|
Note: Even when you create a filtered view influenced by column indexes, if you exceed the List View Threshold, particular extra operations might still be suspended since they demand full access to the whole list or library. These operations encompass the following: adding or deleting an index, creating a sort in the view definition; displaying a column total; and adding, updating, or deleting calculated fields. If this occurs, just carry out the operation during the Daily Time Window, when limits are relaxed.
Supported and unsupported columns for indexing and filtering
|Supported Column Types||Unsupported Column Types|
|Single line of text||Multiple lines of text|
|Choice (single value)||Choice (multi-valued)|
|Currency||Hyperlink or Picture|
|Date and Time||Custom Columns|
|Person or Group (single value)||Person or Group (multi-valued)|
|Managed Metadata||External data|
This information was compiled using information courtesy of © Microsoft 2020. All rights reserved.