Model-Based PivotCharts

Creating the Dashboard Sheet

Charts and slicers live on their source sheets by default. A dashboard sheet collects everything in one place without exposing the underlying pivot tables to stakeholders.

1. Click the + at the bottom of the workbook to create a new sheet — name it Dashboard;
2. For each chart: right-click the chart → Move Chart → Object In → Dashboard;
3. Arrange charts on the dashboard sheet — resize as needed for clarity;
4. For slicers: right-click → Cut from the source sheet, then Ctrl+V on the Dashboard sheet to paste.

Connecting Slicers to Multiple Pivot Tables

By default, a slicer controls only the pivot table it was created from. To make one slicer update all three charts simultaneously, it must be explicitly connected to the other pivot tables using Report Connections.

1. Right-click the slicer on the dashboard;
2. Select Report Connections;
3. Tick all pivot tables that should respond to this slicer — the dialog shows the sheet name for each one;
4. Click OK — the slicer now controls all connected pivot tables simultaneously.

Task

Your task is to build a one-page interactive dashboard using the three PivotTables already in the workbook. Work through the four steps below in order.

Step 1 — Create a PivotChart from each PivotTable

For each of the three PivotTable sheets, insert a PivotChart and move it to the Dashboard sheet.

• PT_Region: insert a Clustered Bar chart. Move it to the Dashboard sheet.
• PT_Trend: insert a Line with Markers chart. Move it to the Dashboard sheet.
• PT_Segments: insert a Clustered Column chart. Move it to the Dashboard sheet.

After moving all three, switch to the Dashboard sheet and arrange the charts into a clean two-row layout. The bar chart and line chart should sit side by side in the top row, and the column chart across the full width in the bottom row. Resize as needed so all three are clearly visible without scrolling.

Step 2 — Clean up the charts

Before adding slicers, make the dashboard look professional:

• Turn off sheet gridlines: View tab → uncheck Gridlines.

• Hide all field buttons: right-click each chart → Field Buttons in Ribbon → Hide All.

• Replace the default chart titles with question-style titles:

  • Bar chart: Which region and category leads on revenue?
  • Line chart: Is revenue growing month on month?
  • Column chart: Which segment places the most orders and at what average value?

Step 3 — Add slicers and connect them

Add two slicers to the Dashboard sheet and connect each one to all three PivotTables.

Go to the PT_Region sheet, click inside the PivotTable, and insert a slicer for Region (from the Customers table). Cut the slicer and paste it onto the Dashboard sheet.

Go to the PT_Region sheet again, insert a second slicer for Category (from the Products table). Cut and paste it to the Dashboard sheet.

Right-click the Region slicer → Report Connections → tick PT_Region, PT_Trend, and PT_Segments → OK.

Repeat for the Category slicer.

Position both slicers neatly to the side of or above the charts. Add a text label above each slicer (Insert → Text Box) indicating what it filters.

Verification: click North on the Region slicer. Confirm that all three charts update simultaneously. Then click Bikes on the Category slicer. Confirm again. Clear both slicers before moving to Step 4.

Step 4 — Hide the PT_ sheets and run a final check

Right-click each of the PT_Region, PT_Trend, and PT_Segments tabs and select Hide.