A PivotTable is two things: a view, a block of records in a worksheet
substream that starts with SxView and says which fields sit on which axis
and how the table is laid out, and a PivotCache, a copy of the source
data that lives partly in the Globals substream and partly in a separate stream of the
compound file. A cache can serve several views. Records here are described field by
field; section numbers are those of [MS-XLS] v20250819; integers are little-endian,
and strings named XLUnicodeStringNoCch have their length in a separate field
(see Strings and the SST).
The later additions to the view and cache (Excel 2002 and up) are
SXAddl records, described on a page of their own.
Axes: SXAxis
Several records name an axis with a 2-byte SXAxis: bit 0 sxaxisRw
(rows), bit 1 sxaxisCol (columns), bit 2 sxaxisPage (page),
bit 3 sxaxisData (values), 4–15 reserved. A field may be on more than one.
The view
| Record | Fields |
|---|---|
| SxView (0x00B0, 2.4.313) | Opens the view; the records after it up to the next view or the end of the sheet belong to it. ref (8, Ref8U: the report body), rwFirstHead (2: the first row of the row area), rwFirstData (2), colFirstData (2: the first row and column of the data area; all 1 when no axis is assigned), iCache (2, signed: the zero-based index of an SXStreamID record in the Globals substream, naming the PivotCache), 2 reserved, sxaxis4Data (2, SXAxis: the default axis of the data field; the row or the column bit), ipos4Data (2, signed, –1 to 0x7FFF: the position of the data field on that axis), cDim (2, signed: the number of pivot fields; equals the number of Sxvd records after this one and the fields in the cache), cDimRw, cDimCol, cDimPg (2 each: fields on the row, column and page axes; equal to the lengths of the SxIvd and SXPI arrays), cDimData (2, signed: data fields, equal to the number of SXDI records), cRw, cCol (2 each: pivot lines in the row and column areas, equal to the lengths of the two SXLI arrays), a flag word, itblAutoFmt (2, an AutoFmt8: the AutoFormat; overridden by the one in SXViewEx9 unless that is 1), cchTableName (2, 0 to 255), cchDataName (2, 1 to 254), stTable (the view’s name) and stData (the name of the data field). Flag bits: 0 fRwGrand (grand totals for rows), 1 fColGrand (for columns), 2 unused, 3 fAutoFormat, 4 fAtrNum, 5 fAtrFnt, 6 fAtrAlc, 7 fAtrBdr, 8 fAtrPat (number, font, alignment, border and pattern AutoFormat applied), 9 fAtrProc (width and height AutoFormat), 10–15 unused. |
| SXEx (0x00F1, 2.4.282) | More view properties; opens the selection and formatting records. csxformat (2: the number of SxFormat records after it), cchErrorString, cchNullString, cchTag (2 each, 0xFFFF when the string is absent), csxselect (2: the number of SxSelect records), crwPage (2, DRw) and ccolPage (2, DCol: the page area’s size), flags, then cchPageFieldStyle, cchTableStyle, cchVacateStyle (2 each, 0xFFFF when absent), then the strings stError (shown in cells with errors), stDisplayNull (in null cells), stTag (saved with the view), stPageFieldStyle, stTableStyle and stVacateStyle (names of StyleExt styles for the page area, the body and cells that become empty on recalculation). Flags: 0 fAcrossPageLay (page fields fill left to right before going down), 1–8 cWrapPage (the number of page fields before wrapping, 0 no wrap), then bits for fEnableWizard, fEnableDrilldown, fEnableFieldDialog, fPreserveFormatting (if 1 there are no SxFormat records), fMergeLabels (merge and centre the empty cells next to captions), fDisplayErrorString, fDisplayNullString, fSubtotalHiddenPageItems, with the rest reserved (the PDF text does not show the exact bit positions of this group). |
| SXViewEx9 (0x0810, 2.4.315) | rt (2, 0x0810), flags: fFrtAlert (features of this table are not supported by older versions), fPrintTitles (print titles follow the table), fLineMode (some field is in outline mode), fRepeatItemsOnEachPrintedPage, others reserved; itblAutoFmt (2, AutoFmt8). |
| SxViewEx (0x080C, 2.4.314) | Opens the extra records of an OLAP view. frtHeaderOld (4), csxth (4: the number of SXTH records after it), csxpi (4: SXPIEx records), csxvdtex (4: SXVDTEx records), cbFuture (4) and rgbFuture (that many bytes from newer versions). |
| SxSelect (0x00F7, 2.4.302) | The selected cells of the report; the selection itself is in the PivotTable rule that follows. pnn (1, a PaneType: the active pane), sxaxisAct (2, SXAxis without the data bit: the axis of the selection), iDimAct (2: the position of the active field), iLiStart, iLiAct, iLiMin, iLiMax (2 each: zero-based pivot line positions: where the selection started, the last line included, and the smallest and largest line that could be), rwClick, colClick, rwClickPrev, colClickPrev (2 each: the cell last clicked and the one before), then a flag word: cClick (5 bits: clicks made to refine the selection), fLabelOnly, fDataOnly, fToggleDataHeader (the selection toggle is on), fSelectionClick, fExtendable (another line can be added), 6 unused bits. |
| SxFormat (0x00FB, 2.4.287) | Opens a piece of differential formatting for an area: a 2-byte word with rlType (4 bits: 0 the formatting was cleared, 1 applied) and reserved bits, then cbData (2: the size of the SxDXF record that follows). The rule (SxRule) says which cells it covers. |
| SxDXF (0x00F4, 2.4.280) | dxf: the differential format (a DXFN12 structure, see styles) applied to a PivotTable area. |
Pivot fields, items and data fields
| Record | Fields |
|---|---|
| Sxvd (0x00B1, 2.4.309) | Opens one pivot field; one per field, in cache field order. sxaxis (2, SXAxis: the axes the field is on; with the data bit a matching SXDI exists), cSub (2: the number of subtotal flags set), a flag word of subtotals, cItm (2, signed: the number of SXVI records that follow), cchName (2: 0xFFFF means no caption), stName (the caption, overriding the cache field’s name; unique within a non-OLAP view). Subtotal flag bits: 0 fDefault (the automatic subtotal), 1 fSum, 2 fCounta (count), 3 fAverage, 4 fMax, 5 fMin, 6 fProduct, 7 fCount (count of numbers), 8 fStdev, 9 fStdevp, 10 fVariance, 11 fVariancep, 12–15 reserved. If fDefault is set no other may be; for a field on the row, column or page axis each set flag needs one SXVI whose itmType is the flag’s bit number plus 1 (1 for default, 2 for sum, and so on to 12). |
| SXVI (0x00B2, 2.4.312) | A pivot item. itmType (2, signed: 0 a data value, 1 default subtotal, 2 sum, 3 count, 4 average, 5 max, 6 min, 7 product, 8 count of numbers, 9 standard deviation (estimate), 10 standard deviation (population), 11 variance (estimate), 12 variance (population)), flags (2): 0 fHidden (only for data items, never in OLAP views), 1 fHideDetail (collapsed), 2 reserved, 3 fFormula (a calculated item), 4 fMissing (not in the source data), 5–15 reserved; iCache (2, signed: the cache item index in the field’s cache field, or –1 when itmType is not 0), cchName (2: 0xFFFF none, else up to 254), stName (a caption overriding the cache item’s value; unique within the field). |
| SXVDEx (0x0100, 2.4.310) | Extended pivot field properties. A 4-byte flag group, isxdiAutoSort (2, signed: the data item AutoSort uses, –1 the items’ own values), isxdiAutoShow (2, signed: the data item AutoShow ranks by, –1 none), ifmt (2: the number format) and an optional subName (an SXVDEx_Opt: the name of the aggregate function for subtotals). Flag bits from 0: fShowAllItems (show items missing from the source; 0 for OLAP), fDragToRow, fDragToColumn, fDragToPage, fDragToHide (1 allowed; ignored for OLAP), fNotDragToData (1 forbidden), reserved, fServerBased (a server-based page field; equal to the same flag of the cache field), reserved, fAutoSort, fAscendSort, fAutoShow, fTopAutoShow (the top rather than the bottom values), fCalculatedField (equal to the cache field’s), fPageBreaksBetweenItems, fHideNewItems (new items are hidden after a refresh; 0 for non-OLAP); then in the second part fOutline (outline form), fInsertBlankRow, fSubtotalAtTop (with outline) and citmAutoShow (8 bits, at least 1: how many items AutoShow keeps). |
| SXDI (0x00C5, 2.4.278) | A data item. isxvdData (2, signed: the pivot field aggregated; it has the data bit in its Sxvd), iiftab (2: 0 sum, 1 count, 2 average, 3 max, 4 min, 5 product, 6 count of numbers, 7 standard deviation (sample), 8 (population), 9 variance (sample), 10 (population); ignored in OLAP views), df (2: how the value is shown: 0 as is, 1 difference from the item given by isxvi, 2 percentage of it, 3 percentage difference from it, 4 running total over the items of isxvd, 5 percentage of the row total, 6 of the column total, 7 of the grand total, 8 the value times the grand total of grand totals over the row total times the column total), isxvd (2: the field df uses, for 1 to 4), isxvi (2: for 1 to 3, a pivot item index up to 0x7EFE, or 0x7FFB the previous item or 0x7FFC the next), ifmt (2: number format), cchName (2: 0xFFFF no name) and stName (a name overriding the default; unique in a non-OLAP view). |
| SxIvd (0x00B4, 2.4.292) | An array of 2-byte signed values listing the fields on an axis in order, each −2 (the data field sits on this axis) or a pivot field index. One record for the row axis if cDimRw is above 0 and, after it, one for the column axis if cDimCol is above 0: so zero, one or two of these per view. |
| SXPI (0x00B6, 2.4.298) | The page axis: present exactly when cDimPg is above 0; rgsxpi is an array of SXPI_Item, cDimPg of them, each 6 bytes: isxvd (2, signed: the pivot field), isxvi (2, signed: the pivot item filtering the page, or 0x7FFD for all items), idObj (2, signed: the identifier of the Obj holding the page item drop-down arrow). |
| SXLI (0x00B5, 2.4.293) | Pivot lines of the row area, then of the column area (two records when either cRw or cCol is above 0, otherwise none). rgsxli is an array of SXLIItem. An SXLIItem is: cSic (2, signed: how many leading item indexes repeat the previous line’s; 0 for a grand total), itmType (15 bits: 0 a data value, 1 automatic subtotal, 2 sum, 3 count, 4 count of numbers, 5 average, 6 maximum, 7 minimum, 8 product, 9 standard deviation (estimate), 10 (population), 11 variance (estimate), 12 (population), 13 grand total, 14 blank line) plus one reserved bit, isxviMac (2, signed: how many entries of rgisxvi are shown; 1 for a grand total), a flag word (fMultiDataName: 0 the data field name is used for the total, 1 for the subtotal; iData, 8 bits: the data item index; fSbt a subtotal; fBlock a block total; fGrand a grand total; fMultiDataOnAxis; unused and reserved bits), then rgisxvi: isxviMac signed 2-byte pivot item indexes (0 up to 0x7EF4, or 0x7FFF for no item, an empty cell in the line). |
| SxRule (0x00F0, 2.4.301) | An area of one or more views (a PivotTable rule), opening a run of SxFilt records. iDim (8 bits: the zero-based position of the field in its axis), isxvd (8 bits: a pivot field or cache field index 0 to 0xFD, 0xFE the data field, 0xFF none), the axis bits sxaxisRw, sxaxisCol, sxaxisPage, sxaxisData, sxrType (4 bits: 0 no area, 1 pivot fields named by the filters, 2 cells showing data items, 3 the whole view, 4 the top-left cells or top-right, 5 a field caption cell, 6 the top-right or top-left), fPart (only a part of the area), fDataOnly, fLabelOnly, fGrandRw, fGrandCol (grand total row or column included), fGrandRwSav, fGrandColSav, fCacheBased (isxvd is a cache field index), csxFilt (2: the number of SxFilt records), and the optional offsets irwFirst, irwLast, icolFirst, icolLast (1 each). |
| SxFilt (0x00F2, 2.4.285) | One filter of a rule. Axis bits (row, column, page, data), 2 reserved bits, iDim (10 bits), isxvd (10 bits: −2 for the data field, otherwise a pivot field index, or a cache field index when the rule is cache based), fSelected (the field header is selected), then grbitSbt (2, signed: the subtotals included, as bits: 0x0001 data, 0x0002 default, 0x0004 sum, 0x0008 count, 0x0010 average, 0x0020 max, 0x0040 min, 0x0080 product, 0x0100 count of numbers, 0x0200 standard deviation, 0x0400 standard deviation (population), 0x0800 variance, 0x1000 variance (population), 0x4000 blank) and cisxvi (2: how many indexes of the following SxItm record apply). |
| SxItm (0x00F5, 2.4.291) | An array of 2-byte signed indexes used by a filter: pivot items, data items (–2 is not used) or, when the rule is cache based, cache item indexes (0 or above, within the cache field). |
| SxName (0x00F6, 2.4.294) | Opens a calculated field or item. A flag word (fErrName: the record is invalid, set when ifdb cannot be resolved), ifdb (2, signed: the cache field index, or –1 when csxpair is above 0 or the formula cannot be parsed), ifn (2, signed: –1) and csxpair (2: the number of SXPair records that follow). |
| SxFmla (0x00F9, 2.4.286) | A PivotParsedFormula (the formula of a calculated field or item); opens a collection stored with the cache. |
| SXFormula (0x0103, 2.4.288) | ifdb (2, signed): the cache field a calculated item belongs to; the formula is in the last SxFmla before it. |
| SXPair (0x00F8, 2.4.297) | An item used to compute a calculated item. isxvd (2: a cache field index), iCache (2, signed: the pivot item), flags: fFormula (the item is itself calculated; not with fPhysical), 2 reserved bits, fPhysical (iCache is not a cache item index), fRelative (the item is addressed by position relative to the calculated item). |
OLAP views
| Record | Fields |
|---|---|
| SXTH (0x080D, 2.4.308) | A pivot hierarchy. frtHeaderOld (4), a 4-byte flag group: fMeasure (an OLAP measure), unused, fOutlineMode, fEnableMultiplePageItems, fSubtotalAtTop, fSet (a named set), fDontShowFList (hidden in the field list), fAttributeHierarchy, fTimeHierarchy, fFilterInclusive (a manual filter includes rather than excludes the members), unused, fKeyAttributeHierarchy, fKPI, then 19 unused bits; sxaxis (2: the axes it is on), isxvd (4, signed: a pivot field index), csxvdXl (4, signed: the number of pivot fields of its levels), a second flag word (fDragToRow, fDragToColumn, fDragToPage, fDragToData, fDragToHide, 11 unused bits), cisxvd (4: the length of rgisxvd, whose entries are pivot field indexes or –1 for none) and cHiddenMemberSets (4: the deepest one-based level of hidden member sets). Strings and arrays follow (names are not shown in this brief). |
| SXPIEx (0x080E, 2.4.299) | The OLAP part of the page axis; one per SXPI_Item. frtHeaderOld (4), isxth (4: the pivot hierarchy index, whose SXTH has the page axis bit), stUnique (XLUnicodeString, at most 255 characters: the unique name of the member used for filtering) and stDisplay (its caption). |
| SXVDTEx (0x080F, 2.4.311) | OLAP extensions of a pivot field; absent in non-OLAP views. frtHeaderOld (4), flags (fTensorSort, fDrilledLevel (all items are expanded), fItemsDrilledByDefault, fMemPropDisplayInReport, fMemPropDisplayInTip, fMemPropDisplayInCaption; 10 reserved bits), isxth (2, signed: the pivot hierarchy, –1 none), isxtl (4, signed: the zero-based OLAP level), csxvi (4, signed) and rgsxvi (csxvi SXVIFlags: extra flags of each pivot item). |
The PivotCache
The view finds its cache through iCache, an index into the SXStreamID
records of the Globals substream. The cache’s own records are in that stream of the
compound file (idStm), in this order: a header, one run of records for
each cache field, and the cache records themselves.
| Record | Fields |
|---|---|
| SXStreamID (0x00D5, 2.4.303) | idStm (2): the stream of the PivotCache storage. |
| SXVS (0x00E3, 2.4.317) | The kind of source data, in 2 bytes: 0x0001 SHEET (a range; followed by DConRef, DConName or DConBin), 0x0002 EXTERNAL (followed by a DbQuery run: connection and query), 0x0004 CONSOLIDATION (multiple ranges; followed by an SXTbl run), 0x0010 SCENARIO (an internal temporary structure with no source data). |
| SXDB (0x00C6, 2.4.275) | PivotCache properties. crdbdb (4, signed: the number of cache records), idstm (2: the stream holding the data), flags (fSaveData (cache records exist; 0 for OLAP), fInvalid, fRefreshOnLoad, fOptimizeCache, fBackgroundQuery, fEnableRefresh; 10 unused bits), 2 unused, cfdbdb (2, signed: the cache fields that correspond to source columns), cfdbTot (2, signed: all cache fields), crdbUsed (2: the number of records used for the cache’s calculation), vsType (2: the source type, equal to the SXVS value), cchWho (2: 0xFFFF none) and an optional rgb (the name of the user who last refreshed). |
| SXDBEx (0x0122, 2.4.277) | More properties. numDate (8, a DateAsNum: when the cache was last refreshed), cSxFormula (4: the number of SXFormula records of the cache). |
| SXFDB (0x00C7, 2.4.283) | A cache field. A flag word: fAllAtoms (the field has a collection of cache items), fSomeUnhashed and fUsed (both ignored), fHasParent (ifdbParent names a parent), fRangeGroup (grouped by numeric or date ranges), fNumField (the items contain numbers), unused, fTextEtcField (text), fnumMinMaxValid, fShortIitms (more than 255 items), fNonDates, fDateInField, unused, fServerBased, fCantGetUniqueItems, fCalculatedField; then ifdbParent (2: the parent’s cache field index), ifdbBase (2: the base field of a grouping), citmUnq (2, ignored), csxoper (2: the number of cache items in this field), cisxoper (2: the number in the base field), catm (2: the number of items in the group collection) and stFieldName (an XLUnicodeString). |
| SXFDBType (0x01BB, 2.4.284) | wTypeSql (2, an ODBCType: the ODBC data type of the field’s values). |
| SXRng (0x00D8, 2.4.300) | Numeric or date grouping of a grouping cache field. Flags: fAutoStart (the start is recalculated from the data), fAutoEnd, iByType (3 bits: 0 by numeric value, 1 seconds, 2 minutes, 3 hours, 4 days, 5 months, 6 quarters, 7 years; the following start, end and interval cache items then have fixed counts: 62 for seconds and minutes, 26 for hours, 368 for days, 14 for months, 6 for quarters), 11 unused bits. |
| SxIsxoper (0x00D9, 2.4.290) | For discrete grouping: maps cache items of a field to the cache items of its grouping field (an array of 2-byte indexes). |
| SXTbl (0x00D0, 2.4.305) | Multiple consolidation ranges. cdref (2: the number of ranges), csxtbpg (2: the number of SxTbpg records after it), then a word with cPages (15 bits: the number of optional cache fields) and fAutoPage (an automatically created cache field qualifies each source range). |
| SxTbpg (0x00D2, 2.4.306) | One per source range, in order: the cache item indexes qualifying the range for each page field, –1 meaning a blank item. |
| SXTBRGIITM (0x00D1, 2.4.307) | cItems (2): the number of SXString records that follow, the values of the consolidation items. |
| SXString (0x00CD, 2.4.304) | A text cache item or value, or a segment of the string of a cache source or connection: cch (2) then the characters (an XLUnicodeStringNoCch). |
Cache items
The values of a cache field and the cache records themselves are made of these small records.
| Record | Contents |
|---|---|
| SXNum (0x00C9) | num (8, Xnum): a number. |
| SxBool (0x00CA) | bool (2, Boolean). |
| SxErr (0x00CB) | wbe (2): an error code: 0x00 #NULL!, 0x07 #DIV/0!, 0x0F #VALUE!, 0x17 #REF!, 0x1D #NAME?, 0x24 #NUM!, 0x2A #N/A. |
| SXInt (0x00CC) | num (2, signed): a small integer. |
| SXDtr (0x00CE) | A date and time: yr (2), mon (2), dom (1), hr (1), min (1), sec (1). |
| SxNil (0x00CF) | Empty: no data. |
| SXDBB (0x00C8, 2.4.276) | The values of all cache fields whose fAllAtoms is 1 and that correspond to source data, for one cache record. |
Source
[MS-XLS] Excel Binary File Format (.xls) Structure, v20250819, sections 2.4.275–2.4.317 and the structures SXAxis, SXPI_Item, SXLIItem, SxIvdRw and SxIvdCol (2.5). Not covered: the stream layout of the cache storage (the order of records in the PivotCache stream, 2.1.7), the PivotParsedFormula, the optional SXVDEx_Opt, SXVIFlags and AutoFmt8 structures, the field-by-field layout of SXTH’s trailing strings, and the bit positions marked above. Excel 2002 and later extras are on the SXAddl page.