Guides

Dynamic Charts in Excel: Grow with a Table, Change with a Drop-down

By Ravindra Bagale, Excel & Data Analytics trainer · LinkedIn · Published: 6 October 2026 · Last updated: 6 October 2026

Every month Ravina adds a new row to her sales list and then edits the chart range by hand. Her manager also wants the same chart for each of six cities. She builds two things once: a chart on an Excel Table, so new months appear by themselves, and a chart that reads a drop-down, so one chart shows any city.

A dynamic chart changes without you editing its range. The two easy ways: build the chart on an Excel Table (it grows with new rows), or point the chart at a small helper range whose formulas read a drop-down cell.

Download the practice file (dynamic-charts.xlsx)

Excel file, 11 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.

=INDEX(B2:G2,MATCH($M$1,$B$1:$G$1,0))

Way 1: a chart that grows with the data

1. Click inside the month list and press Ctrl + T, then OK.
2. With a cell in the table selected, click Insert > Column Chart.
3. Type the next month (Oct) and its revenue in the row under the table.
4. The table grows by one row and the chart shows Oct at once.

This works because the chart series points at the table's columns. If you build the chart on a normal range, you must drag the blue range border every month.

Way 2: a chart that switches with a drop-down

The Chart sheet has six cities in columns B to G and six months in rows 2 to 7.

1. In L1 type Pick a city: and click M1.
2. Data > Data Validation > Allow: List, Source =$B$1:$G$1, then OK. Pick Nashik.
3. In I1 type Month and in J1 type =M1 (the series name follows the drop-down).
4. In I2 type =A2. In J2 type =INDEX(B2:G2,MATCH($M$1,$B$1:$G$1,0)). Copy I2:J2 down to row 7.
5. Select I1:J7 and insert a line chart.
6. Change M1 to Pune or Nagpur. The helper column and the chart update together.

MATCH($M$1,$B$1:$G$1,0) finds Nashik in position 3. INDEX(B2:G2, 3) returns the third number in the Apr row: 70,000. The six helper values for Nashik add up to ₹5,96,000.

Tips for dynamic charts

• Keep the helper range next to the data and give it a heading that says it feeds the chart.
• With Microsoft 365 you can use =XLOOKUP($M$1,$B$1:$G$1,B2:G2) instead of INDEX and MATCH.
• To show only the last 6 months of a long list, use a Table plus a filter, or the OFFSET named-range method from the course lesson.
• Hide helper columns only after the chart works; a chart ignores hidden cells unless you tick Select Data > Hidden and Empty Cells > Show data in hidden rows and columns.