Build an Interactive Chart in Excel With a Drop-Down

An interactive chart in Excel is a chart that changes when someone picks an option, such as a region from a drop-down list. You don't need macros: a drop-down chooses the item, a helper row of lookup formulas fetches its numbers, and the chart is built from that helper row. This guide builds one in five steps with XLOOKUP, shows the INDEX MATCH version for older Excel, and adds a title that updates by itself. It's useful for reports, dashboards and the "make this chart easier to read" tasks in job tests.

How an interactive chart works#

The trick is that the chart never reads the main table. It reads a small helper row: a row of formulas that copy the numbers for whatever is selected. Change the selection and the helper row changes, so the chart redraws.

The example uses monthly sales by region in A1:D5:

Row 7 holds the drop-down, and row 8 is the helper row the chart uses. Four lines on one chart would be cluttered; one line at a time, with a choice, is easy to read.

Step 1: Add a drop-down list to choose the region#

A drop-down list stops people typing a region that doesn't exist, which would break the lookup.

1. Click B7.
2. Go to Data > Data Validation.
3. Under Allow, choose List.
4. In Source, select $A$2:$A$5 (the region names).
5. Click OK and pick East from the new arrow.

Give the cell a fill colour so people know it's the one to change. The guide to creating a drop-down list with Data Validation covers error messages and lists that grow.

Step 2: Build the helper row with XLOOKUP#

The helper row looks up the selected region and returns its numbers, one month per cell. In B8, type:

=XLOOKUP($B$7,$A$2:$A$5,B2:B5)

Then fill it right to D8 (Windows: Ctrl+R; Mac: ⌘R). Here's what each part does:

• $B$7 is the selected region. The $ signs keep it fixed as you fill right.
• $A$2:$A$5 is where to find it, also fixed.
• B2:B5 is the month column to return from. It has no $ signs, so it becomes C2:C5 and D2:D5 as you fill.

With East selected, row 8 shows 143, 128, 160. Pick North and it changes to 120, 135, 150.

In Microsoft 365 you can also fill the whole row with one formula, =XLOOKUP(B7,A2:A5,B2:D5), which spills across three cells. The XLOOKUP function reference lists the optional arguments. XLOOKUP needs Microsoft 365 or Excel 2021 or later.

Step 2 for older Excel: use INDEX and MATCH#

If the file must work in Excel 2019 or earlier, use INDEX and MATCH in B8 instead:

=INDEX(B2:B5,MATCH($B$7,$A$2:$A$5,0))

MATCH finds the position of East in the region list (3), and INDEX returns the third value of the month column (143). Fill it right as before. The INDEX function reference and the MATCH function reference explain both parts; the 0 in MATCH means exact match.

Step 3: Create the chart from the helper row#

Now build the chart from the month headers and the helper row only.

1. Select A1:D1 (the header row).
2. Hold Ctrl (Mac: ⌘) and select A8:D8 (the helper row).
3. Go to Insert and choose a Line or Clustered Column chart.

The chart shows three points for the selected region. Pick another region in B7 and watch it redraw. If you're new to charts, Excel chart basics explains how to pick a chart type and format the axes.

Tip: hide row 8 or move the helper row to another sheet once it works. The chart keeps reading it. To keep a hidden row in the chart, right-click the chart, choose Select Data > Hidden and Empty Cells, and tick Show data in hidden rows and columns.

Step 4: Make the chart title change too#

A fixed title such as "Sales" doesn't say which region is showing. Link the title to a cell instead.

1. In an empty cell, say F7, type ="Monthly sales: "&B7. It shows Monthly sales: East.
2. Click the chart title once to select it.
3. Click in the formula bar, type =, click F7 and press Enter.

Now the title changes with the drop-down. The same trick works for axis titles and text boxes.

Step 5: Add a second choice#

One drop-down picks a row. A second can pick a column, so people choose both the region and the measure. That needs a two-way lookup, which finds a row and a column at the same time:

=INDEX($B$2:$D$5,MATCH($B$7,$A$2:$A$5,0),MATCH(B$1,$B$1:$D$1,0))

This version finds the row from the region in B7 and the column from each month header, so it gives the same numbers as Step 2, but the pattern extends to any grid. The guide to the two-way lookup in Excel builds it step by step. If the second list should depend on the first, such as products within a category, see dependent drop-down lists.

Other ways to make a chart interactive#

A drop-down with a helper row is the most flexible method, but not the only one.

• FILTER for several matching rows. In Microsoft 365, =FILTER(A2:D5,B2:B5>=100) returns only the regions with January sales of 100 or more (North and East). A chart may not resize to follow a spill range in every version, so if bars go missing or blanks appear, re-select the chart's data.
• PivotChart with slicers. A PivotChart plus slicers gives clickable filter buttons, which is handy when the data has many categories. See how slicers filter pivot tables.
• Dashboards. Several interactive charts driven by the same drop-down make a simple dashboard. The sales dashboard guide puts the pieces together.

Practise it#

The chart is a few clicks; the lookup formulas behind it are the skill. Practise XLOOKUP in a live spreadsheet, then practise INDEX and MATCH for files that must work in older Excel. To return several matching rows at once, try the FILTER function lesson. Each lesson checks your formula instantly.