How to Create Multiple PivotCharts with Different Filters in Excel
Question details
The user wants to create multiple PivotCharts connected to the same dataset but needs each chart to display different filters independently.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating an analytical dashboard where multiple charts visualize different aspects, fields, or filtered views of a single source dataset.
- Observed behavior
- When multiple PivotCharts are created from the same PivotTable, changing a filter on one chart automatically applies to all other charts connected to that PivotTable.
Format your source data as an Excel Table (Ctrl+T) before creating your PivotTables, ensuring that any future data updates easily sync across all your independent charts.
Create a Separate PivotTable for Each PivotChart
Because a PivotChart is permanently linked to its source PivotTable's filters, you must build independent PivotTables for each chart to allow for distinct filtering.
PivotCharts and PivotTables share a two-way connection. If you modify the fields or filters on the chart, the PivotTable updates immediately, and vice versa. Therefore, attempting to duplicate a chart from a single PivotTable will result in synchronized filters.
By setting up multiple PivotTables from the exact same data source, you create independent caching instances. This lets you apply unique filters to each resulting PivotChart without affecting the others.
Highlight your source dataset, navigate to the 'Insert' tab on the ribbon, and click 'PivotTable'. Choose to place it on a new worksheet.
Click anywhere inside your newly created PivotTable. Go to the 'Insert' tab, select 'PivotChart', choose your desired chart type, and apply the specific filters for this view.
Return to your original source dataset and insert a second PivotTable. Place this on the same worksheet as the first PivotTable, ensuring there are enough empty rows between them so they do not overlap when expanded.
Click inside the second PivotTable, insert another PivotChart, and set up your distinct fields and filters. Repeat this process for as many independent charts as you need.
Create Independent PivotCharts easily with WPS Office
WPS Spreadsheet provides powerful and intuitive PivotTable and PivotChart features. You can easily build dynamic dashboards with multiple independent charts while keeping full compatibility with your existing Excel files.
- 1. Select Source Data: Open your spreadsheet in WPS Office, select your data range, and format it as a table.
- 2. Insert Multiple PivotTables: Go to the 'Insert' tab, select 'PivotTable', and create separate tables for each view you need.
- 3. Add PivotCharts: Click inside each PivotTable, navigate to 'Insert', and select 'PivotChart' to apply independent filters.
Frequently Asked Questions
Can I disconnect a PivotChart from its source PivotTable?
No, Excel does not allow you to sever the live connection between a PivotChart and its source PivotTable while keeping it interactive. If you only need a static visual, you can copy the PivotChart and paste it as a standard Image.
How can I control multiple independent PivotTables with one filter?
You can use a Slicer to control multiple PivotTables. Insert a Slicer for one PivotTable, right-click the Slicer, choose 'Report Connections', and check the boxes for all the other independent PivotTables you created. They will now all respond to the single Slicer.
Why do my PivotTables overlap when I change filters?
If separate PivotTables are placed too close to one another on the same worksheet, expanding a field or clearing a filter might require more rows or columns than are available, causing an overlap error. Always leave ample blank rows and columns between multiple PivotTables.
