Dynamic Chart not Working on Filter Array

[cryslot]

Currently, I am working on creating a dynamic chart in Excel using the filter array function but my chart wouldn't adjust accordingly. 

 Below is a sample table I am using based on Leila's youtube channel. 

Department	Name	Yearly Salary
Finance	Gary Miller	60270
Sales	James Willard	39627
Finance	Richard Elliot	29726
Sales	Robert Spear	93668
Finance	Roger Mun	134000
Sales	Paul Garza	34808
Finance	Robert Marquez	134468
Procurement	Natalie Porter	45000
Finance	Kim West	89500
Finance	Stevie Bridge	21971
Procurement	Andre Cooper	80000
Finance	Crystal Doyle	185000
Procurement	Robert Musser	50454
Finance	Daniel Garrett	140000
Procurement	Ann Withers	110000
Finance	Paul Hill	68357

Using the Filter formula, I created another set of data that is dependent on a drop down list based on the Department. There are 3 departments from this example, with different number of people from each department. With the data from the filter function, I created a graph and supposedly, when I choose the department from the drop down list, my table would also adjust so there are no more white space or empty spaces in the chart.

But it doesn't seem to work. I don't know what I am doing wrong. 

Sample broken chart when I choose Sales :

[#2]

@cryslot 

All works in my case:

and

Please check in attached file.

[#3]

Hi @Sergei Baklan 

Thank you!

I used your file but it doesn't work on mine.

I am using Office 365 and am currently connected to my OneDrive. I wonder why it wouldn't work on my Excel.

[#4]

@cryslot 

I guess it depends on Channel on which you are. Dynamic resizing of charts was introduced few months ago, not sure on which channels it works now. I'm currently on

Alternatively you may use named dynamic ranges like here dynamic graph from filter function - Microsoft Community Hub

[#5 · Accepted solution]

@cryslot 

Okay, it's supported on

https://techcommunity.microsoft.com/t5/excel-blog/what-s-new-in-excel-april-2023/ba-p/3791667

[#6]

@Sergei Baklan 

Oh. So this explains it. My version is still at 2208. I will ask our IT dept about this. I tried to install latest version but it says I am up to date. 

Thank you so much! This was driving me crazy for 2 days now, unable to get some sleep. Only for this to be the cause. 

[#7]

@cryslot 

Yes, Semi-Annual Enterprise is far behind other channels. It is updated with new functionality twice per year, in Jan-Feb and Jun-Jul. You may check details here Release notes for Semi-Annual Enterprise Channel releases - Office release notes | Microsoft Learn. Thus on this channel is only to wait, some chances dynamic array charts will be added in couple of months. But not necessary, it could happens next year.

Of course, another option is to ask your IT update you on Current or Monthly Enterprise channel. Here is all depends on company policies. Main reasons could be loss of compatibility with other users and adding bit more headache to IT with support of users on different channels.

[#8]

This is a weird one. I have still not got this feature even though I am on monthly enterprise. MS say it's part of 2210 and here I am on 2306 with no sign of this feature yet. I didn't have it, even back in 2210 last year. I get features being deprecated on Beta which I have at home but the lack of it is super annoying given the number of dynamic arrays that are getting built.

[#9]

Did you ever find a fix? Im on version 2307 build 16626 on a monthly Enterprise channel and this functionality isnt working for me...

[#10]

@MattBeck7 nope. Im now on Version 2309 Build 16.0.16827.20278 and still zero luck