Limit Pivot Chart Data in Excel with Filters and Top 8

To limit the data shown in an Excel PivotChart, filter its PivotTable fields: use the field dropdowns on the chart or table, apply a Top 10 value filter, or add a slicer. A PivotChart is linked to its PivotTable, so filtering the chart usually filters that table as well; it does not have a separate, independent data range like a standard chart.

What it means to limit PivotChart data

People use “limit PivotChart data” to mean a few different things. You might want to show fewer categories, restrict the underlying records included in calculations, display only the highest-value items, or simply make a busy chart easier to read. The best method depends on which of those outcomes you need.

A PivotChart visualizes the summarized results of a PivotTable. Its chart categories and series come from the fields arranged in that table, and its plotted values come from the table’s calculations, such as Sum of Sales or Count of Orders. Filtering a field changes which summarized items appear. It does not necessarily change the PivotTable’s source data or permanently remove records from the workbook.

This distinction matters: hiding a category from the chart is different from excluding its source records from analysis. A field filter is usually right for an interactive report. If particular records must never contribute to the result, change the source data or deliberately filter the records in the PivotTable’s data model or source process.

Filter categories from the PivotChart

In Excel for Microsoft 365 on Windows, a PivotChart normally displays field buttons that let you filter the fields used in the chart. Use the dropdown on the relevant category or series button, then select or clear items. For example, a chart with Region on the category axis can be limited to North and West by clearing the other regions.

Select the PivotChart so its chart tabs and field buttons are available.
Find the field button for the categories or series you want to limit, such as Month, Product, or Region.
Open its dropdown, clear Select All if needed, select the items to keep, and choose OK.
Check the chart and its associated PivotTable to confirm that the same filter is appropriate in both places.

You can also apply the filter from the corresponding field dropdown in the PivotTable. This can be easier when the chart is small or the field buttons overlap. Depending on the field, the dropdown can offer item checkboxes and options such as Label Filters or Value Filters. A label filter limits items by their names—for instance, product names beginning with a certain text—whereas a value filter selects items based on their summarized numbers.

To restore the full chart, reopen the field dropdown and choose Clear Filter, or select all the items again. Clearing one field’s filter does not necessarily clear filters on other fields, so check each relevant dropdown if some categories are still missing.

Show only the top or bottom items

A Top 10 value filter is useful when a chart has many categories and the reader mainly needs to see the leaders or laggards. It can show, for example, the products with the highest total sales, rather than every product in a long catalog. The number 10 is adjustable; the feature can be configured for a different count and for top or bottom items.

Open the dropdown for the row or category field in the PivotTable, or the equivalent field button on the PivotChart.
Choose Value Filters, then Top 10.
In the dialog, choose Top or Bottom, enter the number of items, and select the basis for the filter, such as Items.
Choose the value field to rank by, such as Sum of Sales, and confirm the filter.

The chosen measure matters. If the report contains both sales revenue and units sold, a Top 10 filter based on revenue can produce a different chart from one based on unit count. Read the complete filter description in the dialog before confirming it. A common mistake is choosing the right number of items but ranking them by the wrong value field.

A Top 10 filter selects summarized categories; it does not mean that Excel has kept only ten original transaction rows. Each displayed product can still represent many source records. The chart’s values continue to reflect the underlying records included in each selected product’s PivotTable summary.

Use slicers for interactive filtering

A slicer provides clickable buttons for filtering a PivotTable or PivotChart. It is especially helpful in a report that other people will use, because readers can select a region, year, or product without opening dropdown menus. To add one, select the PivotTable and choose PivotTable Analyze > Insert Slicer, then select the fields for which you want controls.

Click a slicer button to filter to one item. Hold Ctrl while clicking on Windows to select multiple non-adjacent items; the slicer’s multi-select button can also be used when available. Use the clear-filter control on the slicer to return to all items. For date fields, a timeline may be a more convenient control: select the PivotTable and choose PivotTable Analyze > Insert Timeline, then choose a date field.

Slicers can connect to more than one PivotTable when those tables share a compatible data source or PivotCache. Select the slicer and use the Slicer tab’s Report Connections command to choose the PivotTables it should control. If a report contains several PivotCharts, check these connections: a slicer may appear to affect only one chart simply because the other chart’s PivotTable is not connected.

Limit the source records, not just the displayed categories

Filtering the chart’s visible categories does not necessarily exclude records from every calculation or report elsewhere in the workbook. If the goal is to remove records that should not be analyzed—for example, test transactions or cancelled orders—use a deliberate source-data or PivotTable filtering method and verify the resulting totals.

One option is to filter a suitable field inside the PivotTable, such as Order Status, so that only Completed orders are included in the displayed analysis. Another is to prepare a separate, correctly defined source table containing only the records that belong in the report. For repeatable data-cleaning steps, a Power Query process can be configured to load a filtered result. Choose a method that makes the exclusion rule visible and maintainable rather than relying on someone to remember a temporary worksheet filter.

