How to Connect Multiple PivotTables to One Slicer and Update Chart Titles
Question details
The user needs to filter multiple PivotTables using a single slicer and automatically display the selected slicer value within a connected PivotChart's title.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Creating an interactive data dashboard where one slicer controls multiple datasets, and a related PivotChart title updates dynamically based on the current slicer selection.
- Observed behavior
- The goal is achieved by utilizing Slicer Report Connections to link the PivotTables, and creating a dynamic formula link between a helper cell and the PivotChart title.
Ensure that all the PivotTables you wish to connect are generated from the exact same source dataset or data cache.
Link Multiple PivotTables to a Slicer and Create a Dynamic Chart Title
Use the Report Connections feature to filter multiple PivotTables with one slicer, then link the PivotChart title to a helper cell to display the selected value.
By default, a slicer only controls the PivotTable it was created from. The Report Connections tool allows you to extend this control to other PivotTables that share the same data source. Afterward, a simple formula trick can dynamically feed the slicer's selection into your chart title.
Click anywhere inside your first PivotTable. Go to the PivotTable Analyze tab (or Options tab) on the ribbon, click 'Insert Slicer', check the box for the desired field (e.g., Asset Number), and click OK.
Right-click the border of the newly created slicer and select 'Report Connections'. In the dialog box that appears, check the boxes next to all the other PivotTables you want to filter with this slicer, then click OK.
Select a cell in the filtered PivotTable data, navigate to the Insert tab, and choose PivotChart. The chart will now update whenever you change the slicer selection.
Click an empty cell outside of your PivotTable (e.g., cell H1). Type a formula that references the cell where the filtered slicer value appears in your PivotTable (e.g., =A4). If multiple items can be selected, use a formula like =TEXTJOIN(", ", TRUE, A4:A10).
Click directly on the title of your PivotChart so a solid border appears around it. Next, click inside the formula bar at the top of the screen, type an equals sign (=), and click the helper cell (e.g., H1). Press Enter. The chart title will now dynamically show the selected asset number.
Build Dynamic Dashboards with WPS Spreadsheet
WPS Spreadsheet provides powerful PivotTable, slicer, and charting tools that allow you to seamlessly build interactive dashboards. Easily connect multiple tables and analyze your data at no cost.
- 1. Open your data in WPS Spreadsheet: Import your dataset or .xlsx file into WPS Spreadsheet and insert your PivotTables.
- 2. Insert a Slicer: Select your PivotTable, navigate to the PivotTable Tools tab, and click Insert Slicer.
- 3. Link the PivotTables: Right-click the slicer, select 'Report Connections', and check the relevant PivotTables.
- 4. Make your Chart Title Dynamic: Insert a PivotChart, select the title text box, type '=' in the formula bar, and select your helper cell.
Frequently Asked Questions
Why are my other PivotTables missing from the Report Connections list?
PivotTables will only appear in the Report Connections dialog if they share the exact same underlying source data or PivotCache as the original PivotTable. If they were created from different ranges or tables, they cannot be connected to the same slicer.
Can I show multiple selected slicer items in the chart title?
Yes, but you will need a more advanced formula in your helper cell. Using a function like TEXTJOIN allows you to concatenate multiple visible rows in the PivotTable into a single text string, which can then be linked to the chart title.
Why did my PivotChart title stop updating automatically?
This usually happens if the link between the title and the helper cell is broken. Ensure you clicked the outer border of the chart title (not the text inside) before typing '=' in the formula bar. Also, check that the referenced helper cell hasn't been deleted or overwritten.
Do slicers and Report Connections work in older spreadsheet formats?
Slicers are a modern spreadsheet feature. If you save your workbook in the older Excel 97-2003 (.xls) format, slicers and their report connections will be disabled or converted to static images.
