# How to Use Dynamic Array Formulas Like FILTER in OfficeCLI

> Learn to use dynamic array formulas like FILTER in OfficeCLI. OfficeCLI automatically generates Excel metadata for spill ranges, simplifying your data analysis without manual cell population.

- Repository: [OfficeAI/OfficeCLI](https://github.com/iofficeai/OfficeCLI)
- Tags: how-to-guide
- Published: 2026-07-28

---

**OfficeCLI automatically generates the required Excel metadata for dynamic array formulas when you write to cells using the `set` command, enabling Excel 365 to calculate spill ranges without requiring manual population of derived cells.**

OfficeCLI is an open-source Excel manipulation engine that evaluates over 350 built-in functions, including the modern dynamic array family. When you write a formula like `FILTER` to a workbook, the tool handles the complex Open XML metadata generation internally, ensuring the anchor cell is properly marked for Excel 365 to recognize and expand the spill region automatically.

## How OfficeCLI Handles Dynamic Array Metadata

The core functionality resides in **[`ExcelHandler.DynamicArray.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.DynamicArray.cs)**. When you write a dynamic array formula, the `EnsureDynamicArrayMetadata` method creates a single `CellMetadataPart` containing the **XLDAPR** record and assigns `cell.CellMetaIndex = 1U` to the anchor cell.

This metadata assignment (exposed as `cm="1"` in the XML) signals to Excel 365 that the cell contains a dynamic array formula. During the write operation in [`ExcelHandler.Set.Cells.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Set.Cells.cs), the cell receives the attribute `t="array"` along with the formula string, while OfficeCLI manages the metadata part behind the scenes.

The function validation pipeline begins in **[`ModernFunctionQualifier.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ModernFunctionQualifier.cs)**, which enumerates supported dynamic array functions including `"FILTER"` (line 70). The actual implementation mapping occurs in **[`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs)**, where the token `"FILTER"` routes to the `EvalFilter` method.

## Writing FILTER Formulas via Command Line

To apply a dynamic array formula, use the `set` command with the `--prop formula` parameter. OfficeCLI parses the expression, registers the function, and attaches the necessary metadata automatically.

```bash

# Create a workbook

officecli create data.xlsx

# Add a sheet

officecli add data.xlsx / --type sheet --prop name="Sales"

# Populate source data

officecli set data.xlsx /Sales!B2 --prop value=120
officecli set data.xlsx /Sales!B3 --prop value=80
officecli set data.xlsx /Sales!C2 --prop value=7
officecli set data.xlsx /Sales!C3 --prop value=3

# Apply FILTER formula to show values where column C > 5

officecli set data.xlsx /Sales!A1 \
  --prop formula="=FILTER(B2:B3, C2:C3>5)"

```

## Using the Python SDK

The Python SDK provides equivalent functionality through the `send` method, handling the same metadata generation transparently.

```python
import officecli

with officecli.create("data.xlsx") as wb:
    wb.send({"command":"add","parent":"/","type":"sheet","props":{"name":"Sales"}})
    wb.send({"command":"set","path":"/Sales!B2","props":{"value":120}})
    wb.send({"command":"set","path":"/Sales!B3","props":{"value":80}})
    wb.send({"command":"set","path":"/Sales!C2","props":{"value":7}})
    wb.send({"command":"set","path":"/Sales!C3","props":{"value":3}})
    wb.send({"command":"set","path":"/Sales!A1","props":{"formula":"=FILTER(B2:B3, C2:C3>5)"}})
    
    # Verify the calculated value

    result = wb.send({"command":"get","path":"/Sales!A1","json":True})
    print(result)

```

## Reading Spilled Values

Retrieve the calculated results using the `get` command. OfficeCLI reads the computed value from the anchor cell, while Excel handles the spill range calculation internally based on the embedded metadata.

```bash

# Get the anchor cell value

officecli get data.xlsx /Sales!A1 --json

# Get a range that may include spilled values

officecli get data.xlsx /Sales!A1:A10 --json

```

## Metadata Reuse and Safety Considerations

The `EnsureDynamicArrayMetadata` method checks for existing `CellMetadataPart` components before creating new ones. If the workbook already contains cell metadata, OfficeCLI reuses the existing part while ensuring the anchor cell's `cm` attribute points to index 1.

**Key points to remember:**

- **Only write the anchor cell.** Never manually populate cells within the spill range, as Excel 365 calculates these automatically when opening the file.
- **Spill errors surface naturally.** If the spill range overlaps existing data, Excel returns a `#SPILL!` error, which OfficeCLI exposes through its structured JSON output.
- **Safe for existing workbooks.** The metadata management logic prevents duplication of `CellMetadataPart` components when modifying existing files.

## Summary

- **OfficeCLI** in `iOfficeAI/OfficeCLI` supports dynamic array formulas like `FILTER`, `SORT`, `UNIQUE`, `SEQUENCE`, `LET`, `LAMBDA`, and `MAP` through automatic metadata generation.
- The `EnsureDynamicArrayMetadata` method in **[`ExcelHandler.DynamicArray.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.DynamicArray.cs)** creates the XLDAPR record and sets `CellMetaIndex = 1U` to mark anchor cells.
- Use **[`CommandBuilder.Set.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/CommandBuilder.Set.cs)** functionality via `officecli set <file> /Sheet!Cell --prop formula="<expression>"` to write dynamic array formulas.
- **Function validation** occurs in [`ModernFunctionQualifier.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ModernFunctionQualifier.cs), while evaluation logic resides in [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs).
- **Excel 365** reads the generated metadata and calculates spill ranges automatically when opening the file, requiring no additional write operations from OfficeCLI.

## Frequently Asked Questions

### Do I need to manually write values to spilled cells?

No. When you write a dynamic array formula to the anchor cell using `officecli set`, OfficeCLI only writes the formula and the required metadata. Excel 365 calculates and displays the spilled values when the file opens. Writing to cells within the spill range would cause `#SPILL!` errors.

### What happens if the spill range overlaps existing data?

Excel 365 detects the collision and displays a `#SPILL!` error in the anchor cell. OfficeCLI surfaces this error through its structured JSON output when you attempt to read the cell value, allowing you to identify and resolve the overlap.

### Can I use other dynamic array functions besides FILTER?

Yes. OfficeCLI supports the full modern dynamic array family including **SORT**, **UNIQUE**, **SEQUENCE**, **LET**, **LAMBDA**, and **MAP**, as registered in [`ModernFunctionQualifier.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ModernFunctionQualifier.cs) and implemented in [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs). All follow the same metadata generation pattern.

### How does OfficeCLI handle existing workbooks with metadata?

The `EnsureDynamicArrayMetadata` method checks for existing `CellMetadataPart` components before creating new ones. It safely reuses existing parts while ensuring the anchor cell references index 1, preventing metadata duplication and maintaining file integrity.