📎 This article includes 1 downloadable practice file ↓
In this article
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.
Build it
- In H1 put a drop-down: Data Validation › List with source
=$B$1:$E$1(the region headers). - 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)). - Chart Month vs the helper column.
- 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.
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