Excel Chart Filter: How to Show or Hide Data

An Excel chart filter lets you show or hide selected data series or categories without changing the chart’s source data. In Excel for Microsoft 365 on Windows, select the chart, click the funnel-shaped Chart Filters button beside it, choose what to display, and click Apply.

What an Excel chart filter does

A chart filter changes which parts of a chart are visible. Depending on the chart, you can filter series, such as Sales or Expenses, or categories, such as months, regions, or product names. For example, a chart with monthly sales for three product lines can show only one product line, or omit selected months, while leaving the underlying worksheet values intact.

This is a display choice, not a data-cleaning operation. The filter does not delete rows, rewrite cells, or permanently remove a series from the chart. You can usually restore a hidden item by reopening the chart filter and selecting it again. That makes chart filters useful for quick exploration and for focusing a chart before presenting it.

Chart filters are distinct from worksheet filters. A worksheet filter hides rows in a data range; a chart filter controls what the chart displays. Those two approaches can interact, but they are not the same control and may suit different tasks.

How to filter a chart in Excel for Microsoft 365

Use the chart’s on-object filter when you want a quick way to hide or show items in a standard chart:

Click the chart once to select it. The chart-specific buttons should appear beside it.
Click the funnel-shaped Chart Filters button.
In the pane, use the Series or Categories list, depending on what you want to filter.
Select the checkboxes for the items to show. Clear the boxes for items to hide.
Click Apply to update the chart.

For instance, suppose the chart has a series for North, South, and West and categories for January through December. To focus on the South region, leave South selected under Series and clear North and West. To show only the first quarter, keep January, February, and March selected under Categories and clear the remaining months. The source table remains available for other calculations and charts.

If you do not see the funnel button, first confirm that a chart is selected. It may not be available for every chart type or chart context, and PivotCharts have their own field-filter controls. You can also make broader changes through Chart Design > Select Data, which opens the data-source controls for the selected chart.

Filter a series or filter categories?

Series and categories answer different questions. A series is a related set of values, commonly drawn as a line, a group of columns, or a legend entry. Categories are the labels along the chart’s category axis, often dates, departments, or products. Filtering a series removes a data group from view; filtering categories removes selected positions or labels across the displayed series.

Consider a clustered column chart where each month is a category and each product is a series. Hiding the Basic product series lets you compare the other products across all months. Hiding several months instead narrows the time period for every product that remains visible. If the chart is hard to read because there are too many products, filter series. If the point is to examine a shorter time span, filter categories.

Use a series filter to compare fewer groups, such as selected regions or product lines.
Use a category filter to focus on selected dates, locations, or items along the axis.
Use both when a chart contains many groups and many categories, but keep enough context for the comparison to remain meaningful.

Chart filters versus worksheet filters

A worksheet AutoFilter is a better choice when you want to inspect or manage the underlying records as well as change what appears in a chart. Select a cell in the data range and choose Data > Filter, then use the dropdown in a column heading to select records. In many standard charts, rows hidden by filtering are excluded from the plotted data by default, so the chart changes along with the visible records.

There is an important setting behind that behavior. Select the chart, choose Chart Design > Select Data, and in the dialog choose Hidden and Empty Cells. The Show data in hidden rows and columns option controls whether hidden worksheet data is plotted. If it is selected, a chart can continue to show values from rows or columns that are hidden; if it is cleared, hidden data is omitted. The exact dialog appearance can vary slightly by chart and Excel build.

This setting helps explain a common puzzle: filtering the worksheet seems to have no effect on a chart. Check whether the chart is set to show hidden data, and confirm that the chart’s source range actually includes the filtered rows. A chart filter, by contrast, targets chart items directly and does not require hiding worksheet records.

When to use a slicer or a PivotChart filter

A chart filter is convenient for a one-off change, but it is not always the clearest way to let someone explore a report. A slicer provides visible buttons for filtering a Table or PivotTable, making the available choices easier to discover. For an ordinary Excel Table, select a cell in the table and choose Table Design > Insert Slicer. Choose the fields you want to filter, then use the slicer buttons. A chart based on the table can reflect the filtered rows, subject to the chart’s hidden-data setting.

PivotCharts work differently because their data comes from a PivotTable. Use the PivotChart field buttons, PivotTable filters, or a slicer connected to the relevant PivotTable to control the displayed data. A standard chart’s Chart Filters button is not a substitute for these PivotChart controls. If a slicer should control more than one PivotTable or PivotChart, check that those reports share a compatible data source and use the slicer’s report-connection options.

Choose the control that fits the audience:

Chart filter: quick, chart-specific selection with little setup.
Worksheet filter: filters records in the source range and can support data review as well as chart updates.
Slicer: a visible, clickable control suited to dashboards and repeated use.
PivotChart field filter: the appropriate route for filtering data summarized in a PivotChart.

Limitations and common problems

A chart filter controls what is drawn, but it does not necessarily change every part of a chart’s presentation. For example, hiding a series can affect the legend and the remaining plotted marks, while chart titles, axis formatting, and labels may still need attention. Review the finished chart to ensure its title and labels describe the data that is currently visible.

If a category or series is missing from the filter list, check the selected chart’s source range in Chart Design > Select Data. The item may not be included in the source, or the chart may be based on a range that has changed. If the chart uses a PivotTable, use PivotChart filters instead. Also check whether cells contain blanks or whether the chart type handles the data layout differently than expected.

Other useful checks include:

Click the chart itself before looking for Chart Filters; selecting a cell near it will not show chart controls.
After changing checkboxes in the filter pane, click Apply if the pane presents that button.
Reopen the filter and check that the intended series or categories are selected.
Review Hidden and Empty Cells if worksheet filtering does not change the chart as expected.
Use Chart Design > Select Data to verify the series names, values, and category-label range.

Excel on Mac and Excel for the web

The steps above describe Excel for Microsoft 365 on Windows. Excel for Mac has chart editing tools too, but the ribbon layout and the placement or availability of on-chart controls can differ by release. Select the chart and look for its chart filter control; if it is not offered, use the chart’s data-selection options or filter the worksheet source instead. Keyboard shortcuts are not needed for the basic chart-filter steps.

Excel for the web also provides chart editing, but its available controls can differ from the desktop application, and some chart operations may be limited. If a chart filter control is missing in the browser, open the workbook in the desktop app and use the chart’s filter or data-source settings there. Before relying on a filtered view in a shared workbook, confirm that the chart and its source behave as intended in the version your audience will use.

Frequently Asked Questions

Does an Excel chart filter delete data?
No. It hides selected chart series or categories from the display. The worksheet values remain unchanged, and you can select the hidden items again in the chart filter.

How do I show hidden data in an Excel chart?
Select the chart and open Chart Design > Select Data > Hidden and Empty Cells. Select Show data in hidden rows and columns if you want the chart to plot values in hidden worksheet rows or columns.

Why does filtering my worksheet not change the chart?
The chart may be set to plot hidden rows, its source range may not include the filtered records, or it may be a PivotChart. Check Hidden and Empty Cells and the chart’s source; for a PivotChart, use its PivotTable or field-filter controls.

Can I filter a chart by month or region?
Yes, if month or region is represented as a category or series in the chart’s source data. Open Chart Filters and select the appropriate items under Categories or Series. For repeated interactive filtering, a slicer or PivotChart filter may be easier to use.

Can one slicer control multiple charts?
It can control multiple compatible PivotTables or PivotCharts when they share a suitable source and are connected through the slicer’s report connections. A slicer for an ordinary Table does not automatically provide the same multi-report connections as a PivotTable slicer.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.