3. Excel Table fundamentals for CAE data

Every pivot table in Excel is built on top of a source range. In CAE post-processing, that source is rarely a clean, fixed block of cells — it is a growing export of node results, element stresses, load-step histories, or modal participation factors that changes every time the solver finishes a new run. The single most consequential decision you make before inserting a pivot table is therefore how that source is defined. If it is a plain cell range such as $A$1:$H$4200, the pivot cache is frozen to those exact coordinates. Add a new load case, append a second solver run, or paste in an extra 300 rows of probe data, and the pivot table silently ignores them. Converting the source to an Excel Table solves this class of problem structurally, and structured references make the formulas that feed the table readable and robust.

Why a plain range fails for CAE result sets

A CAE result export typically has a stable column schema — for example RunID, LoadCase, Part, NodeID, X, Y, Z, VonMises_MPa, Disp_Mag_mm — but a row count that grows with every study. When you build a pivot table on a fixed range, three failure modes appear in practice:

• Silent truncation. New rows appended below the original last row are outside the cache. The pivot totals look plausible but are wrong, and nothing in the UI warns you.
• Manual re-pointing. You must open PivotTable Analyze → Change Data Source and re-select the range after every export. This is error-prone and easy to forget before a report is circulated.
• Fragile formulas. Helper columns written as =H2/G2 and filled down break when rows are inserted, and they do not travel with the data when the table is sorted.

An Excel Table (created with Ctrl+T or Insert → Table) removes all three. The table object has a name, a defined column set, and a row extent that expands automatically when you type or paste into the row immediately below it, or when a Power Query load appends rows to it.

Converting the export to a Table — concrete procedure

Assume a solver export has been pasted into Sheet1 starting at A1, with headers in row 1 and 4,199 data rows. The steps and the observable checkpoints are:

1. Select any single cell inside the data block and press Ctrl+T. Confirm that My table has headers is checked, because row 1 contains field names, not data.
2. On the Table Design tab, rename the table from the default Table1 to something meaningful, e.g. tblResults. This name becomes the anchor for every structured reference you write.
3. Verify the header row is text and unique. Duplicate headers (two columns both called Stress) will be silently renamed to Stress2 by Excel, which then breaks any formula that referenced the original name.
4. Confirm the data types: numeric columns should be right-aligned by default. A VonMises_MPa column that is left-aligned is almost always text — typically caused by a thousands separator, a unit suffix such as "312.4 MPa", or a non-breaking space from the export. Clean these before building the pivot, because text values are counted, not summed.

Structured references in practice

Once the source is a table, formulas address columns by name rather than by letter. The syntax is TableName[ColumnName], with special item specifiers for the header, the data body, and the current row:

A practical derived column: suppose the yield strength for the part material is stored in a named cell SigmaYield (e.g. 355 MPa for S355 steel). Add a column SafetyFactor and enter in the first data row:

=IFERROR(SigmaYield / tblResults[[#This Row],[VonMises_MPa]], "")

Excel fills the formula down the entire column automatically, and it stays correct when rows are added, sorted, or filtered. Compare this with the range-based equivalent =SigmaYield/H2, which must be dragged and which silently points at the wrong row after a sort. The structured version also survives column insertion: if a new Temperature_C column is inserted before VonMises_MPa, the formula still resolves to the correct field.

Ensuring the pivot cache actually refreshes

Building a pivot on tblResults is necessary but not sufficient. The pivot cache is a snapshot; it updates only when you refresh. Three settings determine whether refresh happens reliably:

• Refresh on open. In PivotTable Options → Data, enable Refresh data when opening the file. This guarantees that a colleague who opens the workbook sees current numbers, not last week's.
• Refresh after external load. If the table is populated by Power Query, the query's properties include Refresh data when opening the file and Refresh this connection on Refresh All. Enable both so that Ctrl+Alt+F5 (Refresh All) rebuilds the query, expands the table, and refreshes every dependent pivot in one action.
• Preserve sort and filter. Under Data options, keep Save source data with file enabled if the workbook is shared, but be aware this inflates file size for large CAE exports.

The verification step is simple and should be done once per template: note the current row count of tblResults (visible in the Name Box when the table is selected, or via =ROWS(tblResults)), append a test row with a distinctive RunID such as TEST_APPEND, press Refresh All, and confirm that the pivot's grand total increases by exactly one record and that TEST_APPEND appears in the row field. If it does not, the pivot is still bound to a fixed range — check Change Data Source and confirm it reads tblResults rather than Sheet1!$A$1:$I$4200.

Practical guardrails for CAE workbooks

Three habits keep the table-to-pivot chain reliable over a project's lifetime. First, keep exactly one table per result set and give it a name that encodes the study, such as tblModalR3 or tblStaticLC1toLC8; unnamed Table1, Table2 objects become indistinguishable after a few weeks. Second, never place a second table directly adjacent to the first — Excel merges them on expansion. Leave at least one empty column and row between tables. Third, if the export contains a RunID or timestamp column, keep it in the table even if the pivot does not use it; it is the field that lets you filter the pivot to a single solver run and is the fastest way to confirm that a refresh actually pulled the run you expected.

With the source converted, named, and refresh-verified, the pivot table becomes a stable analytical layer rather than a fragile snapshot. The next step — placing fields on rows, columns, and values to produce stress summaries by part and load case — depends entirely on this foundation being correct.