PyXLL has excellent support for Excel array functions.
See Excel Array Functions and Dynamic Arrays in Python for more details.
Return values from user defined functions can be Python lists, which when returned to Excel become a column of data[1]. Two dimensional arrays can be returned also, using a list of lists. PyXLL can also convert other array types, such as numpy arrays and Pandas DataFrames to Excel arrays.
When returning an array to Excel, PyXLL can automatically resize the formula so that the entire result is shown. To enable this, set auto_resize=True in xl_func
.
Excel added a new feature ‘Dynamic Arrays’ recently to Excel. This is fully supported by PyXLL and when present is used instead of PyXLL’s own array resizing.
[1] | By default one dimensional lists are returned as columns, but the kwarg ‘transpose’ to xl_func can be used to return a row instead. |