Dynamic charts with data ranges in Excel - Dashboard Guide

Dashboard Mode - Dynamic charts with data ranges

Dashboard Goal

This dashboard helps you see sales trends over time. You can choose which product's sales to view, and the chart updates automatically. This way, you quickly understand how each product is doing without making a new chart every time.

Sample Data

Month	Product A	Product B	Product C
Jan	100	150	200
Feb	120	130	210
Mar	140	160	190
Apr	130	170	220
May	150	180	230
Jun	160	190	240

Dashboard Components

• Dropdown Selector (Cell G2): A dropdown list to select the product (Product A, Product B, Product C). Use Data Validation with list source: Product A,Product B,Product C.
• Dynamic Named Range for Sales Data: Define a named range SelectedSales with formula:
  =INDEX(B2:D7,0,MATCH(G2,B1:D1,0))
  This picks the column matching the selected product.
• Dynamic Chart: Create a line chart with:
  - X-axis: Months (A2:A7)
  - Y-axis: SelectedSales named range
  The chart updates automatically when you change the product in G2.
• Summary KPI (Cell G4): Shows total sales for selected product:
  =SUM(SelectedSales)

Dashboard Layout

+----------------------+--------------------+
| Product Selector (G2) | Dynamic Chart       |
| [Dropdown]           | (Line chart here)   |
+----------------------+--------------------+
| Total Sales (G4)     |                    |
| [KPI number]         |                    |
+----------------------+--------------------+

Interactivity

When you pick a product from the dropdown in cell G2, the named range SelectedSales changes to that product's sales column. The chart uses this named range for its data, so it updates automatically. The total sales KPI also recalculates using the same range. This keeps the dashboard in sync with your choice.

Self Check

If you change the product selection in cell G2 from "Product A" to "Product C", which components update?

• The line chart updates to show Product C sales over months.
• The total sales KPI in G4 updates to sum Product C sales.
• The dropdown remains the same, allowing further selection.

Key Result

Interactive sales dashboard with a dropdown to select product and a dynamic chart that updates sales data accordingly.