About filtering

When you filter data, only the data that meets your criteria appears. The data that doesn't meet that criteria is hidden. After you filter data, you can copy, find, edit, format, chart, and print the subset of filtered data.

Table with Top 4 Items filter applied

Filter of top four values

Filters are additive. This means that each additional filter is based on the current filter and further reduces the subset of data. You can make complex filters by filtering on more than one value, more than one format, or more than one criteria. For example, you can filter on all numbers greater than 5 that are also below average. But some filters (top and bottom ten, above and below average) are based on the original range of cells. For example, when you filter the top ten values, you'll see the top ten values of the whole list, not the top ten values of the subset of the last filter.

In Excel, you can create three kinds of filters: by values, by a format, or by criteria. But each of these filter types is mutually exclusive. For example, you can filter by cell color or by a list of numbers, but not by both. You can filter by icon or by a custom filter, but not by both.

Filters hide extraneous data. In this manner, you can concentrate on just what you want to see. In contrast, when you sort data, the data is rearranged into some order. For more information about sorting, see About sorting.

When you filter, consider the following guidelines:

  • Only the first 10,000 unique entries in a list appear in the filter window.

  • You can filter by more than one column. When you apply a filter to a column, the only filters available for other columns are the values visible in the currently filtered range.

  • You can apply filters to only one range of cells on a sheet at a time.

Note:  When you use Find to search filtered data, only the data that is displayed is searched; data that is not displayed is not searched. To search all the data, clear all filters.

See also

Filter a list of data

About sorting

Sort a list of data

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!

×