Make an awesome interactive chart displaying aggregated numbers easily in Microsoft 365

Interactive charts can easily be made directly from spill ranges of formulas with dynamic array functions in Microsoft 365 and Excel for the web. The trick is to link the chart to the spill range of a SINGLE formula. Then the number of displayed chart categories will expand and shrink with the spill range. Using two separate spill ranges for the categories and values will not work, because the chart will stay linked to a fixed range.
But very often you will need to aggregate data, perform calculations, and will inevitably have two spill ranges. So in this video you will see two ways to use this chart trick with aggregating functions as well and create interactive charts.
This charting technique with a spill range works in Microsoft 365 and Excel for the web. In old Excel versions you can use dynamic named ranges as chart references for a similar effect. An example of how to use dynamic named ranges in charts is in my older video:
• Declutter a line chart...
Link to my last video mentioned in this one:
• A feature that you abs...
00:00 Introduction
00:53 Preparing the table to use structured references
01:13 Spill range for categories (function UNIQUE)
01:38 Spill range for values (function SUMIF)
02:40 Formula for SINGLE spill range with function CHOOSE (method 1)
05:23 Adding a chart to the single spill range
05:46 Sorting the values
06:32 Controlling the interactive chart with drop-down lists
08:47 Testing the interactive chart
09:26 Making a dynamic chart title
10:34 Using an additional (helper) spill range (method 2)
10:41 Single spill range with the OFFSET function
11:39 Adding a chart to the single spill range
12:18 Testing the interactive chart
12:39 When to use this charting technique and what to use instead
For more content like this, please subscribe to my channel.
#MsExcel #ExcelTips
Screenshots used with permission from Microsoft.

[@amosso3324]

excellent solution, love it.

[@practicalspreadsheetsolutions]

Thank you for your comment!