📎 This article includes 2 downloadable practice files ↓
📘 Charts & Dashboards Intermediate · Lesson 3 of 6
Advertisement
Four regions, four charts? Make one chart that shows whichever region you pick.
Picker: cell H2 > Data Validation > List > =$B$1:$E$1.
Helper column: =INDEX(B2:E13, 0, MATCH(H2, B1:E1, 0)) returns the picked region’s 12 values.
Chart the helper column (not the raw data).
Title: a cell with ="Monthly sales - "&H2; click the chart title, type = in the formula bar and click that cell.
In the finished example, switching the drop-down from West to North changes the chart from ₹398.7 to ₹549.9 lakh and the title to “Monthly sales – North”.
💡 Add a second picker for a comparison region and a second helper series: a “compare any two” chart.
Practice
The practice workbook has the data and the helper formulas behind the chart (with automatic checks). Build the chart yourself, then open the finished examples file to compare. Excel built those charts and we checked their types and series.
← Charts Intermediate Lesson 2: Waterfall Charts – Explain a Change Charts Intermediate Lesson 4: Actual vs Target With Automatic Colours → 📎 Practice files for this article ⬇ Download all 2 files (ZIP · 38 KB)
📗 Practice workbook Monthly sales by region and a picker cell, 5 tasks.
⬇ XLSX · 14 KB 📗 Finished chart examples Combo, waterfall, dynamic, actual-vs-target, sparklines and heat map, built by Excel.
⬇ XLSX · 35 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.
Written by Atul Vij Atul (AJ) builds office-automation and local-first AI tools as Atulya AI, and works with Excel, SAP and Oracle EPM every day. He writes about the shortcuts, macros and automations that actually save time — plus the occasional wacky fact. Videos on YouTube: Lazy Automator AI (@LazyAutomatorAI).
More posts by Atul → ← Previous Charts Intermediate Lesson 2: Waterfall Charts – Explain a ChangeNext → Charts Intermediate Lesson 4: Actual vs Target With Automatic Colours✨ Ask AI about this article Stuck on a step? Ask a question and the AI answers using this article.
Your question about this article Model Auto — best model for the task GPT-OSS 120B — OpenAI · strongest reasoning · counts as 2 DeepSeek R1 — DeepSeek · thinks step by step (slow) · counts as 2 Llama 3.3 70B — Meta · fast, all-round Qwen 2.5 Coder 32B — Alibaba · best for code Gemma 4 26B — Google · clear explanations Mistral Small 3.1 — Mistral · quick GPT-OSS 20B — OpenAI · balanced Llama 4 Scout — Meta · newest Llama Gemma Sea Lion V4 27b — Aisingapore Glm 4.7 Flash — Zai-org Llama 3.1 8b — Meta Llama 3.2 3b — Meta Nemotron 3 120b A12b — Nvidia · counts as 2 Qwen3 30b A3b — Qwen Qwen3.8 27b — Qwen Qwq 32b — Qwen · counts as 2 Free · AI can be wrong Ask ✨
