.XLS File Format – Worksheet settings

Window, panes and selection

Window2 (0x023E)

The sheet’s view in a workbook window; one per Window1 record, in the same order (2.4.346). It is 18 bytes, or 10 in a chart sheet, where only fSelected is used.

Pane (0x0041)

Where the window is split (2.4.189): x (2) and y (2), the split position, in cells when the panes are frozen (x at most 255) and in twips otherwise (at most 32,767); rwTop (2), the top row of the bottom pane; colLeft (2), the left column of the right pane; pnnAcct (1), the active pane; 1 reserved byte. Panes (PaneType, 2.5.196): 0 bottom right, 1 top right, 2 bottom left, 3 top left.

Selection (0x001D)

A large selection is split over several consecutive Selection records with the same pane and active cell (2.4.248).

SheetExt (0x0862)

Sheet tab colour and more (2.4.259): FrtHeader (12); cb (4, the record size, 0x28 when the optional part is present); 4 bytes whose bits 0–6 are icvPlain, the tab colour as a palette index 8 to 63 or 0x7F for none; then, if cb is 0x28, a 20-byte SheetExtOptional (2.5.238): 4 bytes with bits 0–6 icvPlain12 (tab colour again; icvPlain wins if they differ), bit 7 fCondFmtCalc (conditional formatting formulas are evaluated), bit 8 fNotPublished (the sheet is not published to a server); then a 16-byte CFColor for the tab, which may here be automatic.

Sheet options and outlines

WsBool (0x0081)

Guts (0x0080, 2.4.134): two unused 2-byte fields, then iLevelRwMac and iLevelColMac (2 each), the deepest row and column outline levels: 0 none, otherwise level + 1 (2 for level 1 up to 8 for level 7).

GridSet (0x0082): 2 bytes, reserved, always 1 (2.4.132).

Default sizes

DefaultRowHeight (0x0225)

Height of rows with no Row record (2.4.87): 2 bytes of flags (bit 0 fUnsynced, the default height was changed; bit 1 fDyZero, empty rows are hidden; bit 2 fExAsc and bit 3 fExDsc, thick top or bottom borders on empty rows; 4–15 reserved), then 2 bytes: when fDyZero is 0, miyRw, the height in twips (1 to 8,179); when it is 1, miyRwHidden, the height the rows take when unhidden (0 to 8,179).

Printing

Setup (0x00A1)

Page breaks

HorizontalPageBreaks (0x001B, 2.4.142): cbrk (2, at most 1,026) and that many 6-byte HorzBrk: row (the first row below the break), colStart, colEnd (the columns it spans, up to 16,383), sorted by row then column, not overlapping. VerticalPageBreaks (0x001A, 2.4.343): cbrk (at most 255) and VertBrk entries: col (the first column right of the break), rowStart, rowEnd.

Headers and footers

Header (0x0014) and Footer (0x0015) hold the text printed at the top and bottom of every page (or of odd pages, see below) as an XLUnicodeString of at most 255 characters; an empty record means none (2.4.136, 2.4.124). The text may hold these codes:

HeaderFooter (0x089C)

HFPicture (0x0866)

Pictures for &G (2.4.138): FrtHeader (12); a flags byte (bit 0 fIsDrawing: the data is an OfficeArtDgContainer; bit 1 fIsDrawingGroup: it is an OfficeArtDggContainer, exactly one of the two being set; bit 2 fContinue: continues the previous HFPicture); 1 reserved byte; then the drawing data, an Office Drawing container defined in [MS-ODRAW].

Merged cells, hyperlinks and validation

DVal (0x01B2)

Starts the data validation records of the sheet (2.4.96): 2 bytes of flags (bit 0 fWnClosed, the input window is closed); xLeft and yTop (4 each, position of the input window in pixels); idObj (4, the drop-down button’s object id, or −1 when none is showing); idvMac (4, the number of Dv records that follow, up to 65,534).

Dv (0x01BE)

Sorting and AutoFilter

Sort (0x0090)

The last sort’s settings (2.4.263): 2 bytes of flags (bit 0 fCol, sort columns left to right rather than rows; bits 1–3 fKey1Dsc to fKey3Dsc, descending; bit 4 fCaseSensitive; bits 5–9 iOrder, the custom list used for the order; bit 10 fAltMethod, use phonetic text; 11–15 reserved); cchKey1, cchKey2, cchKey3 (1 byte each); the three key strings (XLUnicodeStringNoCch, present when their length is not 0); 1 reserved byte.

AutoFilter (0x009E)

