Build a Dynamic Excel Chart That Changes with a Dropdown

📎 This article includes 1 downloadable practice file ↓

Instead of ten charts — one per region — build one chart and let the reader pick the region from a dropdown. It keeps dashboards clean and takes about five minutes.

The setup

Assume a table with regions in A3:A6 and months Jan–Jun in B2:G2, with sales values in B3:G6.

Step 1: the dropdown

Select cell J2 → Data → Data Validation → List → Source =$A$3:$A$6. Pick “North”.

Step 2: a helper row that follows the dropdown

In B9 enter:

=XLOOKUP($J$2, $A$3:$A$6, B3:B6)

and copy it across to G9. Older Excel: =INDEX(B3:B6, MATCH($J$2, $A$3:$A$6, 0)). Put the months in B8:G8 (=B2 etc.) and a label in A9: =J2.

Step 3: chart the helper range

Select A8:G9 → Insert → Column (or Line). The series name comes from A9, so the chart title updates too: click the title, type = in the formula bar and click J2.

Step 4: polish

• Fix the vertical axis maximum (Format Axis → Bounds) so bars do not jump in size between regions — otherwise small regions look as big as large ones.
• Add a second series with the average of all regions for comparison: =AVERAGE(B3:B6) in row 10.
• Hide the helper rows or move them to a “Calc” sheet.

Variations

• Two dropdowns (region + metric): use the two-criteria lookup.
• A slider instead of a dropdown: Developer → Insert → Scroll Bar, linked to a cell that picks the row number for INDEX.
• Pivot-based: a PivotChart with a Slicer does the same with no formulas.

Try it yourself: step by step

1. Download dynamic-chart.xlsx and open Dashboard.
2. Click the yellow cell J2 — a dropdown arrow appears. Pick a different region and the chart changes.
3. Look at row 9: each cell uses INDEX(…, MATCH($J$2, $A$3:$A$6, 0)) to fetch the chosen region. The chart only reads row 9.
4. Right-click the vertical axis → Format Axis: the maximum is fixed at 350, so small regions do not look as big as large ones.
5. Extend it: add a row 10 with the average of all regions (=AVERAGE(B3:B6)), then right-click the chart → Select Data → Add series.