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.
Microsoft 365 Excel 2018 or later - supports dynamic arrays which automatically 'spill' the result set into the worksheet
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 dynamic 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 formula spill behaviour.
Refer to Microsoft's documentation on dynamic arrays and legacy 'ctrl-shift-enter' behaviour.
Legacy Mode
Legacy Mode emulates the latest Microsoft 365 dynamic array behaviour in older Office Excel 2010-2019 versions by populating the cells neighbouring that holding an FXLData formula with the data result set.
In versions of Microsoft 365 Excel that do support dynamic arrays, legacy mode can also be used to write the result set directly into the worksheet. That's useful in situations where discrete data points are needed rather than an Excel array which has certain limitations.
Office Excel 2010-2019
FXLData always works in legacy mode when using Office Excel 2010-2019.
When an FXLData function is executed, the first cell of the result set will appear as the formula result. If automatic calculation is enabled, FXLData's dynamic array emulation will immediately populate the neighbouring cells with the remainder of the result set. If automatic calculation is disabled, Refresh must be used.
Office 2010-2019 - FXLData functions operate in legacy mode which provides dynamic array emulation
Forcing Microsoft 365 Excel to use Legacy Mode
FXLData functions in Microsoft 365 Excel operate as dynamic array formulas by default where the result set appears in the worksheet as an Excel array.
Using the Implicit intersection operator: @ however forces the FXLData function to operate in legacy mode causing the result set to be written directly into the worksheet.
=@FXLData("https://registry.domain.org/FusionRegistry","ECB,EXR,1.0","A..BGN+BEF..",100,"round=2","2010","2020","id",TRUE,FALSE,TRUE,FALSE)
In the above example the @ preceeding the FXLData function name instructs Excel to return the implicit intersection of the result set array which is just the first cell. If legacy mode automatic calculation is enabled, the FXLData's dynamic array emulation will populate the neighbouring cells. If not, Refresh must be used.
