Dynamic arrays functionality is available in Preview for users signed up for the Office 365 Insiders Program starting today. We will initially roll out to a subset of Insider users on Windows so that we can gather feedback and monitor feature quality. Over the next few months, we'll be increasing the number of Insider users with access to dynamic arrays and light up support for Excel on Mac, web, and mobile. You’ll know if you have dynamic arrays if you see any of the new functions in your formula autocomplete when you start typing a formula. Thank you - this is a very exciting feature. I have a few initial questions: • Can dynamic arrays be used with RTD-based functions? Currently there are some bugs (including buggy RTD topic disconnect) when RTD is called from legacy CSE array formulae (even if the RTD call is through an indirection).
It would be an immense help if there were RTD support in conjunction with the dynamic arrays, for async, streaming and other scenarios. • Do I understand correctly that any UDF defined in a.xll add-in which returns an XLOPER will automatically work with the dynamic array behaviour? Can I effectively recreate a 'FILTER' or 'SORT' function inside an add-in or do these functions themselves have internal magic too? Is 'SINGLE' special or can I opt into implicit intersection behaviour from inside my array-returning UDF function by calling SINGLE via the C API? • Can dynamic arrays be used inside Tables? Currently array functions cannot be put in Tables. It would be great to have the auto-expanding and shrinking Table behaviour anchored on a synamic array function. • How does the # spill operator impact the reference passed to a function taking an XLOPER reference parameter in the C API?
In this Excel for Mac 2016 update to his popular series, author Dennis Taylor presents numerous formulas and functions in Excel and shows how to use them efficiently. The course begins with tips and keyboard shortcuts to accelerate the way you work with formulas within one or multiple worksheets.
Is there any way to know that a parameter is a dynamic array (as opposite to a fixed, explicit range)? • How is the caller identified for dynamic array functions (via xlfCaller in the C API or Application.Caller in VBA)?
Is anyone seeing a lot of formulas being wrapped with SINGLE() after getting the new functions? I have a number of spreadsheets that return a single row table from Power Query, and then I refer to those tables in other formulas. So =TableName[FieldName] would give me the count of rows in a query, or the total of a value, etc. Usually used for error checking. One of my files this morning had 7 SINGLE() functions in it.
Some simple as above, some deeply nested in those stupidly long uneditable formulas I am prone to do over time. Now those are all showing up as =SINGLE(TableName[FieldName]) Creating a new single row table in a new file, typing =Table2[data field] does not cause it to wrap in SINGLE(), however. If I create the formula in Excel Online or an older version of Excel, =Table1[Test data] becomes =SINGLE(Table1[Test data]) when I open it in the new version.