Dynamic function arrays

Starting with Office 2021 and Office 365 (January 2020 build or later), Microsoft introduced support for dynamic arrays. Dynamic arrays can resize themselves if the data they contain updates. For instance, if a compressed data array shows the events which occurred over the last day, and that number of events increases the next time, the spreadsheet opens and the dynamic array will resize itself without user interaction.

By default, DataLink continues to write legacy function arrays. However, where dynamic arrays are supported, the user has the option to right-click on a legacy function array and convert to a dynamic array. After this conversion, any subsequent modifications to the function through the task pane will write to the sheet as a dynamic array.

When opening a workbook that contains dynamic arrays in an older version of Excel that is non-dynamic aware, the dynamic arrays are available as legacy function arrays. Therefore, we highly recommend only using dynamic arrays if all users who use the workbook have a version of Excel that supports the feature.