4. Excel tables and structured references

CAE post-processing almost always begins with a rectangular block of numbers exported from a solver: node IDs, element IDs, load steps, displacement components, von Mises stress, safety factors, and so on. The instinctive next step is to select that block and start writing formulas against fixed addresses such as D2:D4821. That works exactly once. The moment the solver writes a new result set with 4,905 rows instead of 4,821, every hard-coded range silently truncates or overruns, and the report you hand to the design review contains a stress maximum computed over the wrong population of elements. Excel Tables and structured references exist precisely to eliminate this class of error, and for recurring CAE reporting they are not a convenience — they are the mechanism that makes a workbook survive re-meshing.

Why a plain range is the wrong container for solver output

A plain range carries no identity. Excel does not know that D2:D4821 is "the von Mises column"; it only knows it is a set of cells. Consequently, when you insert a row, sort the data, or paste a longer export beneath the existing block, formulas that reference the range do not adapt in a semantically meaningful way. A Table, by contrast, is a named object with a defined header row, a defined data body, and a defined set of columns. Excel tracks the table's extent as data is added or removed, and formulas written with structured references address columns by name rather than by coordinate.

For CAE work the practical consequences are concrete. If your solver export is appended to each time a new load case is run, a Table automatically absorbs the appended rows into the data body, and any summary formula referencing the table recalculates over the enlarged population. If a colleague inserts a derived column — say, a safety factor computed from yield stress and von Mises stress — the Table propagates the formula down the entire column automatically, including rows added later. Neither behaviour is available with a plain range without manual maintenance.

Converting an exported result block into a Table

The conversion itself is trivial, but the preconditions matter. Before converting, ensure the export has exactly one header row, no merged cells, no blank rows inside the data body, and no subtotal or note rows embedded in the block. Solver exporters frequently append a trailing line such as *** End of results ***; delete it first, because a Table will treat it as a data row and it will corrupt any numeric aggregation.

Select any cell inside the block and use Insert > Table (or Ctrl+T). Confirm that My table has headers is checked, then give the table a meaningful name in Table Design > Table Name. Naming discipline pays off later: use names such as tbl_StaticResults, tbl_ModalFreq, or tbl_FatigueLife rather than the default Table1. Table names must not contain spaces, must begin with a letter or underscore, and must be unique within the workbook. Rename the header cells to match the physical quantity and unit, for example NodeID, UZ_mm, vonMises_MPa, SafetyFactor. These header strings become the vocabulary of every structured formula you write afterwards, so choose them once and keep them stable across result sets.

Structured reference syntax in practice

Structured references replace A1-style coordinates with table and column names. The essential forms are worth memorising because they appear constantly in CAE summary sheets:

• tbl_StaticResults[vonMises_MPa] — the entire data body of one column, excluding the header.
• tbl_StaticResults[[#Headers],[vonMises_MPa]] — the header cell only.
• tbl_StaticResults[[#Totals],[vonMises_MPa]] — the totals row cell, if a totals row is enabled.
• tbl_StaticResults[[#All],[vonMises_MPa]] — header, data, and totals together.
• tbl_StaticResults[@[vonMises_MPa]] — the value in the current row of that column; the @ is the row-context operator.
• tbl_StaticResults[[NodeID]:[vonMises_MPa]] — a contiguous block spanning several columns.

A typical summary cell for peak stress becomes =MAX(tbl_StaticResults[vonMises_MPa]), and the corresponding location lookup becomes =INDEX(tbl_StaticResults[NodeID], MATCH(MAX(tbl_StaticResults[vonMises_MPa]), tbl_StaticResults[vonMises_MPa], 0)). Both formulas remain correct when the next solver run produces a different number of elements, because the column reference resolves to whatever the table's current data body contains.

Inside a Table, calculated columns are where structured references shine. Suppose you add a column headed SafetyFactor and enter =[@[Yield_MPa]]/[@[vonMises_MPa]] in the first data cell. Excel fills the formula down the whole column and, critically, continues to fill it for any row appended later. This is the behaviour that makes a CAE results table self-maintaining across re-meshing: the derived quantities follow the data.

Dynamic named ranges for recurring analyses

Tables solve the in-sheet problem, but recurring CAE reporting often needs a name that can be consumed by charts, data validation lists, or formulas on a separate summary sheet. Two approaches are standard. The first is simply to define a name that points at the table column: in Formulas > Name Manager, create PeakStress with the definition =tbl_StaticResults[vonMises_MPa]. This name is inherently dynamic because the table column is dynamic.

The second approach uses OFFSET or INDEX with COUNTA to build a self-sizing range from a plain block. The INDEX variant is preferable because it is non-volatile and does not force recalculation of the whole workbook on every edit:

=Sheet1!$D$2:INDEX(Sheet1!$D:$D, COUNTA(Sheet1!$D:$D))

This defines a range from the first data cell to the last populated cell in column D, growing and shrinking with the data. It is the right tool when the source block cannot be converted to a Table — for example, when it is produced by a legacy macro that writes into a fixed region. Where a Table is available, prefer the table-column name; it is clearer, faster, and immune to stray values below the data body.

How the pieces fit together

The diagram below shows the relationship between the raw solver export, the Table object, the structured references that address it, and the downstream consumers — summary formulas, calculated columns, and named ranges feeding charts and reports. The key point is that every consumer addresses the Table by name, so a change in row count propagates automatically without editing any formula.

Verification and common failure modes

After converting a result block, verify the table's extent before trusting any summary. Click inside the table and check that the resize handle encloses exactly the data rows and no trailing note lines. Confirm the table name in the Name Box. Then test the dynamic behaviour deliberately: append a small block of dummy rows beneath the table, confirm that the table expands to include them, and confirm that your MAX formula's result changes accordingly. Delete the dummy rows afterwards. This two-minute test catches the most common failure — a table that was converted from a range that already contained a blank row, which splits the data body and causes the table to stop expanding at the gap.

Other frequent symptoms and their causes: a #NAME? error in a structured formula usually means the column header was renamed or contains a typo, since the reference resolves by exact header text. A #REF! error after deleting a column indicates a structured reference pointing at a column that no longer exists. A calculated column that stops filling on new rows typically means the formula was typed as a normal cell formula rather than entered inside the table, or that the table's auto-fill was disabled under File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type. Finally, if a chart series does not extend when the table grows, the series was defined against a fixed range rather than the table column name; redefine it as =Sheet1!tbl_StaticResults[vonMises_MPa] or point the chart at the named range.

Once these structures are in place, the recurring CAE reporting cycle reduces to a single action: paste or append the new solver export into the table, and every summary, derived column, chart, and named range updates against the correct population of elements. The workbook becomes a stable analysis instrument rather than a document that must be rebuilt each time the mesh changes.