Introduction
When building dashboards in Excel, one of the frustrating experiences is realizing that your slicers work perfectly with PivotTables, but your scattered charts refuse to respond correctly.
You try clicking filters expecting your dashboard to behave interactively, but your scattered plots remain unchanged. One might even conclude that Excel is malfunctioning. But no, the Excel is functioning fine.
I recently faced this challenge while building a dashboard for my Excel project. What looked like an issue turned out to reveal one of the most misunderstood concepts in Excel dashboards.
Here is the good news. Once you understand how slicers, PivotTables, and Scattered charts work together, troubleshooting becomes very easy.
In this guide, I’ll walk you step-by-step through connecting slicers to chart plots so your Excel dashboard becomes fully interactive.
⚠️The Problem
When working on a dashboard project, most beginners naturally follow these steps:
Cleaning the dataset and formatting it as an Excel Table.
Creating PivotTables for summaries.
Adding slicers for filtering.
Building a Scatter Chart for trend analysis in a sheet.
Copying PivotTables and charts into a dashboard layout.
Everything looks perfect at the moment, but until you start interacting with the dashboard, and then suddenly:
Slicers control PivotTables but not charts
Charts stop updating
Values look incorrect and may be inflated.
Frustration now begins to build, and many beginners assume Excel is malfunctioning, but Excel is working exactly as designed. The real issue is basically how Excel treats data sources.
🔑 A Key Concept to Understand.
Excel charts can use two different data sources:
PivotTable which Aggregates data (Sum, Count, Average)
Excel Table which uses original row-level data.
Please note: pivot tables summarise information, while Scatter charts require individual records.
🚫Why You Cannot Properly Plot a Scatter Chart from a PivotTable
A scatter chart plots relationships between observations, and each data point represents one row of data. However, PivotTables do not store rows but instead they:
group records,
values,
calculate summaries like sum or Average.
What this means is (X-Y) pairs needed to plot a scatter chart no longer exist; therefore, scatter plots must read table data, not PivotTable summaries.
☑️☑️Correct Architecture for Interactive Dashboards
Raw Data ➡️ Excel Table ➡️ PivotTable ➡️ Slicer
⬇️
Scattered plot(source data from table)
Why does this Architecture Work?
PivotTables act as the filtering engine.
Slicers control PivotTables.
Charts visualise row‑level data.
Filtering flows through the system logically, forcing charts to perform calculations they were never designed to handle. Think of PivotTables as the control engine and charts as the display layer.
🪜🪜 Step‑by‑Step Solution(That actually works)☑️☑️
Step 1. Convert Your Data into an Excel Table
Select your dataset ➡ Go to Insert ➡ Table
Then, rename the table to something like Excel_projectTable. You can give it your preferred name; it does not really matter. Excel Tables are most preferred because they:
expand automatically when new data is added,
maintain references,
synchronize filtering behavior,
allow dashboards to scale without breaking.
Step 2. Build a PivotTable from the Table
Go to Insert ➡️ PivotTable ➡️From Table/Range
The PivotTable is not your chart’s data source, but use it for:
KPIs
summaries
aggregations
slicer control
Step 3. Add Slicers and pivot charts to your Dashboard Control Panel
Inside pivotTable sheets, you can build pivotCharts using either (Bars, column, or pie charts) for summaries
go to pivotTable ➡️ insert ➡️ PivotCharts
Select your preferred chart
For slicers:
Go to PivotTable Analyse ➡️ Insert Slicer
This may include:
Category. Electronics, Fashion, Home Appliances
Brand: Samsung, Nike, Apple
Rating: 3⭐️, 4⭐️,5⭐️Products
Region: Nairobi, Mombasa, Kisumu
Date: Monthly or Quarterly periods
Think of slicers as the control of your dashboard.
Step 4. Let's Build a Scatter Chart That Actually Works with Slicers
Step 4a. Create the Scatter Chart from the Table
Open a worksheet.
Select data from your table
Excel_practiceproject.Copy data for (X-Y) to the opened worksheet.
Highlight the two columns.
Go to Insert ➡️ Scatter Chart.
Set:
. X‑axis ➡️ influencing variable (example fuel prices )
. Y‑axis ➡️ outcome variable (example: goods cost of production)
Then copy the chart into your dashboard sheet.
Step 4b. Create a Helper PivotTable (The Secret Sauce)
Create another PivotTable from the table.
Product ➡️ Rows
X variable ➡️ Values
Y variable ➡️Values
Change aggregation from Sum to Max or Min, this helps avoid values inflation and rename it Scatter_Helper
Step 4C. Convert Pivot Layout
Go to Design → Report Layout → Show in Tabular Form
Each field now appears in its column, and this way, you can copy each column.
Step 4D. Link Helper Data to Dashboard
Go to dashboard ➡️ Right click on an empty cell(to the far right)➡️ Name the two cells e.g(FUEL PRICE-X, Cost of production-Y)
- Copy columns from Scatter_Helper.
Go to Paste Special ➡️ Paste Link.
If you see:
='Scatter_Helper'!A5 on your formula section, then you are correctly linked.
Step 4E. Change the data source of the scattered plot
Change the data source of the chart plot created from the Excel_practiceTable
Right-click on the scattered plot, then:
Go to Select data ➡️ Delete current X Values, and Y values ➡️ replace with values on the dashboard.
Step 5. Connect Slicers to the Helper PivotTable
slicer ➡️ Report Connections.
Select Scatter_Helper.
Now the magic happens.
PivotTables update
Helper PivotTable updates
Linked cells refresh
Scatter chart updates
Why This Works (The Hidden Mechanism)
The architecture:
Slicer ➡️ Helper PivotTable ➡️ Linked Cells ➡️ Scatter Chart
Slicers control PivotTables. The helper PivotTable feeds updated values into linked dashboard cells, allowing the scatter chart to refresh dynamically.
Final Result
An example of a finished dashboard(Before applying filters)
After applying a filter on the slicers
Conclusion
At the beginning, the slicers were working. The scatter chart was not responding, and it seemed like there was a problem with Excel. In reality, nothing was actually broken. The data structure just needed to be organised in a way that made sense to Excel.
Sometimes the best way to improve your analytics is not to get a new tool but to really understand how the current one works. I hope this Guide was helpful. Now, you can avoid static reports but instead create an interactive dashboard.
Happy dashboard building.