Formulas added in Excel 2010 and later

Excel 2010 and later versions added functions which weren't defined in the original file specification. These functions are referred to by Microsoft as "Future Functions". Examples of these functions are ACOT, CHISQ.DIST.RT , CONFIDENCE.NORM, STDEV.P, STDEV.S and WORKDAY.INTL.

When written using worksheet.write_formula() these functions need to be fully qualified with a prefix such as _xlfn., as shown the table in the next section below.

If the prefix isn't included you will get an Excel function name error. For example:

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

//! The following example demonstrates writing an Excel "Future Function" without
//! an explicit prefix, which results in an Excel error.

use rust_xlsxwriter::{Workbook, XlsxError};

fn main() -> Result<(), XlsxError> {
    let mut workbook = Workbook::new();
    let worksheet = workbook.add_worksheet();

    // Write a future function without prefix. This gives an Excel error.
    worksheet.write_formula(0, 0, "=STDEV.S(B1:B5)")?;

    // Write some data for the function to operate on.
    worksheet.write_number(0, 1, 1.23)?;
    worksheet.write_number(1, 1, 1.03)?;
    worksheet.write_number(2, 1, 1.20)?;
    worksheet.write_number(3, 1, 1.15)?;
    worksheet.write_number(4, 1, 1.22)?;

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

    Ok(())
}

If the _xlfn. prefix is included you will get the correct result:

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

//! The following example demonstrates writing an Excel "Future Function" with
//! an explicit prefix.

use rust_xlsxwriter::{Workbook, XlsxError};

fn main() -> Result<(), XlsxError> {
    let mut workbook = Workbook::new();
    let worksheet = workbook.add_worksheet();

    // Write a future function with explicit prefix.
    worksheet.write_formula(0, 0, "=_xlfn.STDEV.S(B1:B5)")?;

    // Write some data for the function to operate on.
    worksheet.write_number(0, 1, 1.23)?;
    worksheet.write_number(1, 1, 1.03)?;
    worksheet.write_number(2, 1, 1.20)?;
    worksheet.write_number(3, 1, 1.15)?;
    worksheet.write_number(4, 1, 1.22)?;

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

    Ok(())
}

Note: The function is displayed by Excel without the prefix.

Alternatively you can use the worksheet.use_future_functions() function to have rust_xlsxwriter automatically handle future functions for you:

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

//! The following example demonstrates writing an Excel "Future Function" with
//! an implicit prefix and the use_future_functions() method.

use rust_xlsxwriter::{Workbook, XlsxError};

fn main() -> Result<(), XlsxError> {
    let mut workbook = Workbook::new();
    let worksheet = workbook.add_worksheet();

    // Write a future function and automatically add the required prefix.
    worksheet.use_future_functions(true);
    worksheet.write_formula(0, 0, "=STDEV.S(B1:B5)")?;

    // Write some data for the function to operate on.
    worksheet.write_number(0, 1, 1.23)?;
    worksheet.write_number(1, 1, 1.03)?;
    worksheet.write_number(2, 1, 1.20)?;
    worksheet.write_number(3, 1, 1.15)?;
    worksheet.write_number(4, 1, 1.22)?;

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

    Ok(())
}

This will give the same correct result as the image above.

List of Future Functions

The following list is taken from MS XLSX extensions documentation on future functions.

The dynamic array functions shown in the Dynamic Array support section are also future functions, however the rust_xlsxwriter library automatically adds the required prefixes on the fly so you don't have to add them explicitly.