Ravindra BagaleCourses & study guides

8. Charts

8.14 Drop-down Driven Dynamic Chart (INDEX / XLOOKUP)

By Last updated:

The user picks a city from a drop-down; one chart redraws for that city.

Steps in Excel

  1. In Dash!B1 create a drop-down (Data Validation › List) with the city names Pune,Nashik,Nagpur.

  2. Build a helper row: months in Dash!B3:G3 (Jul…Dec). In Dash!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

Dynamic chart madhe khup students chart la thet mothya table var banvtat aani mag "series" badalayla jaatat – gondhal hoto. Niyam: chart nehmi chhotya helper range var banva, aani helper range formulas ne (INDEX/XLOOKUP) drop-down pramane bhara. Chart title pan cell la link kara, nahitar city badalte pan title "Pune" ch rahato.

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".