Dynamic Array support

In Office 365 Excel introduced the concept of "Dynamic Arrays" and new functions that use them. The new functions are:

  • FILTER
  • RANDARRAY
  • SEQUENCE
  • SORTBY
  • SORT
  • UNIQUE
  • XLOOKUP
  • XMATCH

The following special case functions were also added with Dynamic Arrays:

  • SINGLE: Explained below in The Implicit Intersection Operator "@"
  • ANCHORARRAY: Explained below in The Spilled Range Operator "#"

Dynamic Arrays - An introduction

Dynamic arrays in Excel are ranges of return values that can change size based on the results. For example, a function such as FILTER() returns an array of values that can vary in size depending on the the filter results:

// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2024, John McNamara, jmcnamara@cpan.org

//! An example of how to use the rust_xlsxwriter library to write formulas and
//! functions that create dynamic arrays. These functions are new to Excel
//! 365. The examples mirror the examples in the Excel documentation for these
//! functions.
use rust_xlsxwriter::{Color, Format, Workbook, Worksheet, XlsxError};

fn main() -> Result<(), XlsxError> {
    // Create a new Excel file object.
    let mut workbook = Workbook::new();

    // Create some header formats to use in the worksheets.
    let header1 = Format::new()
        .set_foreground_color(Color::RGB(0x74AC4C))
        .set_font_color(Color::RGB(0xFFFFFF));

    let header2 = Format::new()
        .set_foreground_color(Color::RGB(0x528FD3))
        .set_font_color(Color::RGB(0xFFFFFF));

    // -----------------------------------------------------------------------
    // Example of using the FILTER() function.
    // -----------------------------------------------------------------------
    let worksheet1 = workbook.add_worksheet().set_name("Filter")?;

    worksheet1.write_dynamic_formula(1, 5, "=FILTER(A1:D17,C1:C17=K2)")?;

    // Write the data the function will work on.
    worksheet1.write_string_with_format(0, 10, "Product", &header2)?;
    worksheet1.write_string(1, 10, "Apple")?;
    worksheet1.write_string_with_format(0, 5, "Region", &header2)?;
    worksheet1.write_string_with_format(0, 6, "Sales Rep", &header2)?;
    worksheet1.write_string_with_format(0, 7, "Product", &header2)?;
    worksheet1.write_string_with_format(0, 8, "Units", &header2)?;

    // Add sample worksheet data to work on.
    write_worksheet_data(worksheet1, &header1)?;
    worksheet1.set_column_width_pixels(4, 20)?;
    worksheet1.set_column_width_pixels(9, 20)?;

    // -----------------------------------------------------------------------
    // Example of using the UNIQUE() function.
    // -----------------------------------------------------------------------
    let worksheet2 = workbook.add_worksheet().set_name("Unique")?;

    worksheet2.write_dynamic_formula(1, 5, "=UNIQUE(B2:B17)")?;

    // A more complex example combining SORT and UNIQUE.
    worksheet2.write_dynamic_formula(1, 7, "SORT(UNIQUE(B2:B17))")?;

    // Write the data the function will work on.
    worksheet2.write_string_with_format(0, 5, "Sales Rep", &header2)?;
    worksheet2.write_string_with_format(0, 7, "Sales Rep", &header2)?;

    // Add sample worksheet data to work on.
    write_worksheet_data(worksheet2, &header1)?;
    worksheet2.set_column_width_pixels(4, 20)?;
    worksheet2.set_column_width_pixels(6, 20)?;

    // -----------------------------------------------------------------------
    // Example of using the SORT() function.
    // -----------------------------------------------------------------------
    let worksheet3 = workbook.add_worksheet().set_name("Sort")?;

    // A simple SORT example.
    worksheet3.write_dynamic_formula(1, 5, "=SORT(B2:B17)")?;

    // A more complex example combining SORT and FILTER.
    worksheet3.write_dynamic_formula(1, 7, r#"=SORT(FILTER(C2:D17,D2:D17>5000,""),2,1)"#)?;

    // Write the data the function will work on.
    worksheet3.write_string_with_format(0, 5, "Sales Rep", &header2)?;
    worksheet3.write_string_with_format(0, 7, "Product", &header2)?;
    worksheet3.write_string_with_format(0, 8, "Units", &header2)?;

    // Add sample worksheet data to work on.
    write_worksheet_data(worksheet3, &header1)?;
    worksheet3.set_column_width_pixels(4, 20)?;
    worksheet3.set_column_width_pixels(6, 20)?;

    // -----------------------------------------------------------------------
    // Example of using the SORTBY() function.
    // -----------------------------------------------------------------------
    let worksheet4 = workbook.add_worksheet().set_name("Sortby")?;

    worksheet4.write_dynamic_formula(1, 3, "=SORTBY(A2:B9,B2:B9)")?;

    // Write the data the function will work on.
    worksheet4.write_string_with_format(0, 0, "Name", &header1)?;
    worksheet4.write_string_with_format(0, 1, "Age", &header1)?;

    worksheet4.write_string(1, 0, "Tom")?;
    worksheet4.write_string(2, 0, "Fred")?;
    worksheet4.write_string(3, 0, "Amy")?;
    worksheet4.write_string(4, 0, "Sal")?;
    worksheet4.write_string(5, 0, "Fritz")?;
    worksheet4.write_string(6, 0, "Srivan")?;
    worksheet4.write_string(7, 0, "Xi")?;
    worksheet4.write_string(8, 0, "Hector")?;

    worksheet4.write_number(1, 1, 52)?;
    worksheet4.write_number(2, 1, 65)?;
    worksheet4.write_number(3, 1, 22)?;
    worksheet4.write_number(4, 1, 73)?;
    worksheet4.write_number(5, 1, 19)?;
    worksheet4.write_number(6, 1, 39)?;
    worksheet4.write_number(7, 1, 19)?;
    worksheet4.write_number(8, 1, 66)?;

    worksheet4.write_string_with_format(0, 3, "Name", &header2)?;
    worksheet4.write_string_with_format(0, 4, "Age", &header2)?;

    worksheet4.set_column_width_pixels(2, 20)?;

    // -----------------------------------------------------------------------
    // Example of using the XLOOKUP() function.
    // -----------------------------------------------------------------------
    let worksheet5 = workbook.add_worksheet().set_name("Xlookup")?;

    worksheet5.write_dynamic_formula(0, 5, "=XLOOKUP(E1,A2:A9,C2:C9)")?;

    // Write the data the function will work on.
    worksheet5.write_string_with_format(0, 0, "Country", &header1)?;
    worksheet5.write_string_with_format(0, 1, "Abr", &header1)?;
    worksheet5.write_string_with_format(0, 2, "Prefix", &header1)?;

    worksheet5.write_string(1, 0, "China")?;
    worksheet5.write_string(2, 0, "India")?;
    worksheet5.write_string(3, 0, "United States")?;
    worksheet5.write_string(4, 0, "Indonesia")?;
    worksheet5.write_string(5, 0, "Brazil")?;
    worksheet5.write_string(6, 0, "Pakistan")?;
    worksheet5.write_string(7, 0, "Nigeria")?;
    worksheet5.write_string(8, 0, "Bangladesh")?;

    worksheet5.write_string(1, 1, "CN")?;
    worksheet5.write_string(2, 1, "IN")?;
    worksheet5.write_string(3, 1, "US")?;
    worksheet5.write_string(4, 1, "ID")?;
    worksheet5.write_string(5, 1, "BR")?;
    worksheet5.write_string(6, 1, "PK")?;
    worksheet5.write_string(7, 1, "NG")?;
    worksheet5.write_string(8, 1, "BD")?;

    worksheet5.write_number(1, 2, 86)?;
    worksheet5.write_number(2, 2, 91)?;
    worksheet5.write_number(3, 2, 1)?;
    worksheet5.write_number(4, 2, 62)?;
    worksheet5.write_number(5, 2, 55)?;
    worksheet5.write_number(6, 2, 92)?;
    worksheet5.write_number(7, 2, 234)?;
    worksheet5.write_number(8, 2, 880)?;

    worksheet5.write_string_with_format(0, 4, "Brazil", &header2)?;

    worksheet5.set_column_width_pixels(0, 100)?;
    worksheet5.set_column_width_pixels(3, 20)?;

    // -----------------------------------------------------------------------
    // Example of using the XMATCH() function.
    // -----------------------------------------------------------------------
    let worksheet6 = workbook.add_worksheet().set_name("Xmatch")?;

    worksheet6.write_dynamic_formula(1, 3, "=XMATCH(C2,A2:A6)")?;

    // Write the data the function will work on.
    worksheet6.write_string_with_format(0, 0, "Product", &header1)?;

    worksheet6.write_string(1, 0, "Apple")?;
    worksheet6.write_string(2, 0, "Grape")?;
    worksheet6.write_string(3, 0, "Pear")?;
    worksheet6.write_string(4, 0, "Banana")?;
    worksheet6.write_string(5, 0, "Cherry")?;

    worksheet6.write_string_with_format(0, 2, "Product", &header2)?;
    worksheet6.write_string_with_format(0, 3, "Position", &header2)?;
    worksheet6.write_string(1, 2, "Grape")?;

    worksheet6.set_column_width_pixels(1, 20)?;

    // -----------------------------------------------------------------------
    // Example of using the RANDARRAY() function.
    // -----------------------------------------------------------------------
    let worksheet7 = workbook.add_worksheet().set_name("Randarray")?;

    worksheet7.write_dynamic_formula(0, 0, "=RANDARRAY(5,3,1,100, TRUE)")?;

    // -----------------------------------------------------------------------
    // Example of using the SEQUENCE() function.
    // -----------------------------------------------------------------------
    let worksheet8 = workbook.add_worksheet().set_name("Sequence")?;

    worksheet8.write_dynamic_formula(0, 0, "=SEQUENCE(4,5)")?;

    // -----------------------------------------------------------------------
    // Example of using the Spill range operator.
    // -----------------------------------------------------------------------
    let worksheet9 = workbook.add_worksheet().set_name("Spill ranges")?;

    worksheet9.write_dynamic_formula(1, 7, "=ANCHORARRAY(F2)")?;

    worksheet9.write_dynamic_formula(1, 9, "=COUNTA(ANCHORARRAY(F2))")?;

    // Write the data the to work on.
    worksheet9.write_dynamic_formula(1, 5, "=UNIQUE(B2:B17)")?;
    worksheet9.write_string_with_format(0, 5, "Unique", &header2)?;
    worksheet9.write_string_with_format(0, 7, "Spill", &header2)?;
    worksheet9.write_string_with_format(0, 9, "Spill", &header2)?;

    // Add sample worksheet data to work on.
    write_worksheet_data(worksheet9, &header1)?;
    worksheet9.set_column_width_pixels(4, 20)?;
    worksheet9.set_column_width_pixels(6, 20)?;
    worksheet9.set_column_width_pixels(8, 20)?;

    // -----------------------------------------------------------------------
    // Example of using dynamic ranges with older Excel functions.
    // -----------------------------------------------------------------------
    let worksheet10 = workbook.add_worksheet().set_name("Older functions")?;

    worksheet10.write_dynamic_array_formula(0, 1, 2, 1, "=LEN(A1:A3)")?;

    // Write the data the to work on.
    worksheet10.write_string(0, 0, "Foo")?;
    worksheet10.write_string(1, 0, "Food")?;
    worksheet10.write_string(2, 0, "Frood")?;

    workbook.save("dynamic_arrays.xlsx")?;

    Ok(())
}

// A simple function and data structure to populate some of the worksheets.
fn write_worksheet_data(worksheet: &mut Worksheet, header: &Format) -> Result<(), XlsxError> {
    let worksheet_data = vec![
        ("East", "Tom", "Apple", 6380),
        ("West", "Fred", "Grape", 5619),
        ("North", "Amy", "Pear", 4565),
        ("South", "Sal", "Banana", 5323),
        ("East", "Fritz", "Apple", 4394),
        ("West", "Sravan", "Grape", 7195),
        ("North", "Xi", "Pear", 5231),
        ("South", "Hector", "Banana", 2427),
        ("East", "Tom", "Banana", 4213),
        ("West", "Fred", "Pear", 3239),
        ("North", "Amy", "Grape", 6520),
        ("South", "Sal", "Apple", 1310),
        ("East", "Fritz", "Banana", 6274),
        ("West", "Sravan", "Pear", 4894),
        ("North", "Xi", "Grape", 7580),
        ("South", "Hector", "Apple", 9814),
    ];

    worksheet.write_string_with_format(0, 0, "Region", header)?;
    worksheet.write_string_with_format(0, 1, "Sales Rep", header)?;
    worksheet.write_string_with_format(0, 2, "Product", header)?;
    worksheet.write_string_with_format(0, 3, "Units", header)?;

    let mut row = 1;
    for data in worksheet_data.iter() {
        worksheet.write_string(row, 0, data.0)?;
        worksheet.write_string(row, 1, data.1)?;
        worksheet.write_string(row, 2, data.2)?;
        worksheet.write_number(row, 3, data.3)?;
        row += 1;
    }

    Ok(())
}

This formula gives the results shown in the image below. The dynamic range here is "F2:I5" but it can vary based on the filter criteria.

Image 2 of output from app_dynamic_arrays.rs

It is also possible to get dynamic array behavior with older Excel functions. For example, the Excel function "=LEN(A1)" applies to a single cell and returns a single value but it can also apply to a range of cells and return a range of values using an array formula like "{=LEN(A1:A3)}". This type of "static" array behavior is referred to as a CSE (Ctrl+Shift+Enter) formula and has existed in Excel since early versions. In Office 365 Excel updated and extended this behavior to create the concept of dynamic arrays. In Excel 365 you can now write the previous LEN function as "=LEN(A1:A3)" and get a dynamic range of return values:

Image of output from doc_working_with_formulas_dynamic_len.rs

The difference between the two types of array functions is explained in the Microsoft documentation on Dynamic array formulas vs. legacy CSE array formulas.

In rust_xlsxwriter you can use the Worksheet::write_array_formula() function to get a static/CSE range and Worksheet::write_dynamic_array_formula() or Worksheet::write_dynamic_formula() to get a dynamic range.

The Worksheet::write_dynamic_array_formula() function takes a (first_row, first_col, last_row, last_col) cell range to define the area that the formula applies to. However, since the range is dynamic this generally won't be known in advance in which case you can specify the range with the same start and end cell. The following range is "F2:F2":

    worksheet1.write_dynamic_array_formula(1, 5, 1, 5, "=FILTER(A1:D17,C1:C17=K2)")?;

As a syntactic shortcut you can use the Worksheet::write_dynamic_formula() function which only requires the start cell:

// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2024, John McNamara, jmcnamara@cpan.org

//! An example of how to use the rust_xlsxwriter library to write formulas and
//! functions that create dynamic arrays. These functions are new to Excel
//! 365. The examples mirror the examples in the Excel documentation for these
//! functions.
use rust_xlsxwriter::{Color, Format, Workbook, Worksheet, XlsxError};

fn main() -> Result<(), XlsxError> {
    // Create a new Excel file object.
    let mut workbook = Workbook::new();

    // Create some header formats to use in the worksheets.
    let header1 = Format::new()
        .set_foreground_color(Color::RGB(0x74AC4C))
        .set_font_color(Color::RGB(0xFFFFFF));

    let header2 = Format::new()
        .set_foreground_color(Color::RGB(0x528FD3))
        .set_font_color(Color::RGB(0xFFFFFF));

    // -----------------------------------------------------------------------
    // Example of using the FILTER() function.
    // -----------------------------------------------------------------------
    let worksheet1 = workbook.add_worksheet().set_name("Filter")?;

    worksheet1.write_dynamic_formula(1, 5, "=FILTER(A1:D17,C1:C17=K2)")?;

    // Write the data the function will work on.
    worksheet1.write_string_with_format(0, 10, "Product", &header2)?;
    worksheet1.write_string(1, 10, "Apple")?;
    worksheet1.write_string_with_format(0, 5, "Region", &header2)?;
    worksheet1.write_string_with_format(0, 6, "Sales Rep", &header2)?;
    worksheet1.write_string_with_format(0, 7, "Product", &header2)?;
    worksheet1.write_string_with_format(0, 8, "Units", &header2)?;

    // Add sample worksheet data to work on.
    write_worksheet_data(worksheet1, &header1)?;
    worksheet1.set_column_width_pixels(4, 20)?;
    worksheet1.set_column_width_pixels(9, 20)?;

    // -----------------------------------------------------------------------
    // Example of using the UNIQUE() function.
    // -----------------------------------------------------------------------
    let worksheet2 = workbook.add_worksheet().set_name("Unique")?;

    worksheet2.write_dynamic_formula(1, 5, "=UNIQUE(B2:B17)")?;

    // A more complex example combining SORT and UNIQUE.
    worksheet2.write_dynamic_formula(1, 7, "SORT(UNIQUE(B2:B17))")?;

    // Write the data the function will work on.
    worksheet2.write_string_with_format(0, 5, "Sales Rep", &header2)?;
    worksheet2.write_string_with_format(0, 7, "Sales Rep", &header2)?;

    // Add sample worksheet data to work on.
    write_worksheet_data(worksheet2, &header1)?;
    worksheet2.set_column_width_pixels(4, 20)?;
    worksheet2.set_column_width_pixels(6, 20)?;

    // -----------------------------------------------------------------------
    // Example of using the SORT() function.
    // -----------------------------------------------------------------------
    let worksheet3 = workbook.add_worksheet().set_name("Sort")?;

    // A simple SORT example.
    worksheet3.write_dynamic_formula(1, 5, "=SORT(B2:B17)")?;

    // A more complex example combining SORT and FILTER.
    worksheet3.write_dynamic_formula(1, 7, r#"=SORT(FILTER(C2:D17,D2:D17>5000,""),2,1)"#)?;

    // Write the data the function will work on.
    worksheet3.write_string_with_format(0, 5, "Sales Rep", &header2)?;
    worksheet3.write_string_with_format(0, 7, "Product", &header2)?;
    worksheet3.write_string_with_format(0, 8, "Units", &header2)?;

    // Add sample worksheet data to work on.
    write_worksheet_data(worksheet3, &header1)?;
    worksheet3.set_column_width_pixels(4, 20)?;
    worksheet3.set_column_width_pixels(6, 20)?;

    // -----------------------------------------------------------------------
    // Example of using the SORTBY() function.
    // -----------------------------------------------------------------------
    let worksheet4 = workbook.add_worksheet().set_name("Sortby")?;

    worksheet4.write_dynamic_formula(1, 3, "=SORTBY(A2:B9,B2:B9)")?;

    // Write the data the function will work on.
    worksheet4.write_string_with_format(0, 0, "Name", &header1)?;
    worksheet4.write_string_with_format(0, 1, "Age", &header1)?;

    worksheet4.write_string(1, 0, "Tom")?;
    worksheet4.write_string(2, 0, "Fred")?;
    worksheet4.write_string(3, 0, "Amy")?;
    worksheet4.write_string(4, 0, "Sal")?;
    worksheet4.write_string(5, 0, "Fritz")?;
    worksheet4.write_string(6, 0, "Srivan")?;
    worksheet4.write_string(7, 0, "Xi")?;
    worksheet4.write_string(8, 0, "Hector")?;

    worksheet4.write_number(1, 1, 52)?;
    worksheet4.write_number(2, 1, 65)?;
    worksheet4.write_number(3, 1, 22)?;
    worksheet4.write_number(4, 1, 73)?;
    worksheet4.write_number(5, 1, 19)?;
    worksheet4.write_number(6, 1, 39)?;
    worksheet4.write_number(7, 1, 19)?;
    worksheet4.write_number(8, 1, 66)?;

    worksheet4.write_string_with_format(0, 3, "Name", &header2)?;
    worksheet4.write_string_with_format(0, 4, "Age", &header2)?;

    worksheet4.set_column_width_pixels(2, 20)?;

    // -----------------------------------------------------------------------
    // Example of using the XLOOKUP() function.
    // -----------------------------------------------------------------------
    let worksheet5 = workbook.add_worksheet().set_name("Xlookup")?;

    worksheet5.write_dynamic_formula(0, 5, "=XLOOKUP(E1,A2:A9,C2:C9)")?;

    // Write the data the function will work on.
    worksheet5.write_string_with_format(0, 0, "Country", &header1)?;
    worksheet5.write_string_with_format(0, 1, "Abr", &header1)?;
    worksheet5.write_string_with_format(0, 2, "Prefix", &header1)?;

    worksheet5.write_string(1, 0, "China")?;
    worksheet5.write_string(2, 0, "India")?;
    worksheet5.write_string(3, 0, "United States")?;
    worksheet5.write_string(4, 0, "Indonesia")?;
    worksheet5.write_string(5, 0, "Brazil")?;
    worksheet5.write_string(6, 0, "Pakistan")?;
    worksheet5.write_string(7, 0, "Nigeria")?;
    worksheet5.write_string(8, 0, "Bangladesh")?;

    worksheet5.write_string(1, 1, "CN")?;
    worksheet5.write_string(2, 1, "IN")?;
    worksheet5.write_string(3, 1, "US")?;
    worksheet5.write_string(4, 1, "ID")?;
    worksheet5.write_string(5, 1, "BR")?;
    worksheet5.write_string(6, 1, "PK")?;
    worksheet5.write_string(7, 1, "NG")?;
    worksheet5.write_string(8, 1, "BD")?;

    worksheet5.write_number(1, 2, 86)?;
    worksheet5.write_number(2, 2, 91)?;
    worksheet5.write_number(3, 2, 1)?;
    worksheet5.write_number(4, 2, 62)?;
    worksheet5.write_number(5, 2, 55)?;
    worksheet5.write_number(6, 2, 92)?;
    worksheet5.write_number(7, 2, 234)?;
    worksheet5.write_number(8, 2, 880)?;

    worksheet5.write_string_with_format(0, 4, "Brazil", &header2)?;

    worksheet5.set_column_width_pixels(0, 100)?;
    worksheet5.set_column_width_pixels(3, 20)?;

    // -----------------------------------------------------------------------
    // Example of using the XMATCH() function.
    // -----------------------------------------------------------------------
    let worksheet6 = workbook.add_worksheet().set_name("Xmatch")?;

    worksheet6.write_dynamic_formula(1, 3, "=XMATCH(C2,A2:A6)")?;

    // Write the data the function will work on.
    worksheet6.write_string_with_format(0, 0, "Product", &header1)?;

    worksheet6.write_string(1, 0, "Apple")?;
    worksheet6.write_string(2, 0, "Grape")?;
    worksheet6.write_string(3, 0, "Pear")?;
    worksheet6.write_string(4, 0, "Banana")?;
    worksheet6.write_string(5, 0, "Cherry")?;

    worksheet6.write_string_with_format(0, 2, "Product", &header2)?;
    worksheet6.write_string_with_format(0, 3, "Position", &header2)?;
    worksheet6.write_string(1, 2, "Grape")?;

    worksheet6.set_column_width_pixels(1, 20)?;

    // -----------------------------------------------------------------------
    // Example of using the RANDARRAY() function.
    // -----------------------------------------------------------------------
    let worksheet7 = workbook.add_worksheet().set_name("Randarray")?;

    worksheet7.write_dynamic_formula(0, 0, "=RANDARRAY(5,3,1,100, TRUE)")?;

    // -----------------------------------------------------------------------
    // Example of using the SEQUENCE() function.
    // -----------------------------------------------------------------------
    let worksheet8 = workbook.add_worksheet().set_name("Sequence")?;

    worksheet8.write_dynamic_formula(0, 0, "=SEQUENCE(4,5)")?;

    // -----------------------------------------------------------------------
    // Example of using the Spill range operator.
    // -----------------------------------------------------------------------
    let worksheet9 = workbook.add_worksheet().set_name("Spill ranges")?;

    worksheet9.write_dynamic_formula(1, 7, "=ANCHORARRAY(F2)")?;

    worksheet9.write_dynamic_formula(1, 9, "=COUNTA(ANCHORARRAY(F2))")?;

    // Write the data the to work on.
    worksheet9.write_dynamic_formula(1, 5, "=UNIQUE(B2:B17)")?;
    worksheet9.write_string_with_format(0, 5, "Unique", &header2)?;
    worksheet9.write_string_with_format(0, 7, "Spill", &header2)?;
    worksheet9.write_string_with_format(0, 9, "Spill", &header2)?;

    // Add sample worksheet data to work on.
    write_worksheet_data(worksheet9, &header1)?;
    worksheet9.set_column_width_pixels(4, 20)?;
    worksheet9.set_column_width_pixels(6, 20)?;
    worksheet9.set_column_width_pixels(8, 20)?;

    // -----------------------------------------------------------------------
    // Example of using dynamic ranges with older Excel functions.
    // -----------------------------------------------------------------------
    let worksheet10 = workbook.add_worksheet().set_name("Older functions")?;

    worksheet10.write_dynamic_array_formula(0, 1, 2, 1, "=LEN(A1:A3)")?;

    // Write the data the to work on.
    worksheet10.write_string(0, 0, "Foo")?;
    worksheet10.write_string(1, 0, "Food")?;
    worksheet10.write_string(2, 0, "Frood")?;

    workbook.save("dynamic_arrays.xlsx")?;

    Ok(())
}

// A simple function and data structure to populate some of the worksheets.
fn write_worksheet_data(worksheet: &mut Worksheet, header: &Format) -> Result<(), XlsxError> {
    let worksheet_data = vec![
        ("East", "Tom", "Apple", 6380),
        ("West", "Fred", "Grape", 5619),
        ("North", "Amy", "Pear", 4565),
        ("South", "Sal", "Banana", 5323),
        ("East", "Fritz", "Apple", 4394),
        ("West", "Sravan", "Grape", 7195),
        ("North", "Xi", "Pear", 5231),
        ("South", "Hector", "Banana", 2427),
        ("East", "Tom", "Banana", 4213),
        ("West", "Fred", "Pear", 3239),
        ("North", "Amy", "Grape", 6520),
        ("South", "Sal", "Apple", 1310),
        ("East", "Fritz", "Banana", 6274),
        ("West", "Sravan", "Pear", 4894),
        ("North", "Xi", "Grape", 7580),
        ("South", "Hector", "Apple", 9814),
    ];

    worksheet.write_string_with_format(0, 0, "Region", header)?;
    worksheet.write_string_with_format(0, 1, "Sales Rep", header)?;
    worksheet.write_string_with_format(0, 2, "Product", header)?;
    worksheet.write_string_with_format(0, 3, "Units", header)?;

    let mut row = 1;
    for data in worksheet_data.iter() {
        worksheet.write_string(row, 0, data.0)?;
        worksheet.write_string(row, 1, data.1)?;
        worksheet.write_string(row, 2, data.2)?;
        worksheet.write_number(row, 3, data.3)?;
        row += 1;
    }

    Ok(())
}

For a wider and more general introduction to dynamic arrays see the following: Dynamic array formulas in Excel.

The Implicit Intersection Operator "@"

The Implicit Intersection Operator, "@", is used by Excel 365 to indicate a position in a formula that is implicitly returning a single value when a range or an array could be returned.

We can see how this operator works in practice by considering the formula we used in the last section: =LEN(A1:A3). In Excel versions without support for dynamic arrays, i.e. prior to Excel 365, this formula would operate on a single value from the input range and return a single value, like the following in Excel 2011:

Image of output from doc_working_with_formulas_static_len.rs in older Excel versions

There is an implicit conversion here of the range of input values, "A1:A3", to a single value "A1". Since this was the default behavior of older versions of Excel this conversion isn't highlighted in any way. But if you open the same file in Excel 365 it will appear as follows:

Image of output from doc_working_with_formulas_static_len.rs

The result of the formula is the same (this is important to note) and it still operates on, and returns, a single value. However the formula now contains a "@" operator to show that it is implicitly using a single value from the given range.

In Excel 365, and with Worksheet::write_dynamic_formula() in rust_xlsxwriter, it would operate on the entire range and return an array of values:

Image of output from doc_working_with_formulas_dynamic_len.rs

If you are encountering the Implicit Intersection Operator "@" for the first time then it is probably from a point of view of "why is Excel/rust_xlsxwriter putting @s in my formulas". In practical terms if you encounter this operator, and you don't intend it to be there, then you should probably write the formula as a CSE or dynamic array function using Worksheet::write_array_formula() or Worksheet::write_dynamic_array_formula()

A full explanation of this operator is given in the Microsoft documentation on the Implicit intersection operator: @.

One important thing to note is that the "@" operator isn't stored with the formula. It is just displayed by Excel 365 when reading "legacy" formulas. However, it is possible to write it to a formula, if necessary, using SINGLE(). The rare cases where this may be necessary are shown in the linked document in the previous paragraph.

The Spilled Range Operator "#"

In the sections above we saw that dynamic array formulas can return variable sized ranges of results. The Excel documentation refers to this as a "Spilled" range/array from the idea that the results spill into the required number of cells. This is explained in the Microsoft documentation on Dynamic array formulas and spilled array behavior.

Since a spilled range is variable in size a new operator is required to refer to the range. This operator is the Spilled range operator and it is represented by "#". For example, the range F2# in the image below is used to refer to a dynamic array returned by UNIQUE() in the cell F2:

Image of spill output from app_dynamic_arrays.rs

Unfortunately, Excel doesn't store the operator in the formula like this and in rust_xlsxwriter you need to use the explicit function ANCHORARRAY() to refer to a spilled range. The example in the image above was generated using the following formula:

// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2024, John McNamara, jmcnamara@cpan.org

//! An example of how to use the rust_xlsxwriter library to write formulas and
//! functions that create dynamic arrays. These functions are new to Excel
//! 365. The examples mirror the examples in the Excel documentation for these
//! functions.
use rust_xlsxwriter::{Color, Format, Workbook, Worksheet, XlsxError};

fn main() -> Result<(), XlsxError> {
    // Create a new Excel file object.
    let mut workbook = Workbook::new();

    // Create some header formats to use in the worksheets.
    let header1 = Format::new()
        .set_foreground_color(Color::RGB(0x74AC4C))
        .set_font_color(Color::RGB(0xFFFFFF));

    let header2 = Format::new()
        .set_foreground_color(Color::RGB(0x528FD3))
        .set_font_color(Color::RGB(0xFFFFFF));

    // -----------------------------------------------------------------------
    // Example of using the FILTER() function.
    // -----------------------------------------------------------------------
    let worksheet1 = workbook.add_worksheet().set_name("Filter")?;

    worksheet1.write_dynamic_formula(1, 5, "=FILTER(A1:D17,C1:C17=K2)")?;

    // Write the data the function will work on.
    worksheet1.write_string_with_format(0, 10, "Product", &header2)?;
    worksheet1.write_string(1, 10, "Apple")?;
    worksheet1.write_string_with_format(0, 5, "Region", &header2)?;
    worksheet1.write_string_with_format(0, 6, "Sales Rep", &header2)?;
    worksheet1.write_string_with_format(0, 7, "Product", &header2)?;
    worksheet1.write_string_with_format(0, 8, "Units", &header2)?;

    // Add sample worksheet data to work on.
    write_worksheet_data(worksheet1, &header1)?;
    worksheet1.set_column_width_pixels(4, 20)?;
    worksheet1.set_column_width_pixels(9, 20)?;

    // -----------------------------------------------------------------------
    // Example of using the UNIQUE() function.
    // -----------------------------------------------------------------------
    let worksheet2 = workbook.add_worksheet().set_name("Unique")?;

    worksheet2.write_dynamic_formula(1, 5, "=UNIQUE(B2:B17)")?;

    // A more complex example combining SORT and UNIQUE.
    worksheet2.write_dynamic_formula(1, 7, "SORT(UNIQUE(B2:B17))")?;

    // Write the data the function will work on.
    worksheet2.write_string_with_format(0, 5, "Sales Rep", &header2)?;
    worksheet2.write_string_with_format(0, 7, "Sales Rep", &header2)?;

    // Add sample worksheet data to work on.
    write_worksheet_data(worksheet2, &header1)?;
    worksheet2.set_column_width_pixels(4, 20)?;
    worksheet2.set_column_width_pixels(6, 20)?;

    // -----------------------------------------------------------------------
    // Example of using the SORT() function.
    // -----------------------------------------------------------------------
    let worksheet3 = workbook.add_worksheet().set_name("Sort")?;

    // A simple SORT example.
    worksheet3.write_dynamic_formula(1, 5, "=SORT(B2:B17)")?;

    // A more complex example combining SORT and FILTER.
    worksheet3.write_dynamic_formula(1, 7, r#"=SORT(FILTER(C2:D17,D2:D17>5000,""),2,1)"#)?;

    // Write the data the function will work on.
    worksheet3.write_string_with_format(0, 5, "Sales Rep", &header2)?;
    worksheet3.write_string_with_format(0, 7, "Product", &header2)?;
    worksheet3.write_string_with_format(0, 8, "Units", &header2)?;

    // Add sample worksheet data to work on.
    write_worksheet_data(worksheet3, &header1)?;
    worksheet3.set_column_width_pixels(4, 20)?;
    worksheet3.set_column_width_pixels(6, 20)?;

    // -----------------------------------------------------------------------
    // Example of using the SORTBY() function.
    // -----------------------------------------------------------------------
    let worksheet4 = workbook.add_worksheet().set_name("Sortby")?;

    worksheet4.write_dynamic_formula(1, 3, "=SORTBY(A2:B9,B2:B9)")?;

    // Write the data the function will work on.
    worksheet4.write_string_with_format(0, 0, "Name", &header1)?;
    worksheet4.write_string_with_format(0, 1, "Age", &header1)?;

    worksheet4.write_string(1, 0, "Tom")?;
    worksheet4.write_string(2, 0, "Fred")?;
    worksheet4.write_string(3, 0, "Amy")?;
    worksheet4.write_string(4, 0, "Sal")?;
    worksheet4.write_string(5, 0, "Fritz")?;
    worksheet4.write_string(6, 0, "Srivan")?;
    worksheet4.write_string(7, 0, "Xi")?;
    worksheet4.write_string(8, 0, "Hector")?;

    worksheet4.write_number(1, 1, 52)?;
    worksheet4.write_number(2, 1, 65)?;
    worksheet4.write_number(3, 1, 22)?;
    worksheet4.write_number(4, 1, 73)?;
    worksheet4.write_number(5, 1, 19)?;
    worksheet4.write_number(6, 1, 39)?;
    worksheet4.write_number(7, 1, 19)?;
    worksheet4.write_number(8, 1, 66)?;

    worksheet4.write_string_with_format(0, 3, "Name", &header2)?;
    worksheet4.write_string_with_format(0, 4, "Age", &header2)?;

    worksheet4.set_column_width_pixels(2, 20)?;

    // -----------------------------------------------------------------------
    // Example of using the XLOOKUP() function.
    // -----------------------------------------------------------------------
    let worksheet5 = workbook.add_worksheet().set_name("Xlookup")?;

    worksheet5.write_dynamic_formula(0, 5, "=XLOOKUP(E1,A2:A9,C2:C9)")?;

    // Write the data the function will work on.
    worksheet5.write_string_with_format(0, 0, "Country", &header1)?;
    worksheet5.write_string_with_format(0, 1, "Abr", &header1)?;
    worksheet5.write_string_with_format(0, 2, "Prefix", &header1)?;

    worksheet5.write_string(1, 0, "China")?;
    worksheet5.write_string(2, 0, "India")?;
    worksheet5.write_string(3, 0, "United States")?;
    worksheet5.write_string(4, 0, "Indonesia")?;
    worksheet5.write_string(5, 0, "Brazil")?;
    worksheet5.write_string(6, 0, "Pakistan")?;
    worksheet5.write_string(7, 0, "Nigeria")?;
    worksheet5.write_string(8, 0, "Bangladesh")?;

    worksheet5.write_string(1, 1, "CN")?;
    worksheet5.write_string(2, 1, "IN")?;
    worksheet5.write_string(3, 1, "US")?;
    worksheet5.write_string(4, 1, "ID")?;
    worksheet5.write_string(5, 1, "BR")?;
    worksheet5.write_string(6, 1, "PK")?;
    worksheet5.write_string(7, 1, "NG")?;
    worksheet5.write_string(8, 1, "BD")?;

    worksheet5.write_number(1, 2, 86)?;
    worksheet5.write_number(2, 2, 91)?;
    worksheet5.write_number(3, 2, 1)?;
    worksheet5.write_number(4, 2, 62)?;
    worksheet5.write_number(5, 2, 55)?;
    worksheet5.write_number(6, 2, 92)?;
    worksheet5.write_number(7, 2, 234)?;
    worksheet5.write_number(8, 2, 880)?;

    worksheet5.write_string_with_format(0, 4, "Brazil", &header2)?;

    worksheet5.set_column_width_pixels(0, 100)?;
    worksheet5.set_column_width_pixels(3, 20)?;

    // -----------------------------------------------------------------------
    // Example of using the XMATCH() function.
    // -----------------------------------------------------------------------
    let worksheet6 = workbook.add_worksheet().set_name("Xmatch")?;

    worksheet6.write_dynamic_formula(1, 3, "=XMATCH(C2,A2:A6)")?;

    // Write the data the function will work on.
    worksheet6.write_string_with_format(0, 0, "Product", &header1)?;

    worksheet6.write_string(1, 0, "Apple")?;
    worksheet6.write_string(2, 0, "Grape")?;
    worksheet6.write_string(3, 0, "Pear")?;
    worksheet6.write_string(4, 0, "Banana")?;
    worksheet6.write_string(5, 0, "Cherry")?;

    worksheet6.write_string_with_format(0, 2, "Product", &header2)?;
    worksheet6.write_string_with_format(0, 3, "Position", &header2)?;
    worksheet6.write_string(1, 2, "Grape")?;

    worksheet6.set_column_width_pixels(1, 20)?;

    // -----------------------------------------------------------------------
    // Example of using the RANDARRAY() function.
    // -----------------------------------------------------------------------
    let worksheet7 = workbook.add_worksheet().set_name("Randarray")?;

    worksheet7.write_dynamic_formula(0, 0, "=RANDARRAY(5,3,1,100, TRUE)")?;

    // -----------------------------------------------------------------------
    // Example of using the SEQUENCE() function.
    // -----------------------------------------------------------------------
    let worksheet8 = workbook.add_worksheet().set_name("Sequence")?;

    worksheet8.write_dynamic_formula(0, 0, "=SEQUENCE(4,5)")?;

    // -----------------------------------------------------------------------
    // Example of using the Spill range operator.
    // -----------------------------------------------------------------------
    let worksheet9 = workbook.add_worksheet().set_name("Spill ranges")?;

    worksheet9.write_dynamic_formula(1, 7, "=ANCHORARRAY(F2)")?;

    worksheet9.write_dynamic_formula(1, 9, "=COUNTA(ANCHORARRAY(F2))")?;

    // Write the data the to work on.
    worksheet9.write_dynamic_formula(1, 5, "=UNIQUE(B2:B17)")?;
    worksheet9.write_string_with_format(0, 5, "Unique", &header2)?;
    worksheet9.write_string_with_format(0, 7, "Spill", &header2)?;
    worksheet9.write_string_with_format(0, 9, "Spill", &header2)?;

    // Add sample worksheet data to work on.
    write_worksheet_data(worksheet9, &header1)?;
    worksheet9.set_column_width_pixels(4, 20)?;
    worksheet9.set_column_width_pixels(6, 20)?;
    worksheet9.set_column_width_pixels(8, 20)?;

    // -----------------------------------------------------------------------
    // Example of using dynamic ranges with older Excel functions.
    // -----------------------------------------------------------------------
    let worksheet10 = workbook.add_worksheet().set_name("Older functions")?;

    worksheet10.write_dynamic_array_formula(0, 1, 2, 1, "=LEN(A1:A3)")?;

    // Write the data the to work on.
    worksheet10.write_string(0, 0, "Foo")?;
    worksheet10.write_string(1, 0, "Food")?;
    worksheet10.write_string(2, 0, "Frood")?;

    workbook.save("dynamic_arrays.xlsx")?;

    Ok(())
}

// A simple function and data structure to populate some of the worksheets.
fn write_worksheet_data(worksheet: &mut Worksheet, header: &Format) -> Result<(), XlsxError> {
    let worksheet_data = vec![
        ("East", "Tom", "Apple", 6380),
        ("West", "Fred", "Grape", 5619),
        ("North", "Amy", "Pear", 4565),
        ("South", "Sal", "Banana", 5323),
        ("East", "Fritz", "Apple", 4394),
        ("West", "Sravan", "Grape", 7195),
        ("North", "Xi", "Pear", 5231),
        ("South", "Hector", "Banana", 2427),
        ("East", "Tom", "Banana", 4213),
        ("West", "Fred", "Pear", 3239),
        ("North", "Amy", "Grape", 6520),
        ("South", "Sal", "Apple", 1310),
        ("East", "Fritz", "Banana", 6274),
        ("West", "Sravan", "Pear", 4894),
        ("North", "Xi", "Grape", 7580),
        ("South", "Hector", "Apple", 9814),
    ];

    worksheet.write_string_with_format(0, 0, "Region", header)?;
    worksheet.write_string_with_format(0, 1, "Sales Rep", header)?;
    worksheet.write_string_with_format(0, 2, "Product", header)?;
    worksheet.write_string_with_format(0, 3, "Units", header)?;

    let mut row = 1;
    for data in worksheet_data.iter() {
        worksheet.write_string(row, 0, data.0)?;
        worksheet.write_string(row, 1, data.1)?;
        worksheet.write_string(row, 2, data.2)?;
        worksheet.write_number(row, 3, data.3)?;
        row += 1;
    }

    Ok(())
}

The Excel 365 LAMBDA() function

Recent versions of Excel 365 have introduced a powerful new function/feature called LAMBDA(). This is similar to closure expressions in Rust or [lambda expressions] (https://docs.microsoft.com/en-us/cpp/cpp/lambda-expressions-in-cpp?view=msvc-160) in C++ (and other languages).

Consider the following Excel example which converts the variable temp from Fahrenheit to Celsius:

    LAMBDA(temp, (5/9) * (temp-32))

This could be called in Excel with an argument:

    =LAMBDA(temp, (5/9) * (temp-32))(212)

Or assigned to a defined name and called as a user defined function:

    =ToCelsius(212)

A rust xlsxwriter example that replicates the described Excel functionality is shown below:

// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2024, John McNamara, jmcnamara@cpan.org

//! An example of using the new Excel LAMBDA() function with the rust_xlsxwriter
//! library.

use rust_xlsxwriter::{Workbook, XlsxError};

fn main() -> Result<(), XlsxError> {
    // Create a new Excel file object.
    let mut workbook = Workbook::new();

    // Write a Lambda function to convert Fahrenheit to Celsius to a cell as a
    // defined name and use that to calculate a value.
    //
    // Note that the formula name is prefixed with "_xlfn." (this is normally
    // converted automatically by write_formula*() but isn't for defined names)
    // and note that the lambda function parameters are prefixed with "_xlpm.".
    // These prefixes won't show up in Excel.
    workbook.define_name(
        "ToCelsius",
        "=_xlfn.LAMBDA(_xlpm.temp, (5/9) * (_xlpm.temp-32))",
    )?;

    // Add a worksheet to the workbook.
    let worksheet = workbook.add_worksheet();

    // Write the same Lambda function as a cell formula.
    //
    // Note that the lambda function parameters must be prefixed with "_xlpm.".
    // These prefixes won't show up in Excel.
    worksheet.write_formula(0, 0, "=LAMBDA(_xlpm.temp, (5/9) * (_xlpm.temp-32))(32)")?;

    // The user defined name needs to be written explicitly as a dynamic array
    // formula.
    worksheet.write_dynamic_formula(1, 0, "=ToCelsius(212)")?;

    // Save the file to disk.
    workbook.save("lambda.xlsx")?;

    Ok(())
}

Note, that the formula name must have a "_xlfn." prefix and the parameters in the LAMBDA() function must have a "_xlpm." prefix for compatibility with how the formulas are stored in Excel. These prefixes won't show up in the formula, as shown in the image.

Image of the output file:

Image of output from app_lambda.rs

The LET() function is often used in conjunction with LAMBDA() to assign names to calculation results.