Which Version of Excel?

Microsoft 365 Excel 2018 and later

In Microsoft 365 versions of Excel, the FXLData functions act as dynamic array formulas which populate or 'spill' the result set into neighbouring cells on execution. There is no need for an explicit refresh because Excel will 'recalculate' any FXLData functions that have changed using its normal worksheet calculation behaviour. Settings Excel's calculation options to Automatic (the default) will automatically refresh an FXLData formula if the formula or something it depends upon such as a parameter in referenced cell has changed. If calculation is set to Manual, changed FXLData formulas will be refreshed when the user asks Excel to perform a recalculation using the Calculate Now or Calculate Sheet buttons on the ribbon bar. A complete recalculation can be forced using CTRL+ALT+SHIFT+F9.

Office 2010-2019 Excel

For these older versions of Excel which do not support dynamic array formulas, the FXLData functions work in legacy mode which emulates to some extent the dynamic array formula behaviour. On execution, the result set still populates into neighbouring cells. However, there are some important differences between legacy mode and proper dynamic arrays:

• In legacy mode, the result set is written directly into the worksheet which Excel treats as a collection of largely disconnected cells. By contrast, Excel treats a dynamic array as a single entity.
• Excel automatically protects against dyanamic arrays overwriting other content in the worksheet resulting in a #Spill! error if that would otherwise occur. Legacy mode will overwrite any existing content meaning that care is required to avoid worksheet corruption if the result set from an FXLData function is larger than expected.
• Legacy mode FXLData functions will be automatically refreshed if the formula itself is changed, but it not if a dependency of the formula changes. This is because the legacy mode refresh only emulates the proper dynamic array formuala spill behaviour.