4. Structuring CAE results for pivot analysis

A pivot table is only as good as the table it reads. In CAE post-processing, the most common reason a pivot analysis stalls is not a missing formula or a wrong aggregation setting — it is a source range that was never shaped for pivoting in the first place. Simulation output typically arrives as a wide, hierarchical, multi-sheet artifact: one worksheet per load case, one column per probe location, a merged header block at the top, and units buried in cell text. Pivot tables require the opposite: a single flat table where every row is one observation, every column is one field, and every field has exactly one data type. This chapter covers how to convert solver output into that shape, how to anchor it with an Excel Table so it survives repeated runs, and how to keep the data trustworthy when the model is re-meshed and re-solved dozens of times.

The canonical row: one observation, all context

The target layout is a long (tidy) table. Each row should represent a single scalar result at a single location for a single run, and it should carry every attribute needed to slice it later. For a structural study, a well-formed row looks like this:

RunID | LoadCase | Variant | Part | ProbeID | Metric | Value | Unit | MeshSize_mm | SolveTime_s
R-014 | LC02_Brake | Baseline | Knuckle | P-07 | MaxVonMises | 312.4 | MPa | 4.0 | 186
R-014 | LC02_Brake | Baseline | Knuckle | P-07 | MaxPrincipal | 298.1 | MPa | 4.0 | 186
R-014 | LC02_Brake | Thickness+1 | Knuckle | P-07 | MaxVonMises | 287.9 | MPa | 4.0 | 191

Note what this structure buys you. Because Metric is a field rather than a column name, a single pivot can compare von Mises against principal stress by dragging Metric to Columns. Because RunID and Variant are separate fields, you can filter to one variant and still aggregate across runs. Because Unit is explicit, you can catch a mixed-unit contamination before it silently averages MPa with GPa. The wide alternative — one column per probe, one sheet per load case — forces you to rebuild the pivot every time a probe is added, and it makes cross-run comparison nearly impossible.

Two layout rules matter more than any other. First, never merge cells and never leave a header row blank. A merged header spanning three columns will be read by Excel as a single field with nulls beneath it, and the pivot cache will quietly drop the affected records. Second, keep one header row only. If the solver export includes a title block, units row, and a second descriptive row, delete or relocate them before defining the table; the header must be the first row of the range.

Anchoring with Excel Tables (ListObjects)

Convert the flat range into a real Excel Table with Ctrl+T (or Insert > Table), then give it a meaningful name in Table Design > Table Name, for example tblResults. This is not cosmetic. A defined Table gives you four concrete benefits for repeated runs:

• Auto-expansion. When you paste the next run's rows directly beneath the last row, the Table grows automatically and any pivot built on tblResults picks up the new records on Refresh — no range editing.
• Structured references. Calculated columns such as =[@Value]/[@Reference] propagate to every new row, so derived metrics like safety factor stay consistent across runs.
• Stable field names. Pivot field names bind to the header text; renaming a column in the Table updates the pivot field list on refresh rather than orphaning it.
• Integrity hooks. Data validation and conditional formatting applied to Table columns extend automatically to appended rows.

Build the pivot with Insert > PivotTable and point the source at the table name, not a cell range. If you must use a range, use a dynamic named range based on COUNTA, but the Table approach is simpler and less fragile. After the pivot is created, set the pivot cache to refresh on open (PivotTable Options > Data > Refresh data when opening the file) so a colleague opening the workbook sees current numbers without manual intervention.

Enforcing data integrity across runs

Repeated runs introduce three failure modes that a pivot will not warn you about: duplicated records, type drift, and orphaned keys. Address them at the source table, before the pivot ever sees the data.

Duplicates. A re-exported run pasted twice doubles every aggregate. Add a composite key column, for example =[@RunID]&"|"&[@LoadCase]&"|"&[@ProbeID]&"|"&[@Metric], and apply a conditional format rule using COUNTIF([CompositeKey],[@CompositeKey])>1 to flag collisions in red. For a hard stop, use Data > Data Validation with a custom formula =COUNTIF($K$2:$K$100000,K2)=1 on the key column.

Type drift. A single text value such as "n/a" or "<0.5" in the Value column converts the whole column to text, and the pivot then counts records instead of summing values — a symptom that looks like a wrong aggregation setting but is actually a data-type problem. Guard against it by keeping Value strictly numeric and pushing qualifiers into a separate Qualifier field. Verify with =COUNT([@Value]) against =COUNTA([@Value]); if they differ, non-numeric entries exist.

Orphaned keys. When a probe is renamed or a part is re-meshed, old ProbeID values may no longer map to the current geometry. Maintain a small lookup table of valid IDs and use a validation list on the ProbeID column so new rows can only reference known probes. This keeps pivot rows from silently aggregating results that belong to a retired configuration.

Run metadata as first-class fields

The most valuable pivot fields in a CAE study are often not the results at all — they are the run attributes. Carry RunID, SolverVersion, MeshSize_mm, ElementType, SolveTime_s, and a RunDate on every row. This lets you pivot convergence behavior (max stress versus mesh size), compare solver versions, or track solve time against model complexity without a separate join. Keep the metadata columns to the left of the result columns by convention; it makes the table readable and keeps the pivot field list ordered sensibly.

The diagram below summarizes the transformation from solver output to a pivot-ready table, and the integrity checks that sit between them.

A repeatable intake procedure

The following sequence is what a working CAE analyst actually does when a new batch of runs lands. It is short enough to run every time and specific enough to catch the failures described above.

1. Export each load case to CSV from the post-processor, one file per run, with probe IDs and metric names in the header.
2. Append the files into a single staging sheet using Power Query (Data > Get Data > From File > From Folder), which preserves column types and records the source file name — useful as a SourceFile field.
3. Unpivot the probe columns into ProbeID / Value pairs (Power Query: select probe columns > Transform > Unpivot Columns). This is the step that converts wide to long.
4. Add metadata columns: RunID, Variant, MeshSize_mm, SolverVersion, RunDate.
5. Load to a worksheet as an Excel Table named tblResults; do not load to the Data Model unless you specifically need DAX measures.
6. Run the three integrity checks — duplicate composite key, COUNT versus COUNTA on Value, and ProbeID validation — and resolve any flags before refreshing the pivot.
7. Refresh the pivot and confirm the record count in the pivot status bar matches the row count of tblResults minus any filtered rows.

Step 7 is the verification that closes the loop: if the pivot reports 1,842 records and the table holds 1,842 data rows, the cache is current and no rows were dropped by a type or merge problem. If the numbers disagree, the discrepancy is almost always a text value in Value or a blank header cell — both of which are visible in the staging sheet before the pivot is ever touched.

Treat the flat table as the durable artifact and the pivot as a disposable view on top of it. When the model is re-meshed next month, you append rows, run the checks, and refresh; the pivot definition, field layout, and filters survive untouched. That separation — stable, validated, long-format data underneath, flexible aggregation above — is what makes pivot-based statistical analysis of CAE results repeatable rather than a one-off exercise.