New dynamic array functions not available in latest Excel version

[Tieme Woldman]

I have Excel Version 1907 (Build 11901.20176) and according to the list of new features

 this version should include the new dynamic array functions (FILTER, SORT, SORTBY, UNIQUE, SEQUENCE and RANDARRAY). But the new function aren't available in my Excel.

Any ideas on this?

Thanks in advance.

Tieme

[#2 · Accepted solution]

@Tieme Woldman 

But according to this the new functions are not part of Build 11901.20176.

[#3]

@Detlef Lewin Thanks, that explains it. 

[#4]

@Tieme Woldman 

With help from MS technical support I was able to solve this.

I wanted to use Excel’s dynamic array functions (e.g. Filter(), RandArray()).

I am an Office 365 Home Subscriber.

I was using version 1907 Build 11901.20176.

Per MS support suggestion, I joined Insider and subscribed to the insider newsletter.

It took about 10 minutes but eventually, new updates became available to Excel. (File->Account->Office Updates). 

After downloading the updates, I closed and restarted Excel.

The functions are now available to me.

[#5]

@Tieme Woldman 

Enjoy the new functionality.  As good as they are, the new functions are only part of the story.  Instead of array formulae being the exception as in normal spreadsheet development, it is now rare that one needs to use anything but array formulas.  One's whole mindset can change and copying single cell formulas becomes a thing of the past!  

[#6]

@Peter Bartholomew @Grosner @Detlef Lewin 

I installed the latest insider update (I'm om Office Pro Plus and had to update through Office deployment) and now the new (stunning) functionality is available.

Thanks for your support.

[#7]

Today I subscribed to Office 365 home edition from my Windows 10 Pro notebook. I too do not have access to dynamic arrays at this late date. I have Excel 365 64 bit Office Insider Edition (Version 2004 Build 12730.20150 from April 15, 2020). I do not have the SORT function, or other dynamic array functions. I do have access to more modern features such as XLOOKUP.

Prior to signing up to the Office Insider Edition, I did not have the dynamic array functionality. I saw a post that said by signing up their problem was resolved. This did not work for me.

I am from Canada. Has this feature been fully deployed worldwide and if so does anyone have suggestions?

Bob

[#8]

I also have the problem like @Bob_Bachman . I was using the new DA formulas - following an update that went wrong a re-install was necessary of Microsoft 365. It's working now, but anything with FILTER etc is not. The version info is 2004 (Build 12730.20250 Click-to-Run).

Now here is the curious bit: the laptop I use has exactly the same version as above and all of the DA functions work without a problem!

The reinstall on my PC was after all traces of 365 were removed including registry elements. 

Any clues as to what's going on would be great. The region can't have anything to do with it as both machines are next to each other.

Frustrating not to have these functions available - particularly as I already had them. they make life so much easier!

[#9]

@SpreadsheetRon

The problem magically went away for me within one week of my post after a new Office Update came in. I see that your build looks more up to date than mine, so I am totally confused.

[#10]

@Bob_Bachman  Sounds like I need to wait for the next update. Only a few weeks...!

[#11]

@SpreadsheetRon 

Version and build number practically don't matter - code for dynamic arrays is in the builds for a long while and functionality is switched on gradually, mainly depends on channel. Last wave is expected for semi-annual channel in July or so.

Sometimes it's not necessary to upgrade the app to receive the new functionality, it's enough to be online for some while. And perhaps restart the app.

If you don't have dynamic arrays are on monthly or insiders channel, that could be something wrong with licensing engine, or you could be on Win7 for which O365 doesn't support new functionality due to end of Win7 support, or that could be something wrong on MSFT servers, or like.

Send frowns.

[#12]

@Sergei Baklan 

Interesting. "Something could be wrong with the licensing engine..." seems highly likely. How can this be fixed? Send frowns - I guess this is will only work if enough of us do it!

The other point: Win7 (no, on win10). Restart app - I'll keep trying!

[#13]

Well, well, well... kept trying and suddenly things are now working. Full suite of DA formulas without any more updates or downloads. Spot on @Sergei Baklan, Thanks!

[#14]

@SpreadsheetRon 

Great to know it is sorted out now. Yes, adding of new functionality is not necessary requires the update, code is in the build already. Excel restart could help since with that license engine checks your credentials and in which pool you are in cloud, and switches new functionality ON if you appear in records. But in general restart is also not required, it's enough to be online long enough.

I very simplified in above, only to illustrate an idea.

[#15]

@Sergei Baklan Hi Sergey, I see you helped some people here to understand why new array functions are not working. Maybe you can help me too? As I can see I am a Microsoft Office 365 ProPlus user (on Windows 10) with Excel Version 1908 (Build 11929.20966 Click and go) Semiannual Enterprise channel (one side note: I am located in Germany) - therefore I was expecting to have array functions on my Excel. Would you have any tips for me? I would highly appreciate your help! Thank you in advance. 

[#16]

@Anastasia_Sentyurova 

Hi Anastasia,

Semi-Annual Enterprise exists in two versions: 1908 and 2002

Update history for Microsoft 365 Apps (listed by date) - Office release notes | Microsoft Docs

Dynamic arrays were introduced with bi-annual feature update in July previous year for version 2002 with build Version 2002 (Build 12527.20880) . This part of announcement looks like

you may check here Release notes for Semi-Annual Enterprise Channel releases in 2020 - Office release notes | Microsoft...

I'm surprised DA is not fully in Production, but based on above document version 1908 doesn't have it. You may ask your IT to update on 2002 or wait for another feature update, for semi-annual channel it shall be on Jan-Feb this year. Not sure this functionality is planned or not, many chances it shall be deployed on 1908.

I don't think availability depends on locale, functionality shall be deployed for all local versions, the only not at once for all of them.

[#17]

@Tieme Woldman @Anastasia_Sentyurova @Sergei Baklan @Bob_Bachman @SpreadsheetRon 
My Excel 365 doesn't support Dynamic Array formulas like GROUPBY and PIVOTBY. Can anyone let me know to resolve the issue. Thanks

[#18]

@adnanmosingmailcom 

Please read the 1st squared paragraph of article New aggregation functions: GROUPBY and PIVOTBY

[#19]

@adnanmosingmailcom 

First please check on which channel you are.

If you don't have these functions on your channel that's only to wait and install updates regularly and/or shift the channel.

If that's Beta channel and you still don't have these functions that's only to wait when 100% of Beta users will be covered. My impression it'd done already, but I'm not sure.

If you are on semi-annual channel that's to wait another couple of years till that channel will be updates with such functionality.