3. Pivot table fundamentals for CAE

A CAE post-processing workflow usually produces a long, flat table rather than a compact report. A single transient run of a bracket model may export one row per node per time step, with columns such as LoadCase, Part, NodeID, Time_s, VonMises_MPa, DispX_mm, and DispY_mm. A modal run may instead export one row per mode per node, with Mode, Frequency_Hz, and ParticipationFactor. Pivot tables are the fastest way to turn this "one row per observation" structure into engineering answers: peak stress per part, displacement trend over time, or the worst load case for each component. The key is understanding how the four pivot areas — Rows, Columns, Values, and Filters — map onto simulation semantics.

From a flat results table to a pivot cache

Before inserting a pivot table, verify that the source range is a proper database: one header row, no merged cells, no blank rows inside the data, and consistent units per column. Give the range a defined name (for example tbl_Results) so the pivot source remains stable when rows are appended. A typical CAE export looks like this:

LoadCase  Part      NodeID  Time_s  VonMises_MPa  DispX_mm  DispY_mm
LC01      Bracket   1001    0.0     12.4          0.000     0.000
LC01      Bracket   1001    0.5     48.7          0.031     0.012
LC01      Bracket   1001    1.0     96.2          0.064     0.027
LC02      Bracket   1001    0.0     15.1          0.000     0.000
LC02      Bracket   1001    1.0     131.8         0.079     0.041
LC01      Housing   2001    1.0     54.3          0.018     0.009
LC02      Housing   2001    1.0     72.6          0.022     0.014

When you insert the pivot table, Excel reads this range into an in-memory pivot cache. Every field in the header becomes available in the PivotTable Fields pane. The cache is a snapshot: if you edit the source data, you must refresh (PivotTable Analyze → Refresh, or Alt+F5) before the pivot reflects the change. For CAE workflows where solvers write new result files, this refresh discipline matters more than in ordinary spreadsheet work.

Rows: the grouping axis that defines the engineering question

The Rows area controls the granularity of the summary. Each unique combination of row-field values becomes one output row. In CAE terms, Rows usually answer "grouped by what?" — by Part, by LoadCase, by Mode, or by a nested combination such as Part then LoadCase. Placing Part in Rows and LoadCase beneath it produces a hierarchical outline: each part expands into its load cases, with subtotals at the part level. This is the natural structure for a stress summary report, because reviewers first ask "which component is critical?" and then "under which load?".

Two practical cautions apply. First, avoid placing a high-cardinality field such as NodeID in Rows unless you genuinely need per-node output; a 200,000-node model will produce an unreadable pivot. Second, numeric fields such as Time_s placed in Rows are grouped by exact value by default, which can create hundreds of rows for a transient run. Either round the time column in the source data (for example to 0.1 s) or use a helper column such as TimeBand with values like 0–0.2 s, 0.2–0.4 s.

Columns: the comparison axis

The Columns area spreads one field horizontally so that categories sit side by side for direct comparison. In simulation data, Columns is most effective for low-cardinality categorical fields: LoadCase (LC01, LC02, LC03), Mode (1–6), or a derived ResultType field. With Part in Rows and LoadCase in Columns, each row becomes a part and each column a load case, so the reader can scan across a row to see how one component responds to different loads. This is the classic "component × load case" matrix used in design reviews.

Columns should not be used for continuous variables. Putting Time_s in Columns creates one column per time step and quickly becomes unmanageable; time belongs in Rows (banded) or in Filters (single step selected). A useful rule: if a field has more than roughly 10–15 distinct values, it is a poor candidate for Columns.

Values: the aggregation that carries the engineering meaning

The Values area performs the actual computation. Excel defaults to Count when you drag a numeric field into Values, which is almost never what a CAE engineer wants — a count of stress values tells you how many nodes were exported, not how severe the stress is. Always change the aggregation explicitly via Value Field Settings:

• Max of VonMises_MPa — the peak stress per group, the most common safety-relevant metric.
• Average of VonMises_MPa — useful for comparing overall severity between load cases, but it hides local hot spots.
• Max of DispY_mm and Min of DispY_mm — together they bound the deflection envelope in the Y direction.
• Count of NodeID — a data-integrity check: the number of nodes per group should match the expected mesh count.

You can place the same field into Values multiple times with different aggregations. A single pivot can therefore show Max stress, Average stress, and node Count side by side, which is often enough to judge whether a high maximum is a genuine hot spot or a single anomalous node. Note that Max and Min are not additive across groups: the maximum of two subgroups is not the sum of their maxima, so subtotals for Max fields are computed over the underlying records, not by combining child totals. This is correct behavior, but it surprises users who expect subtotals to add up.

Filters: scoping the analysis without changing its structure

The Filters area (also called the page field) restricts which records enter the calculation while leaving the row and column layout intact. In CAE work, Filters is the right place for fields that define the analysis scope rather than the comparison: LoadCase when you want to inspect one load case at a time, Time_s when you want the final time step only, or a Subcase or SolutionSet identifier. Selecting Time_s = 1.0 in the filter reduces a transient dataset to its end state, and the pivot then reports peak stress per part at that instant.

Do not confuse Filters with the row/column label filters. The Filters area creates a single selection control above the pivot; label filters (for example, show only parts whose max stress exceeds 100 MPa) are applied inside a row or column field and are better suited to threshold-based screening. For a quick "top offenders" view, a value filter on the row field — Value Filters → Greater Than → 100 — is more direct than a page filter.

Reading the result: a worked example

Using the sample rows above, with Part in Rows, LoadCase in Columns, Max of VonMises_MPa in Values, and Time_s = 1.0 in Filters, the pivot returns:

The reading is immediate: the Bracket under LC02 is the critical combination at 131.8 MPa, and the Grand Total column correctly reports the overall maximum rather than a sum. If the allowable stress for the bracket material is, say, 120 MPa, the value filter Greater Than 120 on the Values field would leave only that one cell visible — a fast screening step before any detailed contour inspection. If instead the Count of NodeID for Bracket/LC02 came back as 1 while other groups show hundreds, the high stress is likely a single-node artifact (constraint singularity or a poorly connected element) and should be verified in the solver before being treated as a design issue.

How the four areas interact

The diagram below summarizes the mapping between pivot areas and simulation semantics, and shows how a single flat results table is redistributed into a review-ready matrix.

Common pitfalls and how to detect them

Three failure modes account for most incorrect CAE pivots. Default Count aggregation is the most frequent: a field dropped into Values shows counts, and the resulting numbers look plausible but are meaningless as stress values. Check the field header — it reads "Count of VonMises_MPa" rather than "Max of VonMises_MPa". Stale cache is the second: after re-running the solver and overwriting the CSV, the pivot still shows the old peak. If a value does not change after a known design modification, refresh before investigating further. Unit mixing is the third: if one export writes stress in Pa and another in MPa, Max aggregation silently returns a value three orders of magnitude too large. Enforce a single unit per column at export time, and add a Units column if multiple sources must be combined.

With these four areas understood as grouping, comparison, aggregation, and scope, the pivot table becomes a compact query language for simulation results. The next step is to extend this foundation with calculated fields, grouping, and multi-level layouts that handle larger result sets without losing traceability back to the underlying nodes and time steps.