SOLVED

Dynamic Chart not Working on Filter Array

Copper Contributor

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. 

DepartmentNameYearly Salary
FinanceGary Miller60270
SalesJames Willard39627
FinanceRichard Elliot29726
SalesRobert Spear93668
FinanceRoger Mun134000
SalesPaul Garza34808
FinanceRobert Marquez134468
ProcurementNatalie Porter45000
FinanceKim West89500
FinanceStevie Bridge21971
ProcurementAndre Cooper80000
FinanceCrystal Doyle185000
ProcurementRobert Musser50454
FinanceDaniel Garrett140000
ProcurementAnn Withers110000
FinancePaul Hill68357

 

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 :

sales.png

9 Replies

@cryslot 

All works in my case:

image.png

and

image.png

Please check in attached file.

Hi @SergeiBaklan 

Thank you!

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

procurement.png

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

@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

image.png

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

best response confirmed by cryslot (Copper Contributor)

@SergeiBaklan 

 

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. 

 

about excel.png

 

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. 

@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.

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.
Did you ever find a fix? Im on version 2307 build 16626 on a monthly Enterprise channel and this functionality isnt working for me...

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

1 best response

Accepted Solutions
best response confirmed by cryslot (Copper Contributor)