How to Connect Excel PivotTable Slicers and Charts to a Dashboard
Question details
The user wants to create an interactive dashboard by building PivotTables from a data table, adding specific slicers (like Status, Risk Assessment, and Object), and making pie charts respond dynamically to these slicers.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Building an interactive data dashboard that requires dynamic filtering across multiple visualizations simultaneously.
- Observed behavior
- The goal is to configure the workbook so that multiple dashboard elements, including various PivotTables and pie charts, automatically update when a user modifies selections in the slicers.
Ensure your source data is formatted as an official Excel Table (Insert > Table) so that any future data additions will automatically be included in your PivotTables upon refreshing.
Build and Connect Slicers to Multiple PivotTables and Charts
Convert your data to a table, create multiple PivotTables, insert slicers, and use the Report Connections feature to link the slicers to all relevant charts and tables.
By default, a slicer only controls the specific PivotTable it was created from. To make a slicer act as a master filter for an entire dashboard, you must manually link it to your other PivotTables using the Report Connections menu. Pie charts built from these PivotTables will then inherit the filtered data automatically.
Select your entire source data range and press Ctrl+T (or navigate to Insert > Table). Check 'My table has headers' and click OK.
Go to Insert > PivotTable, select your newly created table as the source, and choose to place it on your Dashboard sheet.
Click anywhere inside your new PivotTable. Navigate to the PivotTable Analyze tab on the ribbon, click 'Insert Slicer', and check the boxes for the fields you want to filter by (e.g., Status, Risk Assessment, Object).
Copy the initial PivotTable and paste it elsewhere on your dashboard to create summaries for your pie charts. With the new PivotTable selected, go to Insert > PivotChart and choose a Pie Chart.
Right-click on your first slicer and select 'Report Connections' from the context menu. A dialog box will appear showing all PivotTables in the workbook that share the same data source. Check the boxes next to all the PivotTables you want this slicer to control, then click OK. Repeat this process for each slicer.
Build Interactive PivotTable Dashboards for Free with WPS Office
WPS Spreadsheet fully supports creating PivotTables, PivotCharts, and linking slicers to multiple tables. You can easily build dynamic dashboards with seamless compatibility with Microsoft Excel formats, without the heavy subscription fees.
- 1. Import your Data: Open your existing Excel file in WPS Spreadsheet and ensure your source data is formatted as a table.
- 2. Create Pivot Reports: Navigate to the Data tab and select PivotTable to create your base reports and summary tables for your dashboard.
- 3. Add Slicers: Click on your PivotTable, go to the Options tab, and click Insert Slicer to add interactive filtering buttons.
- 4. Connect to Multiple Charts: Right-click the slicer, choose Report Connections, and link it to all the PivotTables driving your dashboard charts.
Frequently Asked Questions
Why is the Report Connections option greyed out for my slicer?
Report Connections may be unavailable if your PivotTables are not created from the exact same source data cache. Ensure all PivotTables on your dashboard are built from the same Excel Table or named range.
Can I connect one slicer to PivotTables on different worksheets?
Yes. As long as the PivotTables share the exact same data source, you can check the boxes for PivotTables located on any sheet within the workbook via the Report Connections dialog box.
Why aren't my pie charts updating when I click a slicer?
Ensure that the pie chart is built as a PivotChart directly linked to one of the PivotTables controlled by the slicer. Standard charts linked to static cells or a different data source won't update automatically when slicer selections change.
How do I clear all filters from a slicer at once?
Click the 'Clear Filter' icon (a funnel with a red 'X') in the top right corner of the slicer box, or simply click the slicer to make it active and press Alt+C on your keyboard.
