4. Excel Tables and dynamic ranges

CAE result workbooks rarely stay static. A single solver run may produce dozens of load cases, each with hundreds of probe points, and the workbook is expected to absorb new runs, new geometry variants, and new sensor channels without breaking every formula that references the data. The most common failure mode in engineering spreadsheets is a formula range that was correct on the day it was written but silently excludes rows added later. Excel Tables (created with Ctrl+T or Insert > Table) solve this structurally: a table is a named object whose boundaries expand automatically when you type in the row directly beneath it, and whose columns can be referenced by name rather than by cell address.

This chapter treats Tables as the backbone of a CAE result workbook: how structured references behave, how table styles support readability without corrupting the data, and how to design a results sheet that survives repeated solver iterations.

From A1 ranges to structured references

A conventional formula such as =AVERAGE(D2:D401) encodes two fragile assumptions: that the data starts at row 2 and that it ends at row 401. When the next solver run appends 40 more probe points, the average silently ignores them. Converting the block to a Table named tblProbe changes the formula to =AVERAGE(tblProbe[VonMises_MPa]), which resolves to the full column regardless of how many rows it contains. The reference is self-documenting: a reviewer can see that the average is taken over von Mises stress in MPa without inspecting cell addresses.

Structured references follow a small, learnable grammar. The table name comes first, then the column name in square brackets. Special item specifiers address parts of the table:

The #This Row specifier is the one engineers underuse most. In a results table with columns Yield_MPa and VonMises_MPa, a safety-factor column can be written once as =[@[Yield_MPa]]/[@[VonMises_MPa]] and it fills down automatically, including into rows added by future solver runs. The @ shorthand is Excel's compact form of #This Row and is what you will see when you click cells while composing a formula inside a table.

Designing the results table schema

Before applying any formatting, decide the column schema. A workable convention for a probe-point results table is: one row per probe point per load case, with explicit identifier columns rather than encoded names. For example:

RunID | LoadCase | ProbeID | X_mm | Y_mm | Z_mm | VonMises_MPa | MaxPrincipal_MPa | DispMag_mm | Yield_MPa | SafetyFactor

Keeping RunID and LoadCase as separate columns rather than concatenating them into a label makes filtering, pivot tables, and SUMIFS straightforward. Units belong in the header (_MPa, _mm) so that no one has to guess whether a displacement column is in millimetres or metres. The SafetyFactor column is a calculated column: type the formula once in the first data row and the table propagates it to every row, and to new rows appended later.

One caution specific to CAE workflows: do not place merged cells, blank separator rows, or subtotal rows inside a table. Tables assume a rectangular, contiguous block. Subtotals belong in the Totals row or in a separate summary sheet driven by SUBTOTAL or pivot tables. If a solver export arrives with blank rows between load cases, clean it first (filter for blanks, delete rows) before converting to a table.

Table styles and readability

Table styles apply banded fill, header formatting, and border rules as a single named style rather than as manual cell formatting. This matters for two reasons. First, manual fills are lost or misaligned when rows are inserted; a table style re-bands automatically. Second, conditional formatting layered on top of a table style remains legible because the style's banding is a background layer, not a hard-coded colour.

A practical style configuration for engineering review:

• Enable Header Row and Banded Rows; disable Banded Columns, which fights with wide numeric tables.
• Use a light neutral band (for example, a pale blue-grey) so that conditional formatting for stress exceedance remains visually dominant.
• Enable the Totals Row and set it to Max for stress columns and Min for safety factor — the two numbers a reviewer looks for first.
• Freeze the header row via View > Freeze Panes; the table header alone does not stay visible when scrolling.

Conditional formatting should encode engineering judgement, not decoration. A common rule set on SafetyFactor: red fill below 1.0, amber between 1.0 and 1.5, no fill above 1.5. Because the rule references [@[SafetyFactor]] or the column range, it extends to appended rows automatically — provided the rule was applied to the table column rather than to a fixed A1 range.

Tables as the source for dynamic charts and summaries

Charts built on table columns are dynamic by construction: adding rows extends the plotted series without editing the chart's data range. For a stress-versus-load-case chart, select the LoadCase and VonMises_MPa columns and insert the chart; Excel records the series as =SERIES(,...,tblProbe[LoadCase],tblProbe[VonMises_MPa],...). New load cases appear as new categories on refresh.

Summary sheets should likewise reference the table, not copy values. A dashboard cell computing the worst safety factor across all runs can use =MIN(tblProbe[SafetyFactor]), and a count of exceedances can use =COUNTIF(tblProbe[SafetyFactor],"<1"). Both update when the next solver run is pasted beneath the last row and the table expands.

Verification and common failure symptoms

After converting a results block to a table, verify the dynamic behaviour deliberately rather than assuming it. A short acceptance check:

1. Type a dummy value in the row immediately below the last data row. The table border should extend to include it, and the calculated SafetyFactor cell should populate. Delete the dummy row afterwards.
2. Check that the Totals row shows the expected Max and Min; if it shows a count instead, the totals function was set on the wrong column.
3. Confirm the chart's series formula in Select Data shows table names, not $D$2:$D$401.
4. Confirm conditional formatting rules were applied to the column range and not to a fixed block.

Typical symptoms and their causes: a #REF! error after deleting a column usually means a structured reference pointed at that column by name — restore the column or update the formula. A #NAME? error often indicates a table name that was changed or a reference typed with a space where an underscore belongs. A summary that stops updating after a paste usually means the pasted rows landed outside the table boundary (for example, after a blank row), so the table never expanded; delete the blank row and re-paste directly beneath the last data row. Finally, if a formula returns the header text instead of a number, the [[#Headers],...] specifier was used where a data reference was intended.

With the table in place, the workbook becomes a stable contract: solver exports are appended, calculated columns and conditional formatting extend themselves, and summary cells and charts read from named columns. The next layer of automation — pivot-based roll-ups and report generation — can then be built on a data structure that will not shift underneath it.