Dynamic Arrays, Spill, and LET: What Changed in Excel and Why It Matters for Java Applications

In a previous article, Working with Spreadsheets in Java: A Practical Overview, we walked through the common scenarios where Java applications need to interact with spreadsheets and the categories of tools available for the job. One of the factors mentioned there was support for modern Excel formulas — a topic that deserves more space than a single bullet point.

Java applications interact with Excel more often than most teams plan for: file uploads from finance, calculation logic authored in a workbook, reporting exports back to business users. The files these users produce today are not the same as the files they produced five years ago. Excel 365 and Excel 2021 introduced a new formula model, and workbooks authored in those versions routinely use it. Depending on which library you use, those formulas may evaluate correctly, fail silently with stale cached values, or throw exceptions at recalculation time.

This article goes deeper on that topic: what dynamic arrays and spill behavior are, what the new function set looks like, why supporting them is technically difficult, and what Java developers should look for when evaluating whether a library handles them correctly.

Why You're Seeing Them in Real-World Workbooks

Dynamic arrays spread rapidly because they eliminate many of the helper columns, copied formulas, and Ctrl+Shift+Enter array formulas that older Excel workbooks depended on. Workbooks become shorter, easier to audit, and easier to maintain. As organizations migrate to Microsoft 365, these newer formulas increasingly appear in spreadsheets exchanged with Java applications, even when the application itself hasn't changed.

What Changed: One Formula, Many Values

Before dynamic arrays, formulas that returned multiple values generally required a pre-sized array range and legacy array-formula syntax. Dynamic arrays changed this by allowing a single formula to return a variable-sized array and automatically spill into neighboring cells. For example: =UNIQUE(A1:A6)

Entered in one cell, this returns the full list of distinct values from A1:A6. The result fills as many cells as there are distinct values. If the source data changes and the number of unique values changes, the spill range automatically grows or shrinks.

This is not just a new function. It is a change in the evaluation model itself.

The Mechanics of Spill

A spilled formula produces a region of cells with specific roles:

• The anchor cell is the one cell that contains the formula. It "owns" the result.
• The spilled cells are the neighboring cells that display the additional values. They do not contain formulas of their own; they mirror slices of the anchor's result.

You can reference the entire spilled range from another formula using the # operator. The # reference is not a fixed cell range such as A1:A6; it refers to whatever range the anchor currently spills into. If A1 contains =UNIQUE(...) and the result spills into A1:A6, then =COUNTA(A1#) counts the values in the entire spilled range. If the spill range grows or shrinks, the # reference adjusts automatically. 

#SPILL! errors. If the range a formula needs to spill into is blocked by an existing value, a merged region, or an Excel Table, the formula cannot spill, and the anchor cell shows #SPILL! instead of a result. Clearing the obstruction allows the formula to complete.

Implicit intersection with @. Older Excel silently reduced arrays to single values in many contexts. Modern Excel returns the full array unless the formula uses the @ prefix. For example, =A1:A10 entered in a cell in modern Excel spills the values from A1:A10, while =@A1:A10 applies implicit intersection and returns the value corresponding to the formula's row.  Files migrated from older Excel versions often contain automatically inserted @ prefixes to preserve their original behavior.

The New Function Family

The modern functions commonly associated with Excel's dynamic-array model can be grouped into three broad categories. It is worth understanding the grouping because the groups behave differently.

Group 1: Language Features

These are not really functions in the traditional sense. They add expression-level constructs to Excel's formula language.

• LET binds names to intermediate values inside a formula, so you can write =LET(total, SUM(B2:B100), tax, total*0.1, total+tax) instead of repeating SUM(B2:B100) three times.
• LAMBDA defines a reusable function inside a workbook. Combined with named ranges, LAMBDA effectively adds user-defined functions without VBA.
• ISOMITTED is used inside LAMBDA to detect whether an optional argument was supplied.

These functions do not inherently produce a spilled array. LET returns the result of its calculation, which may itself be an array. 

Group 2: Dynamic Array Functions

These are the functions people usually mean when they talk about "the new Excel functions." These functions are designed to return arrays, and when their results contain multiple values, Excel can spill those results into neighboring cells. 

• UNIQUE returns distinct values from a range.
• SORT and SORTBY return sorted arrays.
• FILTER returns rows that match a condition.
• SEQUENCE generates a sequence of numbers.
• RANDARRAY generates an array of random numbers.

Array-shaping functions form a subset of this group. They take arrays as input and return reshaped arrays: CHOOSECOLS, CHOOSEROWS, DROP, EXPAND, HSTACK, VSTACK, TAKE, TOCOL, TOROW, WRAPCOLS, WRAPROWS. TEXTSPLIT also fits here — it splits a string into an array.

Other functions, including BYROW, BYCOL, MAP, REDUCE, and SCAN, build on the same dynamic array model.

