About filtering data in a chart

Filtering a field (autofiltering) You can filter fields in the category, series, and filter areas. When you filter a field, you select one or more items of data in the field that you want to view, and hide the other items.

Filtering a field is particularly helpful when you have a large amount of source data but you want to focus on specific areas. When you filter a field, you can display the data for a single item, or you can select some items to display and other items to hide. You can filter on more than one field at a time to further narrow the focus.

Category field Region showing all items (not filtered)

Category field Region filtered to show South and West region items

Conditional filtering You can apply a filter to series or points to show the top or bottom n items based on a total. For example, you can filter for the three cities that generated the most sales or the five products that are least profitable. Instead of n items, you can also choose to filter for a certain percentage of items. For example, you can filter for the top 25% or bottom 25% of salespeople based on the number of orders handled. If there are 40 salespeople, after the filter is applied, you will see data for 10 people.

Filters are additive. Each filter you apply is added to the ones you already have in effect.

You can display all data for all fields by turning off autofiltering. However, the filter settings are retained, so that when you turn autofiltering back on, the same data that was previously displayed or hidden is again displayed or hidden.

Filter settings are retained when you move fields to change the layout. Also, if you remove a field and later add the field back to the layout, the same items are again hidden.

If you don't want to retain your filter selections, make sure the AutoFilter button is not selected before you start selecting items to filter. If the button is not selected, selecting new items to filter automatically turns filtering on and removes your former filter settings.

When items in a field are hidden by autofiltering, the drop-down arrow in the field label is blue. Click the blue arrow to see the filter that is in effect.

If you don't want users to change how you've filtered the data, you can make filtering unavailable to users at run time.