How to Create an Interactive Excel Dashboard with PivotTables and Slicers
Question details
The user needs to build an interactive dashboard to visually track landowner engagement data using slicers and PivotTables.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Creating a dynamic reporting dashboard to track agreement status, case risks, and statutory power needs from raw tracker data.
- Observed behavior
- Transforming raw tracker data into a formatted, visually interactive dashboard with clickable slicers to filter high-risk cases and agreement statuses.
Ensure your raw data contains clear, unique column headers with no blank rows or columns, as this is essential for generating an accurate PivotTable.
Build an Interactive Dashboard Using a PivotTable and Slicers
Convert your raw tracking data into a Table, generate a structured PivotTable, and insert slicers to build a fully interactive dashboard.
Slicers act as visual filters for your PivotTables. By organizing your data correctly and linking slicers to your metrics, you can create a dynamic dashboard that instantly updates when you click a button.
Select your entire range of tracker data, navigate to the Home tab, and click 'Format as Table' (or press Ctrl+T). Give the table a recognizable name, such as 'LandTracker', to make it easier to reference.
With your new Table selected, go to the Insert tab and click 'PivotTable'. Choose to place the PivotTable in an unused area on the same worksheet or on a completely new worksheet dedicated to your dashboard.
In the PivotTable Fields pane, drag 'Reference Number' into the Values area and ensure the aggregation is set to 'Count'. Drag fields like 'Agreement Status', 'Case Status', and 'Risk Level' into the Rows or Filters areas to structure your case metrics.
Click anywhere inside your PivotTable. Go to the PivotTable Analyze (or Options) tab on the ribbon and click 'Insert Slicer'. Check the boxes for the fields you want to filter by, such as Case Status, Risk Level, Agreement Status, and Willingness to Enter Agreement, then click OK.
Drag and arrange the newly created slicers neatly next to your PivotTable. Apply borders, adjust slicer colors from the Slicer Tools tab, and use conditional formatting on the PivotTable values to easily identify high-risk cases.
Create Dynamic Dashboards with WPS Spreadsheet
WPS Spreadsheet provides comprehensive support for PivotTables, Slicers, and advanced data visualization, making it easy to build professional, interactive dashboards completely for free.
- 1. Open your data file: Launch WPS Spreadsheet and open the file containing your raw tracking data.
- 2. Insert a PivotTable: Highlight your dataset, navigate to the Insert tab on the top ribbon, and click 'PivotTable'.
- 3. Structure your metrics: Build your summary by dragging data fields into the Rows, Columns, and Values sections in the PivotTable configuration pane.
- 4. Add Slicers: Select the generated PivotTable, click the Options tab that appears on the ribbon, and choose 'Insert Slicer' to add visual interactive filters.
- 5. Customize and share: Arrange your slicers, apply conditional formatting to highlight critical data, and save your fully interactive dashboard.
Frequently Asked Questions
Why is the 'Insert Slicer' option grayed out in my spreadsheet?
The Insert Slicer option may be disabled if your file is saved in an older compatibility format (like .xls). Save the file as a modern .xlsx document. Additionally, ensure you have clicked inside an active PivotTable or formatted Table, as slicers require an active data model to be enabled.
Can I connect one slicer to multiple PivotTables on my dashboard?
Yes. Right-click the slicer, select 'Report Connections' (or 'PivotTable Connections'), and check the boxes next to all the PivotTables you want that slicer to control. Note that all linked PivotTables must share the exact same underlying data source.
How do I update my dashboard when new tracking data is added?
If your source data is formatted as a Table, simply paste your new rows at the bottom of the table so they are automatically included. Then, right-click anywhere on your PivotTable and select 'Refresh' to update the dashboard metrics and slicers with the newest data.
How do I customize the size and color of my dashboard slicers?
Click on the slicer to activate the Slicer Tools (or Options) tab on the top ribbon. From there, you can select different color styles, adjust the number of button columns, and manually set the height and width to perfectly fit your dashboard layout.
