2. Dynamic array fundamentals for CAE

CAE post-processing rarely produces a single tidy table. A typical solver export contains thousands of node rows and element rows, each carrying IDs, coordinates, connectivity, and result quantities such as displacement magnitude, von Mises stress, or plastic strain. Before any engineering judgment can be made, that raw block must be sliced, ranked, deduplicated, and re-indexed. In modern Excel (Microsoft 365 and Excel 2021+), four dynamic array functions — FILTER, SORT, UNIQUE, and SEQUENCE — turn these operations into single-cell formulas that spill results automatically. This chapter establishes how those functions behave on mesh data and how to combine them into a reusable extraction layer.

Why Spilling Changes the Workflow

A dynamic array formula is entered in one cell but returns a rectangular block. The cell containing the formula is the anchor; the returned block is the spill range. If any cell in the intended spill range is occupied, Excel raises #SPILL! and nothing is returned. This single constraint drives most practical layout decisions: keep a clear region to the right of and below every anchor, and never place annotations inside a potential spill zone.

Two reference operators matter constantly. The range operator (colon) and the intersection operator (space) behave differently against spilled arrays, and the spill operator (#) refers to the entire spilled block. If F2 holds =FILTER(...) returning 240 rows, then F2# is the whole 240-row result, and ROWS(F2#) returns 240. Use F2# in downstream formulas so the extraction layer stays self-sizing when the underlying result set changes.

SEQUENCE: Generating Node and Element Index Sets

SEQUENCE produces an arithmetic series as an array. Its signature is SEQUENCE(rows, [columns], [start], [step]). In CAE work it is most useful for building index vectors that other functions consume, rather than for display.

=SEQUENCE(1, 10)                  → 1,2,3,...,10  (horizontal)
=SEQUENCE(10, 1, 101, 1)           → 101,102,...,110  (vertical, custom start)
=SEQUENCE(5, 1, 0, 0.25)           → 0, 0.25, 0.5, 0.75, 1  (normalized load steps)

A frequent pattern is pairing SEQUENCE with INDEX to pull every n-th row of a large export, for example sampling every 50th node from a 20,000-row table to build a lightweight scatter plot. Another is generating a normalized time or load-step axis for charting transient results, where the solver wrote only step numbers and you need a 0–1 abscissa.

FILTER: Condition-Based Extraction

FILTER(array, include, [if_empty]) returns only the rows (or columns) where include evaluates to TRUE. The include argument must be a Boolean array whose height matches the array's rows. For a node table in A2:D20001 with columns NodeID, X, Y, Z, extracting nodes inside a bounding box is a direct application:

=FILTER(A2:D20001,
        (B2:B20001>=0)*(B2:B20001<=120)*
        (C2:C20001>=-40)*(C2:C20001<=40),
        "No nodes in window")

Multiplication acts as logical AND; addition acts as OR. Parentheses are mandatory around each comparison because * binds tighter than the comparison operators in some contexts and because readability collapses otherwise. The third argument prevents #CALC! when the window is empty — a real scenario when a probe region falls outside the mesh.

For element result sets, the same pattern filters by material, part, or result threshold. Given an element table with ElemID, Part, MatID, vonMises in F2:I50001, extracting all elements of a specific part exceeding a stress limit is:

=FILTER(F2:I50001,
        (G2:G50001="Bracket")*(I2:I50001>=235),
        "No yielding elements")

Note the threshold 235 here is an illustrative value in MPa for a steel bracket; substitute the actual yield strength from your material card. The result is a spill block whose row count equals the number of qualifying elements — a number you can read with ROWS() to get an immediate count of over-stressed elements without a separate COUNTIFS.

SORT: Ranking by Result Magnitude

SORT(array, [sort_index], [sort_order], [by_col]) reorders rows by a chosen column. sort_order 1 is ascending, -1 descending. To rank the filtered elements by stress, wrap the filter:

=SORT(FILTER(F2:I50001, (G2:G50001="Bracket")*(I2:I50001>=235)),
      4, -1)

The 4 refers to the fourth column of the filtered array, not of the original sheet range. This is the most common source of wrong results: sort indices are relative to the array being sorted. If the filtered array has four columns in the order ElemID, Part, MatID, vonMises, then column 4 is vonMises. Always count columns in the array expression, not in the source table.

For multi-key ranking, SORTBY is more expressive than SORT: SORTBY(array, by_array1, order1, by_array2, order2, ...). Sorting elements first by part name ascending, then by stress descending, is a single call. Use SORT for simple magnitude ranking and SORTBY when the report needs grouped ordering.

UNIQUE: Deduplicating Connectivity and Part Lists

UNIQUE(array, [by_col], [exactly_once]) returns distinct values. On CAE data its two highest-value uses are building part/material inventories and extracting node sets from element connectivity.

If element connectivity is stored as one node per column — ElemID, N1, N2, N3, N4 for a tet4 mesh — the set of all nodes referenced by any element is the union of the four node columns. Stacking them with VSTACK and deduplicating gives the active node set:

=SORT(UNIQUE(VSTACK(C2:C50001, D2:D50001, E2:E50001, F2:F50001)))

Comparing ROWS() of that result against the total node count in the model immediately reveals orphan nodes — nodes present in the mesh but referenced by no element. Orphans are usually harmless but occasionally indicate a meshing error or a deleted part that left nodes behind.

The exactly_once argument (TRUE) returns only values appearing exactly once. Applied to a node column, it lists nodes touched by a single element — the boundary of a single-element-thick region, or a symptom of disconnected mesh patches.

Composing the Four Functions

The real power appears when the functions nest. A typical CAE extraction layer looks like this: SEQUENCE builds an index or axis, FILTER narrows the result set by region or threshold, SORT ranks it, and UNIQUE collapses duplicates for inventory or set operations. The diagram below shows the data flow and the shape transformation at each stage.

Practical Pitfalls and Verification

#SPILL! errors almost always mean a non-empty cell sits in the spill zone. Select the anchor, use Formulas → Error Checking → Select Obstructing Cells to locate the blocker. Merged cells are a common culprit and must be unmerged.

#CALC! errors from FILTER mean the include array returned no TRUE values and no if_empty was supplied. Always provide the third argument in production sheets.

#VALUE! from mismatched dimensions occurs when the include array height differs from the array height — typically because one range was written as B2:B20001 and another as B2:B20000. Verify with ROWS() on each range before nesting.

Sort index confusion is the silent failure mode: the formula returns data, but ranked by the wrong column. Cross-check by reading the first row of the sorted output and confirming it matches the expected extreme value from a MAX or MIN over the filtered set.

The four functions form a compact vocabulary: SEQUENCE generates, FILTER selects, SORT ranks, and UNIQUE collapses. Once these are fluent on mesh node and element tables, the same patterns transfer directly to load-step histories, contact pair lists, and material assignment tables — the recurring shapes of CAE data.