8.14 Drop-down Driven Dynamic Chart (INDEX / XLOOKUP)
The user picks a city from a drop-down; one chart redraws for that city.
Steps in Excel
- In
Dash!B1create a drop-down (Data Validation › List) with the city namesPune,Nashik,Nagpur. -
Build a helper row: months in
Dash!B3:G3(Jul…Dec). InDash!B4:=INDEX(ChartData!$B$2:$D$7, MATCH(B$3, ChartData!$A$2:$A$7, 0), MATCH($B$1, ChartData!$B$1:$D$1, 0))copy across to G4. (Microsoft 365 alternative in B4:
=XLOOKUP(B$3, ChartData!$A$2:$A$7, XLOOKUP($B$1, ChartData!$B$1:$D$1, ChartData!$B$2:$D$7)).) 3. Chart title cell:=B1&" – monthly sales (₹ lakh)". 4. Select B3:G4 › Insert › Charts › Clustered Column. 5. Link the chart title to the title cell: click the title › in the formula bar type=› click the title cell › Enter. 6. Change the drop-down to Nagpur – the chart and its title change.
Ravindra Bagale's Tip
In a dynamic chart, many students build the chart directly on the big table and then try to change the "series" – it becomes a mess. The rule: always build the chart on a small helper range, and fill the helper range with formulas (INDEX/XLOOKUP) based on the drop-down. Link the chart title to a cell too, otherwise the city changes but the title stays "Pune".
Ravindra Bagale's Tip – मराठी
Dynamic chart मध्ये बरेच students chart थेट मोठ्या table वर बनवतात आणि मग "series" बदलायला जातात – गोंधळ होतो. नियम: chart नेहमी छोट्या helper range वर बनवा, आणि helper range formulas ने (INDEX/XLOOKUP) drop-down प्रमाणे भरा. Chart title पण cell ला link करा, नाहीतर city बदलते पण title "Pune" च राहतो.
Ravindra Bagale's Tip – हिंदी
Dynamic chart में बहुत से students chart सीधे बड़ी table पर बनाते हैं और फिर "series" बदलने लगते हैं – गड़बड़ हो जाती है. नियम: chart हमेशा छोटी helper range पर बनाओ, और helper range को formulas (INDEX/XLOOKUP) से drop-down के हिसाब से भरो. Chart title को भी cell से link करो, वरना city बदल जाती है पर title "Pune" ही रहता है.
Practice task
Build a drop-down driven chart with a second drop-down for the measure (Sales or Orders). Link the chart title so it reads, for example, "Nagpur – Orders".