Aug 3, 2026 · 7 min · 6 segments
Wouldn't it be useful to learn how to build interactive Excel charts that respond automatically to user input using dynamic arrays? This expert-led episode introduces the core techniques behind…
So as I mentioned, in example one, the user can select how many months they want to report on, and the chart will automatically update based on an input cell.
So instead of having huge formulas, you can break down the huge formulas into shorter formulas that are much easier to understand.
A lot of the helper cells use dynamic arrays and they were all based on the sequence function.
So it's very common to use the sequence function to actually drive dynamic array calculations.
And again, it provides a flexible way to control how many months are reported on.
The example data had two years worth of sales data, and I used the max function to find out the current date or the last date in the data.
So the max function usually works with numbers, but it also works with dates, because in Excel, dates are numbers.
It stands for end of month, and as well as returning the end of the month, I shared a hack on how you can return the first of the month.
So once we brought together all of the figures we needed, then the HStack function allowed us to join two columns together, and that became the source data for the chart.
So in this case, it was spilling down and across, and spilling is associated with dynamic arrays.
So the HStack function allowed us to join two columns together and that became the source data for the chart.
So as the user changes the number of months, the spill range automatically updates to match that, and the chart automatically updates as well.
Now in the companion video, I also shared a solution to a dates bug in dynamic arrays.
So unfortunately, this has been around since day one of dynamic arrays, but Microsoft haven't fixed it yet.
And the solution, which is really easy, is to put the plus sign in front of the spill range reference.
So as I mentioned, in example one, the user can select how many months they want to report on, and the chart will automatically update based on an input cell.
So instead of having huge formulas, you can break down the huge formulas into shorter formulas that are much easier to understand.
A lot of the helper cells use dynamic arrays and they were all based on the sequence function.
So it's very common to use the sequence function to actually drive dynamic array calculations.
And again, it provides a flexible way to control how many months are reported on.
The example data had two years worth of sales data, and I used the max function to find out the current date or the last date in the data.
So the max function usually works with numbers, but it also works with dates, because in Excel, dates are numbers.
It stands for end of month, and as well as returning the end of the month, I shared a hack on how you can return the first of the month.
So once we brought together all of the figures we needed, then the HStack function allowed us to join two columns together, and that became the source data for the chart.
So in this case, it was spilling down and across, and spilling is associated with dynamic arrays.
So the HStack function allowed us to join two columns together and that became the source data for the chart.
So as the user changes the number of months, the spill range automatically updates to match that, and the chart automatically updates as well.
Now in the companion video, I also shared a solution to a dates bug in dynamic arrays.
So unfortunately, this has been around since day one of dynamic arrays, but Microsoft haven't fixed it yet.
And the solution, which is really easy, is to put the plus sign in front of the spill range reference.
The rest of this transcript — segmented and speaker-labeled, so you land on the exact moment something was said
Search every transcript — by keyword, by phrase, or by meaning, across every show Radar indexes
Trends — what is surging across podcasts, measured against its own baseline
Alerts — when a name you follow appears in a newly indexed episode
No account is needed to search Radar.