An AFDOper (2.5.5) is vt (1), grbitSign (1: 1 less than, 2 equal, 3 less or equal, 4 greater, 5 not equal, 6 greater or equal) and 8 bytes of value. vt: 0 no condition; 2 an RK number (4 bytes, then 4 unused); 4 a double; 6 a string (4 unused bytes, cch, fCompare = 0 when it holds ? or * wildcards, a reserved and an unused byte; the text follows the record’s fixed part); 8 a Boolean or error (2-byte Bes, then 6 unused); 12 all blanks; 14 all non-blanks.

Scenarios

ScenMan (0x00AE, 2.4.246): csct (2, number of scenarios); isctCur (2, the one selected in the Scenario Manager, −1 none); isctShown (2, the one applied, −1 none); irefResult (2, 0 to 32); that many Ref8U result ranges. SCENARIO records follow.

SCENARIO (0x00AF)

Data consolidation and label ranges

DCon (0x0050, 2.4.82): four 2-byte fields: iiftab, the function (0 average, 1 count numbers, 2 count, 3 maximum, 4 minimum, 5 product, 6 standard deviation of a sample, 7 standard deviation of the population, 8 sum, 9 variance of a sample, 10 variance of the population); fLeftCat (1: match rows by the labels in the left column, case insensitively, else by position); fTopCat (the same for columns by the top row); fLinkConsole (1: create links to the source data). The source ranges follow as:

A DConFile (2.5.69) is an XLUnicodeStringNoCch holding the workbook, or workbook and sheet, in the encoded virtual-path form also used by external references, or a self-reference whose first character is 2. These records also name PivotTable source ranges.

Custom sheet data

BigName (0x0418, 2.4.18): a name and value of custom data attached to the sheet: 2 reserved bytes (0x1000), lcbData (4, size of the value), cbName (1), the name in ANSI characters, then the value, carried on in ContinueBigName records (0x043C, each fewer than 8,225 bytes, 2.4.59).

Sorting and advanced AutoFilters

SortData (0x0895)

A sort over a range, written after a Sort-style FrtHeader (2.4.264). A future record: frtHeader (12, rt = 0x0895), then a 2-byte flag word, rfx (16, an RFX: the range to sort), cconditions (4), idParent (4) and the sort conditions, each carried in its own ContinueFrt12 record.

cconditions is the number of sort conditions (one ContinueFrt12 record each). idParent matters only when sfp is 1 (it equals idList of the table’s TableFeatureType) or 3 (the zero-based index of the Qsi record in the substream); otherwise ignore it.

SortCond12 (2.5.242)

The spec lists sortOn values 0, 1, 2 (condData is a CondDataValue) and 3 (a CFFlag) without naming them. CondDataValue (2.5.55) is condDataValue (4: for sortOn 1 or 2 the zero-based index of a DXF record in the Globals substream that gives the formatting; for 0 it is zero and ignored) and 4 reserved zero bytes. CFFlag (2.5.31) is iIconSet (4, a KPISets value naming an icon set) and iIcon (4, signed: -1 no icon, 0 to 4 the first to fifth icon of the set), which changes the sort order of icon-set sorts.

AutoFilter12 (0x087E)

The advanced settings of one AutoFilter column (2.4.7). A future record with an FrtRefHeader (rt = 0x087E, fFrtRef 1, the range the filter covers).

The flag word bit positions follow the order the spec lists the fields in (A, B, C ... from bit 0). cft values:

AF12Criteria (2.5.3): doper (10, an AFDOper, the comparison) and str (an XLUnicodeStringNoCch of length doper.vtValue.cch, present exactly when doper.vt is 6). AF12DateInfo (2.5.4), 20 bytes: year (2), month (2, 1 to 12), day (4, 1 to 31), hour (2, 0 to 23), minute (2, 0 to 59), second (2, 0 to 59), unused1 (2) and reserved1 (4, zero). AF12CellIcon (2.5.2): iIconSet (4, a KPISets value) and iIcon (4): 0xFFFFFFFF for KPINIL (no icon); 0 to 2 for the three-icon sets (arrows, grey arrows, flags, two traffic-light sets, signs, symbols, symbols 2); 0 to 3 for the four-icon sets; 0 to 4 for the five-icon sets (arrows, grey arrows, rating, quarters).

Shared features

Shared features hold data that belongs to ranges of cells and is stored in blocks of records: FeatHdr opens a block, Feat records follow it. SharedFeatureType (2.5.237) is 2 for enhanced protection (ISFPROTECTION), 3 for ignored formula errors (ISFFEC2), 4 for smart tags (ISFFACTOID) and 5 for a table (ISFLIST).

FeatHdr (0x0867)

