Skip to content
Tutorials Center
My learning

Topics

Lesson 55 of 93 ยท Charts and Visualization

Dynamic and Interactive Charts

Make Excel charts that update themselves: dynamic titles, Tables and named ranges, drop-down driven charts, rolling last-N charts, multi-sheet sources, overlapping bars and percent stacks.

A static chart is finished the day you build it. A dynamic chart follows the data: new rows appear in it, a drop-down switches the product, the title reads "Sales, March" without anyone typing it. The tricks are all the same idea: the chart points at cells or names that calculate what to show, and you change the inputs.

Excel cannot run on this site, so formulas and menus are real Excel but read-only. The TryIts use JavaScript stand-ins for the calculations that feed the chart (a rolling window, a drop-down lookup, percent-of-total). They show the numbers a chart would plot; they do not draw one.

Chart from a Table: the easiest dynamic chart

Convert the source to an Excel Table with Ctrl+T (Excel Tables) and build the chart from it. When you type a row directly under the table, the Table grows and the chart grows with it. No formulas needed. If the chart is built from a plain range, you can still click Table Design and resize it, but starting from a Table is the cleanest approach.

Dynamic chart titles

A chart title can display a cell's value. Build the text in a cell and link the title to it:

TEXT
="Sales for "&B1&": "&TEXT(SUM(C2:C13),"#,##0")

Then select the chart title, click in the formula bar, type =, click the cell (for example Sheet1!$E$1), and press Enter. The title now changes whenever B1 or the data changes. Axis titles and text boxes can link the same way. Use TEXT (Text Formatting and Conversion) to format numbers in the sentence.

Let the reader pick the product, region or month from a list, and chart only that selection.

  1. Put a data validation list (Data Validation) in H1 with the product names.
  2. Build a helper range that looks up the selected product's numbers, for example in H3:H14:
TEXT
=INDEX($B$2:$D$13, ROW()-2, MATCH($H$1, $B$1:$D$1, 0))

or, in Microsoft 365, with XLOOKUP/FILTER (XLOOKUP, FILTER and SORT). 3. Chart the helper range (months in G3:G14, values in H3:H14).

Changing the drop-down now redraws the chart. For several series at once, make the helper table as wide as the number of series, and pass the whole block to the chart.

JS A drop-down choosing which column the chart plots

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Dynamic named ranges

Named ranges (Ranges and Names) can grow with the data using OFFSET or INDEX (OFFSET and INDIRECT). Create two names in Formulas > Name Manager:

TEXT
ChartMonths  =OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A$2:$A$200), 1)
ChartValues  =OFFSET(Sheet1!$B$2, 0, 0, COUNTA(Sheet1!$A$2:$A$200), 1)

A non-volatile alternative, which is better for big workbooks:

TEXT
ChartValues  =Sheet1!$B$2:INDEX(Sheet1!$B$2:$B$200, COUNTA(Sheet1!$A$2:$A$200))

Then Select Data > Edit series and set the Series values to ='Book1.xlsx'!ChartValues (use the workbook name, not the sheet, or Sheet1!ChartValues for a sheet-level name). Typing a new row extends the names, and the chart follows. A Table does this for you, so use names when the data cannot be in a Table.

Rolling and last-N charts

A rolling chart always shows the latest N points (the last 12 months, the last 30 days). Make a cell H1 with N, and define the start of the window by counting back:

TEXT
RollMonths =OFFSET(Sheet1!$A$1, COUNTA(Sheet1!$A:$A) - $H$1, 0, $H$1, 1)
RollValues =OFFSET(Sheet1!$B$1, COUNTA(Sheet1!$A:$A) - $H$1, 0, $H$1, 1)

In Microsoft 365, =TAKE(A2:B500, -H1) (after filtering blanks) returns the same window as one spilled range that you can chart directly (Dynamic Array Basics). Change H1 to 6 or 24 and the chart rescales.

JS A rolling window of the last N points

Press Run (or Ctrl + Enter) to see the console output here.

No output.

A dynamic pie

A pie of the top five items changes when the data changes if the helper range ranks them: =LARGE($B$2:$B$50, ROWS($A$1:A1)) for the values and =INDEX($A$2:$A$50, MATCH(D2, $B$2:$B$50, 0)) for the labels. Add one extra slice, "All others", as =SUM(B2:B50) - SUM(D2:D6). Ties make MATCH return the same label twice, so break ties first (for example, add ROW()/1000000 to each value in a helper column).

Percentages in a stacked column, and overlapping bars

  • 100% stacked column: choose "100% Stacked Column" so every column totals 100% and the labels show shares. If you also want the real values, show data labels with Value From Cells pointing at a percent column, or compute the percentage yourself: =B2/SUM($B2:$D2).
  • Stacked column with totals on top: add a Total series as a line with no line and labels Above, so the number floats above each stack.
  • Overlapping bars: set Series Overlap to 100% and make the target wider than the actual (put the actual on the secondary axis) for a progress look. Keep both axes on the same scale, or the bars will lie about the comparison.
JS Percent-of-total columns for a 100% stack

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Charts from several sheets

A chart can combine ranges from different sheets. Create it from the first sheet, then Select Data > Add and, for each new series, choose the Series name and Series values by clicking the cells on the other sheet. Keep every series the same length and aligned to the same categories, or points will not line up. If the sheets share a layout (one per month), consider consolidating them first with 3D references or Power Query (Power Query Basics) so one table feeds one chart. Mixed units still need a secondary axis (Formatting and Improving Charts).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How do you make a chart title display a cell's text?

    A linked title follows the cell. Build the sentence with & and TEXT in that cell, then link the title to it.
  2. 2. What is the easiest way to make a chart include new rows automatically?

    A Table expands when you add a row beneath it, and charts built on the Table expand with it.
  3. 3. In a drop-down driven chart, what does the chart actually plot?

    The drop-down only chooses an item. Formulas such as INDEX/MATCH or XLOOKUP pull that item's numbers into a helper range, and the chart plots the helper range.
  4. 4. What does =TAKE(A2:B500, -H1) return in Microsoft 365?

    A negative row count takes rows from the end, which is what a rolling last-N chart needs (after blank cells are excluded).

Summary

  • Tables make charts grow with the data; link chart titles to cells built with & and TEXT.
  • A drop-down plus a lookup helper range gives an interactive chart.
  • Dynamic names (OFFSET or INDEX) cover non-Table data; TAKE or an OFFSET window gives a rolling last-N chart.
  • Rank with LARGE/INDEX/MATCH for a dynamic top-N pie, and compute percent-of-total columns for stacked shares.
  • Multi-sheet charts need aligned series; consolidate first when you can.

Next: Scatter, Bubble, Frequency Polygon, Ogive and Statistical Plots.