[GH-ISSUE #327] Adding support for new dynamic array functionality in Excel #262

New issue

Closed

opened 2026-05-05 12:03:27 -06:00 by gitea-mirror · 3 comments

gitea-mirror commented

2026-05-05 12:03:27 -06:00

Owner

Originally created by @jmcnamara on GitHub (Apr 22, 2021).
Original GitHub issue: https://github.com/jmcnamara/libxlsxwriter/issues/327

Originally assigned to: @jmcnamara on GitHub.

Excel365 has introduced new dynamic array functionality to formulas and functions. The dynamic array functions are:

FILTER
RANDARRAY
SEQUENCE
SORT
SORTBY
UNIQUE
XLOOKUP
XMATCH

In addition array functions have been updated to use the new implicit intersection operator @.

The work is in progress and I will be adding support for these functions and features in the next release. Add a +1 if you want to get updates for early testing.

For more information see:

Originally created by @jmcnamara on GitHub (Apr 22, 2021). Original GitHub issue: https://github.com/jmcnamara/libxlsxwriter/issues/327 Originally assigned to: @jmcnamara on GitHub. Excel365 has introduced new dynamic array functionality to formulas and functions. The dynamic array functions are: - FILTER - RANDARRAY - SEQUENCE - SORT - SORTBY - UNIQUE - XLOOKUP - XMATCH In addition array functions have been updated to use the new implicit intersection operator @. The work is in progress and I will be adding support for these functions and features in the next release. Add a +1 if you want to get updates for early testing. For more information see: - [Dynamic array formulas in Excel](https://exceljet.net/dynamic-array-formulas-in-excel) - [Implicit intersection operator: @](https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34?ui=en-us&rs=en-us&ad=us) - [Dynamic array formulas and spilled array behavior](https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531) - [Dynamic array formulas vs. legacy CSE array formulas](https://support.microsoft.com/en-us/office/dynamic-array-formulas-vs-legacy-cse-array-formulas-ca421f1b-fbb2-4c99-9924-df571bd4f1b4) - [SORT function](https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c)

gitea-mirror closed this issue

2026-05-05 12:03:28 -06:00

gitea-mirror commented

2026-05-05 12:03:31 -06:00

Author

Owner

@jmcnamara commented on GitHub (Apr 23, 2021):

I've pushed an initial version of dynamic array support to master/main and added a new function called worksheet_write_dynamic_array_formula() which has the same syntax as worksheet_write_array_formula(). Here is an example:

#include "xlsxwriter.h"

int main() {

    lxw_workbook  *workbook  = workbook_new("unique.xlsx");
    lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);

    worksheet_write_dynamic_array_formula(worksheet, RANGE("A1:A1"), "=_xlfn.UNIQUE(C1:C6)", NULL);

    worksheet_write_number(worksheet, CELL("C1"), 1 , NULL);
    worksheet_write_number(worksheet, CELL("C2"), 1 , NULL);
    worksheet_write_number(worksheet, CELL("C3"), 2 , NULL);
    worksheet_write_number(worksheet, CELL("C4"), 1 , NULL);
    worksheet_write_number(worksheet, CELL("C5"), 3 , NULL);
    worksheet_write_number(worksheet, CELL("C6"), 1 , NULL);

    return workbook_close(workbook);
}

And here is the output:

If you try it out and encounter any issue (or if you encounter no issues) please let me know.

@jmcnamara commented on GitHub (Apr 23, 2021): I've pushed an initial version of dynamic array support to master/main and added a new function called `worksheet_write_dynamic_array_formula()` which has the same syntax as `worksheet_write_array_formula()`. Here is an example: ```C #include "xlsxwriter.h" int main() { lxw_workbook *workbook = workbook_new("unique.xlsx"); lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL); worksheet_write_dynamic_array_formula(worksheet, RANGE("A1:A1"), "=_xlfn.UNIQUE(C1:C6)", NULL); worksheet_write_number(worksheet, CELL("C1"), 1 , NULL); worksheet_write_number(worksheet, CELL("C2"), 1 , NULL); worksheet_write_number(worksheet, CELL("C3"), 2 , NULL); worksheet_write_number(worksheet, CELL("C4"), 1 , NULL); worksheet_write_number(worksheet, CELL("C5"), 3 , NULL); worksheet_write_number(worksheet, CELL("C6"), 1 , NULL); return workbook_close(workbook); } ``` And here is the output: ![Screenshot 2021-04-23 at 07 38 03](https://user-images.githubusercontent.com/94267/115829581-10e37300-a407-11eb-8c3f-45b4fa8c7513.png) If you try it out and encounter any issue (or if you encounter no issues) please let me know.

gitea-mirror commented

2026-05-05 12:03:32 -06:00

Author

Owner

@Robert-M-Muench commented on GitHub (May 17, 2021):

Sorry for not having tried it yet, but I will. Super cool stuff!!

@Robert-M-Muench commented on GitHub (May 17, 2021): Sorry for not having tried it yet, but I will. Super cool stuff!!

gitea-mirror commented

2026-05-05 12:03:35 -06:00

Author

Owner

@jmcnamara commented on GitHub (Jul 7, 2021):

This functionality has been added in version 1.0.9. See https://libxlsxwriter.github.io/working_with_formulas.html#ww_formulas_dynamic_arrays and https://libxlsxwriter.github.io/dynamic_arrays_8c-example.html#a14.

Closing.

@jmcnamara commented on GitHub (Jul 7, 2021): This functionality has been added in version 1.0.9. See https://libxlsxwriter.github.io/working_with_formulas.html#ww_formulas_dynamic_arrays and https://libxlsxwriter.github.io/dynamic_arrays_8c-example.html#a14. Closing.