DocumentationFormulasSpilled ranges

Dynamic Arrays and Spilled Ranges

A formula that returns an array spills its result into the neighbouring cells, as in Excel and Google Sheets. The cell holding the formula is the anchor, and the cells filled by the result are the spilled range. Version 13 adds the spilled range operator, B2#, which references the whole array spilled from an anchor, and keeps that reference correct through copy, fill and structural changes.

What's new with Version 13

  • Spilled range operator: B2# references the array spilled from the anchor cell B2
  • Dependency chain: formulas holding a spilled reference recalculate when the spilled values change
  • Translation: the reference follows copy and paste, fill and structural changes; deleting the anchor leaves #REF!#, the operator applied to the error, exactly as Excel reads it
  • Formula editor: F4 cycles the anchor reference and the editor highlights the anchor cell
  • Intersection operator: =B2:B4 A3:C3 returns the cells two references have in common

Evaluation requires a formula engine with spilled range support, such as Formula Pro.

Documentation

The spilled range operator

Expression Meaning
=B2# The whole array spilled from B2, whatever its current size.
=SUM(B2#) Aggregates over the spilled range; the result follows the size of the spill.
=INDEX(B2#, 3) The third item of the spilled range.
#REF!# The operator applied to an error: the anchor was deleted, so the reference no longer resolves.

The reference resolves at evaluation time, so it always covers the current spill, even when the array grows or shrinks because its inputs changed.

Behaviour

Situation Result
Spilled values change Every formula holding a reference to the spill recalculates, through the dependency chain.
Copy and paste, fill The anchor reference shifts like a normal reference and keeps the operator: =SUM(B2#) copied one column right becomes =SUM(C2#).
Insert, delete, move Structural changes shift the anchor and keep the operator. Deleting the anchor leaves #REF!#.
Removing the array formula The spilled cells are cleared; undo restores the formula and the spill, and redo clears them again without leaving a ghost spill.
Formula editor F4 cycles the anchor through the absolute forms, and the editor highlights the anchor cell of the reference.

The intersection operator

A space between two reference-like tokens is the Excel intersection operator: =B2:B4 A3:C3 returns the cells the two ranges have in common, B3 in this case. References, ranges, full rows and columns, defined names, calls that return references such as INDIRECT, structured references, spilled ranges and error literals all qualify. Any other whitespace in a formula is still dropped, and runs collapse to a single space.

Examples

A spilled range and a reference to it

SEQUENCE spills five numbers from A1, and the formulas in column C reference the whole spill. Change the count in B1 to see the spill and every dependent formula follow.

<html>
<script src="https://jspreadsheet.com/v13/jspreadsheet.js"></script>
<script src="https://jsuites.net/v6/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v13/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro@6/dist/index.min.js"></script>

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<div id="spreadsheet"></div>

<script>
// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('NmY1Yjc3NWUwOGMwYzVhZjk3MDYwYWUwZTQzN2ZlODI5YjUzNmVjNmI0MTM5OWM4YTU1N2MyYjE1ZGJlNGI5OWE2YWU4NWEzMmE2ODU5N2M4MWNiMGNiOTA1Y2VmMWM4OGEzMjgzZjg3ZmUyM2UwMTE3YmNhZTAxNGIyMDdmZDMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpnNE9ESTNOekEwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9');

// Formula Pro is required for dynamic arrays
jspreadsheet.setExtensions({ formula });

// Create the spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            ['=SEQUENCE(B1)', 5, '=SUM(A1#)'],
            ['', '', '=COUNT(A1#)'],
            ['', '', '=INDEX(A1#, 3)'],
        ],
        columns: [
            { title: 'Spill', width: 100 },
            { title: 'Count', width: 100, type: 'number' },
            { title: 'Reference', width: 140 },
        ],
        minDimensions: [3, 8],
    }],
});
</script>
</html>

See Also