Common data for one feature type (2.4.112). frtHeader (12, rt = 0x0867), isf (2, ISFPROTECTION, ISFFEC2 or ISFFACTOID), reserved (1, 1), cbHdrData (4: 0 means rgbHdrData is absent, 0xFFFFFFFF that it is present) and rgbHdrData. In the Globals substream the data is an EnhancedProtection structure for ISFPROTECTION, none for ISFFEC2, and a PropertyBagStore ([MS-OSHARED] 2.3.4.1) of smart-tag header data for ISFFACTOID; in worksheet and macro-sheet substreams, ISFFACTOID has none.

Feat (0x0868)

FeatProtection (2.5.124)

4 bytes of flags (bit 0 fSD: the structure holds a self-relative security descriptor; the other 31 bits reserved zero); wPassword (4, the verifier of the password needed to edit the referenced ranges of a protected sheet; 0 for an empty password); stTitle (XLUnicodeString, the title); and sdContainer (SDContainer: cbSD, 4, more than 20, then the SECURITY_DESCRIPTOR of [MS-DTYP] 2.4.6 naming who may edit the ranges without a password).

EnhancedProtection (2.5.104)

The FeatHdr data of ISFPROTECTION: 4 bytes, one bit each from bit 0, 1 meaning the action is allowed on a protected sheet: iprotObjects (edit linked or embedded objects), iprotScenarios, iprotFormatCells, iprotFormatColumns, iprotFormatRows, iprotInsertColumns, iprotInsertRows, iprotInsertHyperlinks, iprotDeleteColumns, iprotDeleteRows, iprotSelLockedCells, iprotSort, iprotAutoFilter, iprotPivotTables and iprotSelUnlockedCells (bits 0 to 14); bits 15 to 31 reserved zero.

FeatFormulaErr2 and FFErrorCheck (2.5.123, 2.5.126)

FeatFormulaErr2 is grffecIgnore (4), an FFErrorCheck of the error checks that are ignored: bit 0 ffecCalcError, 1 ffecEmptyCellRef, 2 ffecNumStoredAsText, 3 ffecInconsistRange (formulas that refer to less than a whole block of continuous data), 4 ffecInconsistFmla (inconsistent with neighbours), 5 ffecTextDateInsuff, 6 ffecUnprotFmla, 7 ffecDataValidation; bits 8 to 31 reserved zero.

FeatSmartTag (2.5.125)

hashValue (4, a hash of the cell’s text so changes can be detected; 0 for empty text, else computed byte by byte: shift the hash left 4 and add the byte; if the top four bits are then non-zero, clear them and XOR the hash with those bits shifted right 24; at the end a hash of 0 becomes 1); cSmartTags (1) and rgFactoid (that many FactoidData structures, one per smart tag, whose layout is not reproduced here).

Tables (lists)

An Excel table is a block starting at FeatHdr11, with one Feature11 (or Feature12) record per table, each followed by List12 records, then the table’s AutoFilter12 and SortData records.

FeatHdr11 (0x0871)

Common information for all tables on a sheet (2.4.113): frtHeader (12, rt = 0x0871), isf (2, ISFLIST), reserved1 (1, 1), reserved2 (4, 0xFFFFFFFF), reserved3 (4, 0xFFFFFFFF), idListNext (4, the next identifier to try for a new table) and reserved4 (2).

Feature11 (0x0872) and Feature12 (0x0878)

One table (2.4.114, 2.4.115). frtRefHeaderU (12, rt = 0x0872, or 0x0878 for Feature12, whose range is a Ref8U covering the table), isf (2, ISFLIST), reserved1 (1, zero), reserved2 (4), cref2 (2, count of Ref8U in refs2), cbFeatData (4, the size of rgbFeat; when 0, the record’s size minus the size of refs2 minus 27 bytes), reserved3 (2), refs2 (Ref8U array) and rgbFeat (a TableFeatureType). Feature12 is the same record used for a table with properties that Feature11 cannot hold: at least one of an lt of LTEXTERNALDATA, crwHeader and fSingleCell both zero, or a column with fLoadTotalFmla or fLoadTotalStr set. A Feature11 record must have none of these.

TableFeatureType (2.5.266)

