Dynamic Arrays¶

Dynamic arrays are one of Excel 365's most powerful features. A single formula can return multiple values that "spill" into adjacent cells. Codcel fully supports dynamic array functions and transpiles them into array-returning functions in the generated code.

What Are Dynamic Arrays?¶

In traditional Excel, a formula in one cell produces one value. With dynamic arrays, a formula can return a range of values. In Excel, these values "spill" into neighbouring cells automatically.

For example, the formula =SORT(A1:A10) returns all 10 values sorted, spilling down from the cell containing the formula.

How Codcel Handles Dynamic Arrays¶

When Codcel transpiles a dynamic array formula, the generated function returns an array (or 2D array) instead of a single value. The exact representation depends on the output target:

Rust: Vec<Value> or Vec<Vec<Value>>
Java/Kotlin: List<Object> or List<List<Object>>
Python: list or list[list]
C#: List<object> or List<List<object>>
JSON (REST API): JSON arrays

If a dynamic array result is used as an input to another formula, Codcel handles the reference correctly -- just as Excel's spill references work.

Annotating Array Outputs¶

To mark a dynamic array result as an output, use the standard *O* annotation on the cell containing the array formula. The generated code will return the full array.

To mark an array as an input, use *I*, *ID*, or *IX* annotations. The generated code will accept an array parameter.

Supported Dynamic Array Functions¶

Codcel supports all standard Excel 365 dynamic array functions:

Array Generation¶

Function	Description
SEQUENCE	Generate a sequence of numbers
RANDARRAY	Generate an array of random numbers

Sorting and Filtering¶

Function	Description
SORT	Sort a range
SORTBY	Sort a range by another range
FILTER	Filter rows by condition
UNIQUE	Extract unique values

Reshaping¶

Function	Description
CHOOSECOLS	Select specific columns
CHOOSEROWS	Select specific rows
DROP	Remove rows or columns from the start/end
TAKE	Take rows or columns from the start/end
EXPAND	Expand an array to specified dimensions

Stacking and Wrapping¶

Function	Description
HSTACK	Stack arrays horizontally
VSTACK	Stack arrays vertically
TOCOL	Convert array to a single column
TOROW	Convert array to a single row
WRAPCOLS	Wrap a row into columns
WRAPROWS	Wrap a row into rows
TRANSPOSE	Transpose rows and columns

Limitations¶

Spill references (A1# syntax) are supported when the spill source is a known dynamic array formula
Implicit intersection (@ operator) behaviour may differ from Excel in edge cases -- see Differences
Dynamic arrays in table columns are not supported -- tables use fixed schemas