Excel Dynamic Arrays

Previews of dynamic arrays in Excel have been available for some time through the “Insider” programme.  They are now being published to general subscribers through Office 365 monthly updates:

Further information is given at:

Easier array formulas
Dynamic array formulas vs. legacy CSE array formulas

Most of my spreadsheets make extensive use of user defined functions (UDFs) that often return arrays of data, that up till now needed to be entered by selecting the output range and pressing Ctrl-Shift-Enter.  These “legacy” CSE functions continue to work as they did before, but if you want to change to using the new dynamic arrays the procedure is:

Select the entire output range of the CSE array.  Note that it displays in the edit line surrounded by {} :

Press F2 to edit, then convert the function to text by entering ‘ before the = sign, then enter with Ctrl-Shift-Enter:

Now delete the text from all the cells other than the top left:

Finally press F2, delete the ‘ to return the cell to an active function, and press enter (not Ctrl-Shift-Enter).  The entire array will now display:

Note that the function only occupies the top left cell.  You can enter data in any other cell of the array output range, but if you do the function will display as SPILL!:

As far as I know, there is no way to return part of an array using the new functionality, other than the top left cell, that can be returned with the Single() function.  Fortunately the old Ctrl-Shift-Enter method still works.

This entry was posted in Arrays, Excel, UDFs and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.