DEV Community

joseph mwangi
joseph mwangi

Posted on

# How to Connect Slicers with scattered plot on an Excel Dashboard (Beginner-Friendly Guide That Actually Works)

#beginners #tutorial #productivity #data

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:

  1. PivotTable which Aggregates data (Sum, Count, Average)

  2. 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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Select your preferred chart

For slicers:


Go to PivotTable Analyse ➡️ Insert Slicer
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode
  • Copy columns from Scatter_Helper. 

 Go to Paste Special ➡️ Paste Link.
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Dashboard before applying slicer filters

After applying a filter on the slicers

Dashboard after applying slicer filters

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.

Top comments (1)

Collapse
 
julesindata profile image
Jullian Musyoka •

This was so helpful! Thanks a lot!