Scatter plots in Excel - Dashboard Guide

Dashboard Mode - Scatter plots

Goal

Understand the relationship between advertising spend and sales using a scatter plot.

Sample Data

Month	Advertising Spend ($)	Sales ($)
Jan	200	1500
Feb	300	1800
Mar	250	1600
Apr	400	2100
May	350	2000
Jun	500	2500

Dashboard Components

• KPI Card - Average Advertising Spend: Displays average spend.
  Formula: =AVERAGE(B2:B7)
  Result: 333.33
• KPI Card - Average Sales: Displays average sales.
  Formula: =AVERAGE(C2:C7)
  Result: 1916.67
• Scatter Plot: Plots Advertising Spend (X-axis) vs Sales (Y-axis).
  Data range: B2:B7 for X values, C2:C7 for Y values.
  Shows how sales increase with advertising spend.

Dashboard Layout

+----------------------+----------------------+
| Average Advertising   | Average Sales        |
| Spend: $333.33       | $1916.67             |
+----------------------+----------------------+
|                      Scatter Plot               |
|          (Advertising Spend vs Sales)           |
|                                                |
+------------------------------------------------+

Interactivity

Add a filter for Month to select specific months. When a month is selected, the KPI cards and scatter plot update to show data only for those months.

Self Check

If you filter the data to show only months with Advertising Spend above $300, which components update and what changes do you see?

• KPI Cards update to show averages for filtered months only.
• Scatter plot updates to show points only for filtered months.

Key Result

Scatter plot dashboard showing relationship between advertising spend and sales with average KPIs.