Group 3: Scalar Functions Added in the Same Era

Dynamic arrays are primarily an evaluation model; modern functions are a collection of functions that take advantage of, or coexist with, that model. Group 3 functions were introduced as part of the broader set of modern Excel functions, but they are not themselves primarily array-producing functions.

• XLOOKUP and XMATCH are modern replacements for VLOOKUP and MATCH. They normally return a single value, though they can return an array when passed an array of lookup values.
• TEXTAFTER and TEXTBEFORE return substrings.
• VALUETOTEXT and ARRAYTOTEXT convert values to text (ARRAYTOTEXT takes an array as input but returns a single string).

These are often lumped in with dynamic array functions because they arrived together, but their evaluation model is closer to VLOOKUP than to UNIQUE.

Why This Is Hard for a Formula Engine

Supporting these features is not just a matter of adding new function names to a list. The dynamic array model requires substantial changes to the evaluation engine itself.

A traditional one-cell-at-a-time formula model is not sufficient to implement dynamic arrays. An engine must be able to represent a formula whose result has a variable shape and propagate that result across multiple cells.

A modern engine has to handle four additional concerns:

Array-shaped results. A formula's return value may be a 2D array whose dimensions depend on the input data. =UNIQUE(A1:A100) returns a different number of rows depending on how many unique values the range contains. The engine must determine the result shape at evaluation time, not at parse time.

Spill range tracking. The engine must reserve the cells the formula spills into and prevent other content from occupying them. When something occupies a spill target, the anchor must return #SPILL! rather than overwrite the obstruction. The reserved region must also update when the shape of the result changes.

Downstream references. Expressions like A1# refer to the entire spilled range. When the shape of the anchor formula changes, every downstream reference must be re-evaluated with the new dimensions. This makes the dependency graph more dynamic than in a one-value-per-cell model.

Implicit intersection compatibility. Older Excel silently collapsed arrays to single values in many contexts. Modern Excel returns the whole array. When files authored in older Excel are opened in modern Excel, @ prefixes are inserted automatically to preserve original behavior. An engine that reads modern .xlsx files needs to honor the @ operator, or the imported formulas will produce different results.

Adding these behaviors to an engine designed around the one-formula-one-value model is a substantial rewrite, not an incremental feature addition. This is part of why support across the Java ecosystem has been uneven.

What Java Developers Should Check

Support for these capabilities varies significantly across Java spreadsheet libraries. Some engines were originally designed around traditional one-cell-one-result evaluation and only implement subsets of the modern Excel model. Others have extended or redesigned their evaluators to support dynamic arrays. Rather than relying on feature lists, it is worth validating behavior against workbooks representative of your own application.

If your application needs to evaluate modern Excel formulas, the following checks are worth running before committing to a library.

Test with a file containing a spilled formula. Create a small .xlsx with =UNIQUE(A1:A100) or =SORT(A1:A100) in a cell. Load it in your candidate library and try to recalculate the anchor cell. A library that supports dynamic arrays will return the array; one that does not will typically throw an exception or return only the first value.

Check for the # spill operator. In the same file, add another cell containing =COUNTA(A1#) where A1 is the anchor. This tests whether the library understands spilled range references, which is a separate capability from evaluating the anchor formula itself.

Test the @ operator. Add =@A1:A10 in a cell and check whether the library correctly returns the value at the current row rather than the full array. Files migrated from older Excel routinely contain @ prefixes; a library that doesn't handle them will produce different results than Excel.

Test with LET and LAMBDA. Write a formula like =LET(total, SUM(A1:A100), total * 1.1) and check both evaluation and .xlsx round-trip. Test LET and LAMBDA independently. Parsing, preserving, and evaluating these functions are separate capabilities, so a library that can read or write the formula text may not necessarily be able to evaluate it correctly. 

Test round-trip. Save the workbook, reopen it in Excel, and check that the formulas still produce correct results. Some engines strip modern constructs on save.

Check what happens on failure. When a library encounters a function it does not implement, does it raise an exception, return an error value, or silently fall back to the cached value from the file? Silent fallback is the most dangerous behavior because it masks the problem during development and only fails in production when the data changes.

Conclusion

Excel's formula language has changed more in the last few years than in the two decades before it. Dynamic arrays, spill behavior, and the new function set are not experimental — they are standard in Excel 365 and Excel 2021, and they show up in workbooks that Java applications routinely have to process.

For Java developers, the practical implication is that "Excel formula support" is no longer a single property that a library either has or doesn't have. There are several distinct capabilities involved, and libraries vary widely on each.

As covered in the previous article, the Java spreadsheet landscape spans open source libraries such as Apache POI, commercial headless engines, and embedded spreadsheet components like Keikai. Whichever category fits your use case, the checks above are a reasonable way to verify that a candidate library handles modern Excel behavior against the workbooks your real users produce.