The 4-byte flag word, from bit 0: unused2; fAutoFilter (1; must be 1 when fPersistAutoFilter is); fPersistAutoFilter (the AutoFilter is kept after a data refresh); fShowInsertRow (must be 1 if fInsertRowInsCells); fInsertRowInsCells (rows below shift down when the insert row shows); fLoadPldwIdDeleted; fShownTotalRow (the total row was ever visible); reserved1 (0, bit 7); fNeedsCommit (changes not synchronised; 0 unless lt is 1); fSingleCell (bit 9; the table is one cell, with no header, total row or extra columns; requires lt 2); reserved2 (bit 10); fApplyAutoFilter (the filter is currently applied); fForceInsertToBeVis (insert row forced visible because the table is empty); fCompressedXml (the cached data is compressed; lt 1); fLoadCSPName; fLoadPldwIdChanged (bit 15); bits 16 to 19 verXL (the application version that created the table: 0xB or 0xC); bit 20 fLoadEntryId; 21 fLoadPllstclInvalid; 22 fGoodRupBld (rupBuild is valid); 23 unused3; 24 fPublished (ignored when fPublishedBookItems of BookExt_Conditional12 is 0); bits 25 to 31 unused. The flags marked “0 unless lt is 1” are zero for the other source types.

Feat11FieldDataItem (2.5.113)

One column of a table:

lfxidt values (XML schema object model item types, [MSDN-SOM]): 0x1000 SCHEMA, 0x1001 ATTRIBUTE, 0x1002 ATTRIBUTEGROUP, 0x1003 NOTATION, 0x1100 IDENTITYCONSTRAINT, 0x1101 KEY, 0x1102 KEYREF, 0x1103 UNIQUE, 0x2000 ANYTYPE, 0x2100 DATATYPE, 0x2101 to 0x212D the datatypes in this order: ANYTYPE, ANYURI, BASE64BINARY, BOOLEAN, BYTE, DATE, DATETIME, DAY, DECIMAL, DOUBLE, DURATION, ENTITIES, ENTITY, FLOAT, HEXBINARY, ID, IDREF, IDREFS, INT, INTEGER, LANGUAGE, LONG, MONTH, MONTHDAY, NAME, NCNAME, NEGATIVEINTEGER, NMTOKEN, NMTOKENS, NONNEGATIVEINTEGER, NONPOSITIVEINTEGER, NORMALIZEDSTRING, NOTATION, POSITIVEINTEGER, QNAME, SHORT, STRING, TIME, TOKEN, UNSIGNEDBYTE, UNSIGNEDINT, UNSIGNEDLONG, UNSIGNEDSHORT, YEAR, YEARMONTH; 0x21FF DATATYPE_ANYSIMPLETYPE, 0x2200 SIMPLETYPE, 0x2400 COMPLEXTYPE, 0x4000 PARTICLE, 0x4001 ANY, 0x4002 ANYATTRIBUTE, 0x4003 ELEMENT, 0x4100 GROUP, 0x4101 ALL, 0x4102 CHOICE, 0x4103 SEQUENCE, 0x4104 EMPTYPARTICLE, 0x0800 NULL, 0x2800 NULL_TYPE, 0x4801 NULL_ANY, 0x4802 NULL_ANYATTRIBUTE, 0x4803 NULL_ELEMENT (each name is prefixed SOMITEM_).

Structures inside a column

Feat11WSSListInfo (2.5.119)

The link between a column and a Web data provider list (lt 1). LCID (4), cDec (4, decimal places); then 4 bytes of flags from bit 0: fPercent, fDecSet (fixed decimal point at cDec), fDateOnly, fReadingOrder (2 bits: 0 by surrounding cells, 1 left to right, 2 right to left), fRichText, fUnkRTFormatting, fAlertUnkRTFormatting, 24 bits unused; then 4 bytes: fReadOnly, fRequired, fMinSet, fMaxSet (the limits are stored in the List Data stream), fDefaultSet, fDefaultDateToday, fLoadFormula, fAllowFillIn (a choice field allows custom entries), bDefaultType (8 bits: 0 none, 1 string, 2 Boolean, 3 number; ignored unless fDefaultSet), 16 bits unused; then rgbDV (the default value, typed by the column’s lfdt: an XLUnicodeString of at most 255 characters for Text, Choice and Multiple Choices; an Xnum for Number and Currency; a 32-bit Boolean for Boolean; a DateAsNum for Date Time; absent for Note, Lookup, URL and Counter), strFormula (XLUnicodeString, the provider’s validation formula, only if fLoadFormula) and 4 reserved zero bytes.

List12 (0x0877)

Extra formatting for a table, following its Feature11 or Feature12 (2.4.157): frtHeader (12, rt = 0x0877), lsd (2: 0 the data is a List12BlockLevel, 1 a List12TableStyleClientInfo, 2 a List12DisplayName), idList (4, non-zero, the table’s idList) and rgb.

See TableStyle and DXFN12 on the styles page.

Sheet protection

The sheet’s own Protect and Password records are described on workbook settings.