Charts Lesson 5: Dynamic Charts With Drop-Downs

Charts Lesson 5: Dynamic Charts With Drop-Downs featured illustration

πŸ“Ž This article includes 1 downloadable practice file ↓

⏱ 1 min readUpdated 12 October 2026

πŸ“˜ Charts & Dashboards Course Β· Lesson 5 of 6

In this article
  1. Build it
  2. Other dynamic tricks
  3. Where people go wrong
  4. Practice
  5. Continue learning

Instead of four charts, one per region, make one chart and a drop-down to choose the region. Dashboards stay small and readers explore for themselves.

Advertisement

Build it

  1. In H1 put a drop-down: Data Validation β€Ί List with source =$B$1:$E$1 (the region headers).
  2. Helper column I2:I13: =INDEX($B2:$E2, MATCH($H$1, $B$1:$E$1, 0)) (or =XLOOKUP($H$1,$B$1:$E$1,B2:E2)).
  3. Chart Month vs the helper column.
  4. Dynamic title: click the chart title, type = in the formula bar and click a cell containing =$H$1&" sales by month (Rs lakh)".

Pick “South” and the chart and title update.

πŸ’‘ Add the Target as a second series to the same chart; it doesn’t depend on the drop-down, so it stays as a constant reference line.

Other dynamic tricks

  • Excel 365: =FILTER() or =TAKE() to chart “last N months”.
  • Slicers on a table or pivot chart give clickable buttons instead of a drop-down.

Where people go wrong

Problem Fix
Chart doesn’t change It’s built on the original data, not the helper
Title static Link it to a cell with =

More: dynamic chart with a drop-down.

Advertisement

Practice

Build the region drop-down chart on the Monthly sheet with a linked title.

πŸ“Ž Practice files for this article

  • πŸ“—
    Charts practice dataMonthly regional sales, target, online share and product data.
    ⬇ XLSX Β· 7 KB

Free to use for learning. Files with macros (.bas) are plain text β€” import them with Alt+F11 β†’ File β†’ Import File, and always test on a copy.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free Β· AI can be wrong

Leave a Comment