Do not assume that filtering rows in the worksheet’s source table automatically removes those rows from a PivotTable. A PivotTable can include rows that are hidden by a worksheet filter. If the PivotTable uses that table as its source, refresh it and verify whether the records are still represented. Worksheet visibility and PivotTable field filters are separate controls.

When you add rows to a well-structured Excel Table, the table can expand to include them, but the PivotTable generally still needs a refresh before the new records appear in its results. Use Data > Refresh All to update workbook connections and PivotTables, or select the PivotTable and choose PivotTable Analyze > Refresh. If the PivotTable uses a fixed cell range rather than an Excel Table, adding rows outside that range may require changing its source.

Example: reduce a crowded monthly sales chart

Suppose a PivotTable summarizes sales by Month and Product, and the linked PivotChart is difficult to read because it plots 40 products. If the question is which products lead in total sales, apply a Top 10 value filter to Product and rank by Sum of Sales. The chart will show a smaller set of product categories, while the Month field can remain on the chart so the monthly pattern is still visible.

If the audience instead needs to explore a few products of its own choosing, a slicer for Product may be better than a fixed Top 10 filter. Add a Region slicer as well if comparing locations is useful. The reader can then select products and regions interactively, while the chart recalculates from the PivotTable’s summarized data.

If the goal is to show a fixed subset, such as only products in a particular department, filter Department or Product in the PivotTable. If the goal is to exclude transactions marked Cancelled from the calculations, filter Order Status or prepare a source that excludes them. These are different reporting questions, even if all three methods make the chart look less crowded.

What PivotCharts cannot filter independently

A PivotChart is not an ordinary chart with an editable list of cell references for each series. Its data structure is controlled by the associated PivotTable, so the standard chart workflow of choosing Select Data and deleting individual series is not the normal way to limit a PivotChart. Use PivotTable fields, value filters, or slicers instead.

Also distinguish filtering from hiding the field buttons. To make a chart less cluttered, you can select the chart and use PivotChart Analyze > Field Buttons > Hide All. This only removes the on-chart controls; it does not filter any categories or change the plotted data. You can still filter through the PivotTable or use slicers after hiding those buttons.

If you need a chart that shows a hand-picked combination of values without changing the PivotTable view, consider creating a separate standard chart from a helper range. That range can contain references to selected PivotTable results or another prepared summary. This adds maintenance work, but it separates the chart’s displayed range from the PivotChart’s shared field filters. Make sure the helper range updates correctly when the PivotTable changes.

Common problems and practical fixes

The filter changed the PivotTable too. This is normal for a linked PivotChart. Use a separate standard chart or a separate PivotTable if the two views need independent filters.
New source rows do not appear. Refresh the PivotTable. If the source is a fixed range, update it through PivotTable Analyze > Change Data Source; using an Excel Table as the source can make later additions easier to include.
Hidden source rows still affect results. A worksheet filter is not a reliable way to exclude records from a PivotTable. Apply a PivotTable filter or build a source that explicitly leaves those rows out.
The Top 10 chart shows unexpected items. Reopen the filter and confirm the value field and aggregation, such as Sum rather than Count. Also check whether another field filter is changing the set of items being ranked.
The chart has no visible field buttons. The buttons may have been hidden. Use PivotChart Analyze > Field Buttons to show them again, or filter from the associated PivotTable instead.
A slicer does not affect every chart. Select it and check Slicer > Report Connections. Only connected PivotTables respond, and compatible sources may be required to share a slicer.

On Excel for Mac, the PivotTable and PivotChart concepts are similar, but ribbon labels and the placement of controls can differ by release. Look for the PivotTable or PivotChart analysis tab and its filter, slicer, or field-button controls. Excel for the web also supports many PivotTable interactions, but some creation and connection controls may be more limited than in the Windows desktop application; if a command is missing, open the workbook in desktop Excel.

Frequently Asked Questions

Can I filter a PivotChart without changing its PivotTable?
No, not as an independent PivotChart filter. A PivotChart shares its field layout and filtering with its associated PivotTable. Use a separate PivotTable or a standard chart based on a separate range when the views must be independent.

How do I show only the top five categories?
Open the category field’s dropdown, choose Value Filters > Top 10, set the count to 5, and select the value field to rank. The menu name remains Top 10 even when you enter a different count.

Does hiding a PivotChart field button limit the data?
No. Hiding field buttons only removes controls from the chart display. Apply a field filter or value filter to change the plotted categories, or use a slicer to provide another filtering control.

Can a slicer control multiple PivotCharts?
Yes, when the charts’ PivotTables can share the slicer. Select the slicer, open Report Connections on the Slicer tab, and select the PivotTables to control. Confirm each chart responds after selecting a slicer item.

Why does my PivotChart include rows hidden in the source worksheet?
Worksheet filters and hidden rows do not necessarily remove records from the PivotTable’s source. Apply a filter in the PivotTable itself or prepare a source dataset that excludes the records, then refresh and check the results.

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.