.XLS File Format – Cells and rows

The Cell header

Most cell records start with this 6-byte Cell structure (section 2.5.19):

Value records

Two records pack a run of cells in one row: MulRk (0x00BD) holds the row, the first column, then for each cell a 6-byte RkRec (2-byte XF index and 4-byte RK number), and finally the last column; MulBlank (0x00BE) holds the row, first column, one 2-byte XF index per cell and the last column. The number of entries is last − first + 1, and the first column is at most 254.

RK numbers

An RK number (section 2.5.217) packs common numbers into 4 bytes:

For example 0x3FF00000 has both flags clear: it is the double 0x3FF0000000000000, 1.0. 0x00000193 has fInt and fX100 set and num = 100, so it is 100 ÷ 100 = 1.0 as well; 0x00000192 is the integer 100.

Error codes

Used by BoolErr, cached formula results and formula error constants (section 2.5.10):

Formula (0x0006)

The value (FormulaValue, section 2.5.133) is a double unless its last two bytes are 0xFFFF. Then byte 0 gives the type: 0 text, held in a String record (0x0207, an XLUnicodeString) that follows the Formula record; 1 Boolean, in byte 2; 2 error, code in byte 2; 3 empty text.

A formula shared by several cells, an array formula and a data table store their definition once in a following ShrFmla (0x04BC), Array (0x0221) or Table (0x0236) record; each cell’s own formula is then just a PtgExp or PtgTbl naming the top-left cell.

Array (0x0221)

An array formula for a range (2.4.4). It follows the Formula record of the first cell of the range; every other cell of ref has its own Formula record (not necessarily next to it) whose formula begins with PtgExp, and every cell of ref must have one.

ShrFmla (0x04BC)

A formula shared by many cells, a size optimisation (2.4.260). It follows the Formula record of the first cell of the range; the other cells’ Formula records have fShrFmla set and a cell inside ref.

Table (0x0236)

A what-if data table (2.4.319). It follows the Formula record of the first cell of the table; the other cells of ref have Formula records beginning with PtgTbl, and each cell of ref belongs to the table. The first row and column of ref are 1 or more, because the input values lie above and to the left.

For a one-variable table the input cell lies outside the rows rwFirst - 1 to rwLast and columns colFirst - 1 to colLast, or equals (rwFirst - 1, colFirst - 1). For a two-variable table, the row input cell and the column input cell may not both lie inside those bounds in the same dimension: if colInpCol is within colFirst - 1 to colLast, then rwInpCol is outside rwFirst - 1 to rwLast, and the reverse.

String (0x0207)

The text result of a formula (2.4.268), written straight after that Formula record (after any Array, ShrFmla or Table record) when its value type is 0. One field: string, an XLUnicodeString of at most 32,767 characters, and it may be continued in Continue records like any long string.

Rows, columns and the used range

Fonts, number formats and XF records

The globals substream holds the formatting tables that cells point into.

Font (0x0031)

The first four Font records must be the default font, then bold, italic, and bold italic. A font index below 4 counts from 0; above 4 it counts from 1, so index 4 is never used and index 5 means the fifth Font record (section 2.5.129, FontIndex).

Format (0x041E)

A 2-byte number format id and its format string (XLUnicodeString, 1 to 255 characters). An XF’s format id is either a built-in format, listed in ECMA-376 Part 1 section 18.8.30 and shared with XLSX, or a custom format defined by a Format record with an id from 164 to 392 (0x00A4–0x0188; Excel 97 to 2010 also write 383 to 392). Format records may also carry the locale-dependent built-in ids 5–8, 23–26, 41–44 and 63–66 (sections 2.4.126 and 2.5.165).

XF (0x00E0)

Colour indexes

Colour indexes (section 2.5.161, Icv): 0 to 7 are fixed (black, white, red, green, blue, yellow, magenta, cyan); 8 to 63 are the palette, taken from the Palette record if there is one; 0x40 default foreground (window text), 0x41 default background (window), 0x4D and 0x4E chart foreground and background, 0x4F chart neutral (black), 0x51 tooltip text, 0x7FFF automatic font colour. The default palette, as red, green, blue: