Excel Dynamic Charts Based On Dynamic Arrays - 2515

Brand new in Excel Insiders Beta:
A chart can be based on an entire dynamic array in Excel.
In this first iteration, the chart has to point to the entire array. If you have columns in your array that you don't want to chart, you should use =CHOOSECOLS to get a smaller subset of the array for charting.

Transcript of the video:

Oh, it's great. It's here.
Dynamic charts based on dynamic arrays.
This is brand new in Insiders beta build 16.015617.10000 or later. All right.
So what I have here is I have a report that's actually built from dynamic arrays, but I only want to chart customer and revenue and only the Top N.
So here I'm using the FILTER function, sending that into the SORT function.
For right now, the chart has to be based on the entire spilled array.
Check this out.
So right here, when I change this input cell from four to six, the filter then returns more records and the chart automatically returns those records.
That's a Textbox there using a formula to update based on the six. This is a great improvement.
There's some oddities, like when I look at the SERIES function, they're not using the Hash nomenclature.
They're just pointing to the whole series, just like a table would do, with a chart built on a table, they don't reference the table, they reference the whole range of the table and that's the same thing that's going to happen here.
So I select all of this data, go to insert, choose my chart type.
And they point to the range of that table.
But as that range changes, so we go from five to 10 and then come back here and look at the SERIES function, you see they automatically update the series.
So it's the same, very similar logic to Ctrl+T tables. What a great improvement.
Watch for that again in Insiders beta, if you're not in Microsoft 365 Insiders, it's easy to join.
Just Google how to join office insiders and make sure to choose the beta option as opposed to monthly or targeted, beta gets you the new stuff faster.
If you like these videos, please down below like, subscribe, and ring the bell.
Feel free to post any questions or comments down in the comments below.
Well, Hey, I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.

 

[csicilia]

I like this feature, it is working well with only one data serie. If I add a second serie coming from another dynamic array of the same length the auto-adjustment stop working.
Any idea about how to have more than one data serie with this feature?

 

[smozgur]

I like this feature, it is working well with only one data serie. If I add a second serie coming from another dynamic array of the same length the auto-adjustment stop working.
Any idea about how to have more than one data serie with this feature?

Welcome to the MrExcel Message Board!

I can see that Bill used K and L columns in the FILTER formula in cell O4. If we also include the M column, then we can get 3 columns dynamic array result and easily create a chart with two series by using that range.

Basically, the cell O4 formula could be changed to the following, and the chart source can be changed accordingly.

=SORT(FILTER($K$2:$M$28,$M$2:$M$28<=P2),2,-1)

(The screenshots below use different cell addresses & values, but you get the idea)
For Top N = 3:

For Top N = 5

 

[csicilia]

Thank you for your reply, from your message I got the idea. It only auto-size one array. In my case I solve it just creating a new dynamic array with all the series =A1#:A4#

 

[chrisw76]

In theory this feature was released to the Monthly Enterprise Channel as of 2210 32-bit (Release notes for Monthly Enterprise Channel releases - Office release notes), but for the life of me I can't get it to work. Creating a chart from a spilled array works fine, but it just doesn't update when the number of rows / data points changes. Doesn't anyone else have this problem or is it just me?

Cheers, Chris W.

 

[jdellasala]

In theory this feature was released to the Monthly Enterprise Channel as of 2210 32-bit (Release notes for Monthly Enterprise Channel releases - Office release notes), but for the life of me I can't get it to work. Creating a chart from a spilled array works fine, but it just doesn't update when the number of rows / data points changes. Doesn't anyone else have this problem or is it just me?

Cheers, Chris W.

There are two releases for that channel as noted here, but they both should have gotten them around Jan 10. When I was in a corporate environment, updates were controlled by IT regardless of the channel. I left in June 2020 and we still didn't have dynamic arrays. In a Windows/Azure environment that could be controlled by Group Policy. In my case they were afraid of the new calc engine!
I'm on the personal 365 Insider Edition and it works there. Hang tight, you'll get it eventually!

 

[chrisw76]

There are two releases for that channel as noted here, but they both should have gotten them around Jan 10. When I was in a corporate environment, updates were controlled by IT regardless of the channel. I left in June 2020 and we still didn't have dynamic arrays. In a Windows/Azure environment that could be controlled by Group Policy. In my case they were afraid of the new calc engine!
I'm on the personal 365 Insider Edition and it works there. Hang tight, you'll get it eventually!

Hello!

As it turns out the release notes were wrong so even though we have deployed 2210 in the MEC, the feature is not there! I got a response from a MS employee confirming on the community here: How do I make Dynamic Array Integration with Charts in MEC 2210 work?

I guess we'll just have to wait for the feature to properly arrive in a few months.

Cheers, Chris W.