Excel PivotTable range export
A worksheet range that was produced by an Excel PivotTable and then copied, saved, or sent on as if it were a flat table. People assume every row is a record and every value column is a raw sum; a pivot is neither.
The shape, how the file is really laid out
Excel offers three pivot layouts: Compact (the default), Outline, and
Tabular. In Compact and Outline, an outer row field is printed once at the
top of its group and left blank on the rows beneath it, "Repeat All Item
Labels" is off by default, so the label column is mostly empty. Subtotal
rows are inserted at the top (or bottom) of each group and a Grand Total row
sits at the very end; both are on by default. Empty intersections render as
blank or as the literal "(blank)". A visible column headed, say, "Sales" may
actually be a "Show Values As" transform, % of Grand Total, Running Total
In, Difference From, so the cells are ratios or cumulative figures, not the
additive dollars the header implies. Number formatting is display-only; the
stored value can carry more precision or a different sign.
The traps
Summing the value column double-counts, because the per-group subtotal rows and the Grand Total row are mixed in with the leaf rows and look identical.
- Every trap in this export, worked through
- The reconciliation anchor, the total the file asserts about itself
- The refusal cases, when to stop and ask instead of guess
Reference: Microsoft Support, "Design the layout and format of a PivotTable" · Microsoft Support, "Show different calculations in PivotTable value fields (Show Values As)"