Offset Offset 3

Size Size 4
Token tRef3d for external references, BIFF5:

Token tRef3d, BIFF8:

3.9.16 tArea3d (3BH, 5BH, 7BH) (BIFF5-BIFF8)
This token contains a 3D reference or an external reference to a cell range. In BIFF5 the contents of this token differs for 3D respectively external references. This token is not allowed in conditions of conditional formattings and in data validity. If this token is used in cell formulas, it prevents the creation of shared formulas. Operand class: reference. Token tArea3d for 3D references, BIFF5: Offset Size Contents 3BH (tArea3dR), 5BH (tArea3dV), 7BH (tArea3dA) This is always a negative value to indicate a 3D reference. The absolute value is the onebased index to EXTERNSHEET record (5.41) in the Local Link Table (4.10.2) containing the name of the first referenced sheet. Not used Zero-based index to first referenced sheet (FFFFH = deleted sheet) Zero-based index to last referenced sheet (FFFFH = deleted sheet) Encoded cell range address (3.3.3) Contents 3BH (tArea3dR), 5BH (tArea3dV), 7BH (tArea3dA) This is always a positive value to indicate an external reference. One-based index to EXTERNSHEET record (5.41) in the Local Link Table (4.10.2). Not used Encoded cell range address (3.3.3) Contents 3BH (tArea3dR), 5BH (tArea3dV), 7BH (tArea3dA) Index to REF entry in EXTERNSHEET record (5.41) in the Link Table (4.10.3) Encoded cell range address (3.3.4)

Size Size 8

Token tArea3d for external references, BIFF5:

Token tArea3d, BIFF8:

3.9.17 tRefErr3d (3CH, 5CH, 7CH) (BIFF5-BIFF8)
This token contains the last 3D reference or external reference to a cell in a deleted row or column. In BIFF5 the contents of this token differs for 3D respectively external references. This token is not allowed in conditions of conditional formattings and in data validity. If this token is used in cell formulas, it prevents the creation of shared formulas. Operand class: reference. Token tRefErr3d for 3D references, BIFF5: Offset Offset 3 Offset 3 Size Size 15 Size 4 Contents 3CH (tRefErr3dR), 5CH (tRefErr3dV), 7CH (tRefErr3dA) This is always a negative value to indicate a 3D reference. The absolute value is the onebased index to EXTERNSHEET record (5.41) in the Local Link Table (4.10.2). Not used Index to first referenced sheet (FFFFH = deleted sheet) Index to last referenced sheet (FFFFH = deleted sheet) Not used Contents 3CH (tRefErr3dR), 5CH (tRefErr3dV), 7CH (tRefErr3dA) This is always a positive value to indicate an external reference. One-based index to EXTERNSHEET record (5.41) in the Local Link Table (4.10.2). Not used Contents 3CH (tRefErr3dR), 5CH (tRefErr3dV), 7CH (tRefErr3dA) Index to REF entry in EXTERNSHEET record (5.41) in the Link Table (4.10.3) Not used

Token tRefErr3d for external references, BIFF5:

Token tRefErr3d, BIFF8:

3.9.18 tAreaErr3d (3DH, 5DH, 7DH) (BIFF5-BIFF8)
This token contains the last 3D reference or external reference to a cell range in deleted columns or rows. In BIFF5 the contents of this token differs for 3D respectively external references. This token is not allowed in conditions of conditional formattings and in data validity. If this token is used in cell formulas, it prevents the creation of shared formulas. Operand class: reference. Token tAreaErr3d for 3D references, BIFF5: Offset Offset 3 Offset 3 Size Size 18 Size 8 Contents 3DH (tAreaErr3dR), 5DH (tAreaErr3dV), 7DH (tAreaErr3dA) This is always a negative value to indicate a 3D reference. The absolute value is the onebased index to EXTERNSHEET record (5.41) in the Local Link Table (4.10.2). Not used Index to first referenced sheet (FFFFH = deleted sheet) Index to last referenced sheet (FFFFH = deleted sheet) Not used Contents 3DH (tAreaErr3dR), 5DH (tAreaErr3dV), 7DH (tAreaErr3dA) This is always a positive value to indicate an external reference. One-based index to EXTERNSHEET record (5.41) in the Local Link Table (4.10.2). Not used Contents 3DH (tAreaErr3dR), 5DH (tAreaErr3dV), 7DH (tAreaErr3dA) Index to REF entry in EXTERNSHEET record (5.41) in the Link Table (4.10.3) Not used
Token tAreaErr3d for external references, BIFF5:

Token tAreaErr3d, BIFF8:

3.10 Control Tokens

3.10.1 tExp (01H)

This token is used to mark that a formula cell is part of an array formula or shared formula (4.8). It is always the only token in a token array and is only allowed in the FORMULA record ( 5.50). It contains the address of the base FORMULA record for this formula range. Note that in shared formulas this address may be different to the top-left cell of the formula range (if the top left cell is not part of the shared formula). Token tExp, BIFF2: Offset 3 Offset 3 Size 1 Size 2 Contents 01H Index to row of first FORMULA record in the formula range Index to column of first FORMULA record in the formula range Contents 01H Index to row of first FORMULA record in the formula range Index to column of first FORMULA record in the formula range

Token tExp, BIFF3-BIFF8:

3.10.2 tTbl (02H)
This token is used to mark that a formula cell is part of a multiple operation table ( 4.9). It is always the only token in a token array and is only allowed in the FORMULA record (5.50). Token tTbl, BIFF2: Offset 3 Offset 3 Size 1 Size 2 Contents 02H Index to first row of the table range Index to first column of the table range Contents 02H Index to first row of the table range Index to first column of the table range

5.38 5.39 5.114 5.23

The EXTERNALBOOK Block may contain cached values of cells in the referenced document. These values are stored in several XCT and CRN records.
The EXTERNALBOOK for the own document has a special format: It contains only the number of all sheets and the byte sequence 01H 04H instead of the sheet names. The sheet range indexes in the EXTERNSHEET record refer to the position of the sheets (zero-based). If a referenced sheet does not exist anymore, the sheet index 1 will occur (deleted 3D reference). Example: A document with 7 sheets (named from Sheet1 to Sheet7) contains the formulas =Sheet2!A1, =SUM(Sheet4:Sheet6!A1:B3), =SUM([example.xls]ExtSheet1!A1:B2) (contents: A1=1.11, B1=2.22, A2=3.33, B2=4.44), =[example.xls]ExtSheet3!A1 (contents: ABCD), and =Sheet8!A1. EXTERNALBOOK 0 EXTERNALBOOK 1 Number of sheets: 7 01H 04H (own workbook) Number of sheets: 3 Document = example.xls Sheet 0 = ExtSheet1 Sheet 1 = ExtSheet2 Sheet 2 = ExtSheet3 Number of CRN = 2, sheet = 0 (ExtSheet1) Cell range = A1:B1, contents = 1.11, 2.22 Cell range = A2:B2, contents = 3.33, 4.44 Number of CRN = 1, sheet = 2 (ExtSheet3) Cell range = A1, contents = ABCD REF 0 = {EXTERNALBOOK = 0, sheet range = 11} REF 1 = {EXTERNALBOOK = 0, sheet range = 35} REF 2 = {EXTERNALBOOK = 1, sheet range = 00} REF 3 = {EXTERNALBOOK = 1, sheet range = 11} REF 4 = {EXTERNALBOOK = 0, sheet range = 11}
XCT CRN 0 CRN 1 XCT CRN 0 EXTERNSHEET
Inside of the first formula the cell reference is represented by the token tRef3d (3.9.15). The second formula contains the token tArea3d (3.9.16).
All internal names are stored in a list of DEFINEDNAME records (5.33) that follows the EXTERNSHEET record. There exist two types of internal names: global names which are valid in the whole workbook and local names which are attached to a specific sheet. For instance the local name MyCell of the sheet Sheet1 can be used from everywhere in the workbook by entering =Sheet1!MyCell. Each DEFINEDNAME record contains the name itself and a onebased sheet index. The index zero indicates a global name. If the document contains local names, a special REF entry will be created in the EXTERNSHEET record. It contains the index to the internal EXTERNALBOOK and the sheet range 22. Inside of a formula a global name or a local name of the own sheet is represented by the token tName ( 3.9.1) with a one-based index to the DEFINEDNAME record list. Local names from other sheets (with explicitly denoted sheet name) are represented by the token tNameX (3.9.14) with an index to the special REF entry of the EXTERNSHEET record and an index to the DEFINEDNAME record list. In defined names, only 3D references are allowed. The special syntax =!<reference> allowed in defined names only (<reference> being a 2D cell reference or range reference) makes it possible to refer to cells or ranges in the same sheet that contains the formula cell with the defined name. Example for internal names: A document contains the global name GlobalName, and the local names Sheet1! LocalName and Sheet2!LocalName. In Sheet1 there are the formulas =GlobalName, =LocalName, =Sheet1!LocalName, and =Sheet2!LocalName. The defined name GlobalName contains the formula =!$A$1. EXTERNALBOOK 0 EXTERNALBOOK 1 EXTERNSHEET Number of sheets: 3 01H 04H (own workbook) Number of sheets: 0 Document = <00H> (Current sheet for defined names, 2.5.9) REF 0 = {EXTERNALBOOK = 0, sheet range = 00} REF 1 = { EXTERNALBOOK = 0, sheet range = 22} REF 2 = { EXTERNALBOOK = 1, sheet range = 22} Name = GlobalName, sheet (one-based) = 0 (Global) Formula = tRef3d token to REF 2 (3.9.15) Name = LocalName, sheet (one-based) = 1 (Sheet1) Name = LocalName, sheet (one-based) = 2 (Sheet2)

4.18.1 Single Worksheet Protection (BIFF2-BIFF4)
Definition: Worksheet Protection Block Several records in the Worksheet Protection Block determine the protected items in the sheet. PROTECT protects the cell contents, WINDOWPROTECT protects the window settings, and OBJECTPROTECT protects the embedded objects. The PASSWORD record contains the hash value of the password (4.18.4) used to protect the sheet. All the mentioned records occur always, if the file was written by Excel, but may be omitted. If a protection password is set in the PASSWORD record, the read/write file protection (4.19) will be enabled, which causes encryption of the file (4.19.1) in every case. If this protection is not enabled manually while saving the file, the built-in password VelvetSweatshop is used, not the password set for worksheet protection. Structure of the Worksheet Protection Block, BIFF2-BIFF4: PROTECT WINDOWPROTECT OBJECTPROTECT PASSWORD Cell contents: 1 = protected (5.82) Window settings: 1 = protected (5.111) Embedded objects: 1 = protected (5.72) Hash value of the password; 0 = No password (5.76)
4.18.2 Sheet Protection in a Workbook (BIFF5-BIFF8)
The PROTECT record in the Worksheet Protection Block indicates that the sheet is protected. There may follow a SCENPROTECT record or/and an OBJECTPROTECT record. The optional PASSWORD record contains the hash value of the password used to protect the sheet (4.18.4). In BIFF8, there may occur additional records following the cell records in the Sheet Substream (4.2.5). Sheet protection with password does not cause to switch on read/write file protection. Therefore the file will not be encrypted. Structure of the Worksheet Protection Block, BIFF5-BIFF8: PROTECT OBJECTPROTECT SCENPROTECT PASSWORD Worksheet contents: 1 = protected (5.82) Embedded objects: 1 = protected (5.72) Scenarios: 1 = protected (5.91) Hash value of the password; 0 = no password (5.76)
4.18.3 Workbook Protection (BIFF5-BIFF8)
Definition: Workbook Protection Block The Workbook Protection Block in the Workbook Globals Substream determines the protected items in the entire workbook. The PROTECT record protects the workbook contents and the WINDOWPROTECT record protects the window settings. The PASSWORD record contains the hash value of the password used to protect the workbook ( 4.18.4). All the mentioned records occur always, if the file was written by Excel, but may be omitted. If a protection password is set in the PASSWORD record, the read/write file protection (4.19) will be enabled, which causes encryption of the file (4.19.1) in every case. If this protection is not enabled manually while saving the file, the built-in password VelvetSweatshop is used, not the password set for workbook protection. Structure of the Workbook Protection Block: WINDOWPROTECT PROTECT PASSWORD PROT4REV PROT4REVPASS Window settings: 1 = protected (5.111) Workbook contents: 1 = protected (5.82) Hash value of the password; 0 = no password (5.76) Shared workbook: 1 = protected Hash value of the shared password; 0 = no password

In each line all pixels are written from left to right. Each pixel is stored as 3-byte array: the red, green, and blue component of the colour of the pixel, in this order. The size of each line is aligned to multiples of 4 by inserting zero bytes after the last pixel. Example of the bitmap data for a image, each entry represents one byte. The three pixels of one line are stored in 9 bytes, therefore each line is expanded to 12 bytes (next multiple of 4). Offset Contents Remarks 20 R G B R G B R G B 0 Third (bottom) line of the bitmap 32 R G B R G B R G B 0 Second line of the bitmap 44 R G B R G B R G B 0 First (top) line of the bitmap

5.7 BLANK

BIFF2 0001H BIFFH BIFFH BIFFH BIFFH
This record represents an empty cell. It contains the cell address and formatting information. Record BLANK, BIFF2: Offset 4 Offset 4 Size 3 Size 2 Contents Index to row Index to column Cell attributes (2.5.13) Contents Index to row Index to column Index to XF record (5.115)
Record BLANK, BIFF3-BIFF8:
5.8 BOF Beginning of File
BIFFH BIFFH BIFFH BIFFH BIFF8 0809H
The BOF record is the first record of any kind of stream or substream: The Worksheet Stream (4.1.1) and the Chart Stream (7.1.1)
All substreams in the Workbook Stream (the Sheet Substream and the Workbook Globals Substream, 4.1.2, and the
Chart Substream, 7.1.2) The Workspace Stream. A BOF record will never be encrypted, regardless of its type and position in the stream. If a BIFF8 version of Excel (Excel 8.0 and newer) writes a BIFF5 workbook, it writes a wrong BIFF version in BOF records of the Sheet Substreams. Only the leading BOF record of the Workbook Globals Substream contains the correct value and should be used to determine the BIFF version for the entire stream.
5.8.1 BOF Records Written by Excel
Record BOF, BIFF2 (record identifier is 0009 H): Offset Size Contents BIFF version (not used) Type of the following data:
0010H = Sheet 0020H = Chart 0040H = Macro sheet
Record BOF, BIFF3 (record identifier is 0209 H) and BIFF4 (record identifier is 0409H): Offset Size Contents BIFF version (not used) Type of the following data:

Record DATATABLE, BIFF3-BIFF8:
5.25 DATATABLE2 Multiple Operation Table
BIFF2 0037H BIFF3 BIFF4 BIFF5 BIFF8
In BIFF2 this record stores information about a multiple operation table in a sheet using two independent input ranges. It follows the first FORMULA record (5.50) of the cell range containing the operation table. For more information about multiple operations see 4.9. From BIFF3 on, the DATATABLE record (5.24) contains this kind of multiple operation too. Record DATATABLE2, BIFF2: Offset 14 Size 2 Contents Cell range address of the multiple operation table range (2.5.14) 0 = Do not recalculate the table, 1 = Always recalculate the table Not used Row index of first input cell (row input cell) Column index of first input cell (row input cell) Row index of second input cell (column input cell) Column index of second input cell (column input cell)

5.26 DATAVALIDATION

BIFF2 BIFF3 BIFF4 BIFF5 BIFF8 01BEH
This record is part of the Data Validity Table (4.14). It stores data validity settings and a list of cell ranges which contain these settings. The prompt box appears while editing such a cell. The error box appears, if the entered value does not fit the conditions. The data validity settings of a sheet are stored in a list of DATAVALIDATION records which is preceded by a DATAVALIDATIONS record (5.27). If a string is empty and the default text should appear in the prompt box or error box, the string must contain a single zero character (string length will be 1). Record DATAVALIDATION, BIFF8: Offset var. var. var. var. var. var. var. var. var. var. Option flags field: Bit 3-0 Mask 0000000F H Contents Data type: 00H = Any value 04H = Date 01H = Integer values 05H = Time 02H = Decimal values 06H = Text length 03H = User defined list 07H = Formula Error style: 00H = Stop 01H = Warning 02H = Info 1 = In list type validity the string list is explicitly given in the formula 1 = Empty cells allowed 1 = Suppress the drop down arrow in list type validity 1 = Show prompt box if cell selected 1 = Show error box if invalid values entered Condition operator: 00H = Between 04H = Greater than 01H = Not between 05H = Less than 02H = Equal 06H = Greater or equal 03H = Not equal 07H = Less or equal Size 4 var. var. var. var. sz2 sz2 var. Contents Option flags (see below) Title of the prompt box (Unicode string, 16-bit string length, 2.5.3) Title of the error box (Unicode string, 16-bit string length, 2.5.3) Text of the prompt box (Unicode string, 16-bit string length, 2.5.3) Text of the error box (Unicode string, 16-bit string length, 2.5.3) Size of the formula data for first condition (sz1) Not used Formula data for first condition (RPN token array without size field, 3) Size of the formula data for second condition (sz2) Not used Formula data for second condition (RPN token array without size field, 3) Cell range address list with all affected ranges (2.5.15)

5.30 DCONREF Data Consolidation Reference
BIFFH 2do BIFFH BIFFH BIFFH BIFF8 0051H

5.31 DEFAULTROWHEIGHT

BIFF2 0025H BIFFH BIFFH BIFFH BIFFH
This record specifies the default height and default flags for rows that do not have a corresponding ROW record (5.88). Record DEFAULTROWHEIGHT, BIFF2: Offset 0 Size 2 Contents Default height for unused rows, in twips = 1/20 of a point Bit Mask Contents 7FFF H Default height for unused rows, in twips = 1/20 of a point 0-H 1 = Row height not changed manually 15
Record DEFAULTROWHEIGHT, BIFF3-BIFF8: Offset 0 Size 2 Contents Option flags: Bit Mask 0001 H H H H 3
Contents 1 = Row height and default font height do not match 1 = Row is hidden 1 = Additional space above the row 1 = Additional space below the row Default height for unused rows, in twips = 1/20 of a point

5.32 DEFCOLWIDTH

BIFF2 0055H BIFFH BIFFH BIFFH BIFFH
This record specifies the default column width for columns that do not have a specific width set using the records COLWIDTH (BIFF2, 5.20), COLINFO (BIFF3-BIFF8, 5.18), or STANDARDWIDTH (5.101). Record DEFCOLWIDTH, BIFF2-BIFF8: Offset 0 Size 2 Contents Column width in characters, using the width of the zero character from default font (first FONT record in the file). Excel adds some extra space to the default width, depending on the default font and default font size. The algorithm how to exactly calculate the resulting column width is not known. Example: The default width of 8 set in this record results in a column width of 8.43 using Arial font with a size of 10 points.

5.33 DEFINEDNAME

BIFFH BIFF3 0218H BIFFH BIFFH BIFF8 0018H
This record is part of a Link Table (4.10). It contains the name and the token array of an internal defined name. Token arrays of defined names contain tokens with aberrant token classes (3.2). Record DEFINEDNAME, BIFF2: Offset 0 Size 1 Contents Option flags: Bit Mask 02H 1 04H 2
5 5+ln 5+ln+sz Offset 6 6+ln

ln sz 1 Size ln sz

Contents 1 = Function macro or command macro 1 = Complex function (array formula or user defined) If name is function macro or command macro (see option flags above): 01H = Function macro, 02H = Command macro Keyboard shortcut (only for command macro names) Length of the name (character count, ln) Size of the formula data (sz) Character array of the name Formula data (RPN token array without size field, 3) Duplicate of the formula data size field (sz) Contents Option flags, see below Keyboard shortcut (only for command macro names, see below) Length of the name (character count, ln) Size of the formula data (sz) Character array of the name Formula data (RPN token array without size field, 3)

Record FILEPASS, BIFF8, for BIFF8 standard encryption:
Record FILEPASS, BIFF8, for BIFF8 strong encryption (available only in Excel 10.0 (Excel XP) and later): Offset Size Contents 0001 H = BIFF8 standard encryption or strong encryption not used, should be equal to next field (0002H) 0002 H = BIFF8 strong encryption Option flags: Bit Mask Contents 00000004 H Always 00000008 H 1 = Do not encrypt document properties 3 FILEPASS Info Block (see below) Size of unique document identifier (uidl) Unique document identifier used to initialise the encryption algorithm Encrypted document identifier used to verify the entered password Size of following verification digest (dl) Digest used to verify the entered password Contents Size of the following data in this block, without this field Repeated option flags from main record (see above) Not used Stream encryption algorithm identifier: 00006801 H = RC4 (Ron's Code 4) 00006802 H = SEAL (Secure Encryption Algorithm) Password hashing algorithm identifier: 00008001 H = MD2 (Message Digest 2) 00008002 H = MD4 (Message Digest 4) 00008003 H = MD5 (Message Digest 5) 00008004 H = SHA-1 (Secure Hash Algorithm) Hash key length (bits) Cryptographic provider type: 00000001 H = RSA 0000000C H = RSA SChannel 0000000D H = DSS and Diffie-Hellman 00000012 H = DH SChannel 00000018 H = RSA and AES Not used Cryptographic provider name, Unicode character array with trailing null character
10 var. var. var. var. var. Offset 8 12
var. 4 uidl uidl 4 dl Size 4 4
FILEPASS Info Block, BIFF8, for BIFF8 strong encryption:

8 var.

5.44 FILESHARING
BIFF2 BIFF3 005B H BIFF4 005B H BIFF5 005B H BIFF8 005B H
This record is part of the File Protection Block (4.19). It contains information about write protection, for instance the write protection password. The write protection state of the file is switched on with the WRITEPROT record (5.113). Record FILESHARING, BIFF2-BIFF8: Offset 4 Size var. Contents 1 = Recommend read-only state while loading the file Hash value calculated from the read-only password (4.18.4) User name of the file creator BIFF2-BIFF5: Byte string, 8-bit string length (2.5.2) BIFF8: Unicode string, 16-bit string length (2.5.3)

5.45 FONT

BIFFH BIFFH BIFFH BIFFH BIFF8 0031H
This record contains information about a used font, including character formatting. All FONT records occur together in a sequential list. Other records referencing a FONT record contain an index into this list. The font with index 4 is omitted in all BIFF versions. This means the first four fonts have zero-based indexes, and the fifth font and all following fonts are referenced with one-based indexes. Record FONT, BIFF2:

91/91//235mm 322mm 81/210mm 297mm 91/227mm 356mm 305mm 487mm 812 1211/16 210mm 330mm 148mm 210mm 182mm 257mm 322mm 445mm 174mm 235mm 201mm 276mm 420mm 594mm 297mm 420mm 322mm 445mm 200mm 148mm 105mm 148mm
Letter Rotated A3 Rotated A4 Rotated A5 Rotated B4 (JIS) Rotated B5 (JIS) Rotated Japanese Postcard Rot. Dbl. Jap. Postcard Rot. A6 Rotated
11 81/2 420mm 297mm 297mm 210mm 210mm 148mm 364mm 257mm 257mm 182mm 148mm 100mm 148mm 200mm 148mm 105mm
B6 (JIS) B6 (JIS) Rotated 1211
128mm 182mm 182mm 128mm 12 11

5.74 PALETTE

BIFF2 BIFF3 0092H BIFFH BIFFH BIFF8 0092H
This record contains the definition of all user-defined colours available for cell and object formatting. This record is optional. If it is omitted, a built-in default colour table will be used (see 5.74.3).

5.74.1 Record Contents

Record PALETTE, BIFF3-BIFF8: Offset Size 2 4nm Contents Number of following colours (nm). Contains 16 in BIFF3-BIFF4 and 56 in BIFF5-BIFF8. List of nm RGB colours (2.5.4)
5.74.2 Special Built-In Colours
Beside the colours described in a PALETTE record, Excel may use a couple of built-in colours: Colour index 00H 01H 02H 03H 04H 05H 06H 07H 08H 17H (BIFF3-BIFF4) 3FH (BIFF5-BIFF8) 18H (BIFF3-BIFF4) 40H (BIFF5-BIFF8) 19H (BIFF3-BIFF4) 41H (BIFF5-BIFF8) 43H 4DH 4EH 4FH 50H 51H 7FFF H Resulting colour or internal list index 000000H = EGA Black FFFFFFH = EGA White FF0000H = EGA Red 00FF00H = EGA Green 0000FFH = EGA Blue FFFF00H = EGA Yellow FF00FFH = EGA Magenta 00FFFFH = EGA Cyan First colour from user-defined PALETTE record or from default colour table (5.74.3) Last colour from user-defined PALETTE record or from default colour table (5.74.3) System window text colour for border lines (used in records XF 5.115, CFRULE 5.16, and WINDOW2 (BIFF8 only), 5.110) System window background colour for pattern background (used in records XF, and CFRULE) System face colour (dialogue background colour) System window text colour for chart border lines System window background colour for chart areas Automatic colour for chart border lines (seems to be always Black) System tool tip background colour (used in note objects) System tool tip text colour (used in note objects) System window text colour for fonts (used in records FONT 5.45, FONTCOLOR 5.47, and CFRULE)

BIFFH BIFF3 0041H BIFFH BIFFH BIFF8 0041H
This record stores the position of window panes. It is part of the Sheet View Settings Block (4.5). If the sheet does not contain any splits, this record will not occur. A sheet can be split in two different ways, with unfrozen panes or with frozen panes. A flag in the WINDOW2 record (5.110) specifies, if the panes are frozen, which affects the contents of this record. Record PANE, BIFF2-BIFF8: Offset 0 Size 2 Contents Position of the vertical split (px, 0 = No vertical split): Unfrozen pane: Width of the left pane(s) (in twips = 1/20 of a point) Frozen pane: Number of visible columns in left pane(s) Position of the horizontal split (py, 0 = No horizontal split): Unfrozen pane: Height of the top pane(s) (in twips = 1/20 of a point) Frozen pane: Number of visible rows in top pane(s) Index to first visible row in bottom pane(s) Index to first visible column in right pane(s) Identifier of pane with active cell cursor (see below) Not used (BIFF5-BIFF8 only, not written in BIFF2-BIFF4)
If panes are unfrozen, every visible pane may be active. The active pane shows the current selection (which may be different in each pane) and reacts on cursor movement and other user input. If the panes are frozen, the bottom pane (if no horizontal split present), right pane (if no vertical split present), or bottom-right pane is always active, regardless of the cursor position. The correct identifiers for all possible combinations of visible panes are shown in the following pictures. px = 0, py = 0 px = 0, py > px > 0, py = 0 px > 0, py > 1

5.76 PASSWORD

BIFFH BIFF3 0013H BIFFH BIFFH BIFF8 0013H
This record is part of the worksheet/workbook protection (4.18). It stores a 16-bit hash value, calculated from the worksheet or workbook protection password. Record PASSWORD, BIFF2-BIFF8: Offset 0 Size 2 Contents 16-bit hash value of the password (4.18.4)

5.77 PHONETICPR

BIFF2 BIFF3 BIFF4 BIFF5 BIFF8 00EF H
This record contains default settings for the Asian Phonetic Settings dialog and the addresses of all cells which show Asian phonetic text. Record PHONETICPR, BIFF8: Offset Size Contents Index to FONT record (5.45) used for Asian phonetic text of new cells Additional settings used for Asian phonetic text of new cells: Bit Mask Contents 0003 H Type of Japanese phonetic text: 1-= Katakana (narrow) 102 = Hiragana 012 = Katakana (wide) 000C H Alignment of all portions of the Asian phonetic text: 3-= Not specified (Japanese only) 102 = Centered 012 = Left (Top for vertical text) 112 = Distributed 0030 H 112 (always set) 5-4 Cell range address list (2.5.15) with all cells with visible Asian phonetic text

Record WRITEACCESS, BIFF5:
Record WRITEACCESS, BIFF8:

5.113 WRITEPROT

This record is part of the File Protection Block (4.19). It does not contain any data. If present it specifies that the file is write protected. The write protection password is stored in the FILESHARING record (5.44).

5.114 XCT CRN Count

This record stores the number of immediately following CRN records. These records are used to store the cell contents of external references. See 4.10 for details about external references. Record XCT, BIFF3-BIFF5: Offset 0 Offset Size 2 Size Contents Number of following CRN records (5.23) Contents Number of following CRN records (5.23) Index into sheet table of the involved EXTERNALBOOK record (5.38)

Record XCT, BIFF8:

5.115 XF Extended Format
BIFFH BIFFH BIFFH BIFF5 00E0 H BIFF8 00E0H
This record contains formatting information for cells, rows, columns or styles.

5.115.1 XF Substructures

From BIFF3 on, some of the elements occur unchanged in every BIFF version. These elements are described in the following using a specific name for each element. In the description of the record structure the names are used to reference to these tables.
XF_TYPE_PROT XF Type and Cell Protection (3 Bits), BIFF3-BIFF8
Bit 2 Mask 01H 02H 04H Contents 1 = Cell is locked 1 = Formula is hidden 0 = Cell XF; 1 = Style XF
These 3 bits are part of a specific data byte.
XF_USED_ATTRIB Attributes Used from Parent Style XF (6 Bits), BIFF3-BIFF8
Each bit describes the validity of a specific group of attributes. In cell XFs a cleared bit means the attributes of the parent style XF are used (but only if the attributes are valid there), a set bit means the attributes of this XF are used. In style XFs a cleared bit means the attribute setting is valid, a set bit means the attribute should be ignored. Bit 4 5
Mask 01H 02H 04H 08H 10H 20H
Contents Flag for number format Flag for font Flag for horizontal and vertical alignment, text wrap, indentation, orientation, rotation, and text direction Flag for border lines Flag for background area style Flag for cell protection (cell locked and formula hidden)
XF_HOR_ALIGN Horizontal Alignment (3 Bits), BIFF2-BIFF8
Value Horizontal alignment General Left Centred Right Filled Justified (BIFF4-BIFF8) Centred across selection (BIFF4-BIFF8) Distributed (BIFF8, available in Excel 10.0 (Excel XP) and later only) 219
The horizontal alignment consists of 3 bits and is part of a specific data byte.
XF_VERT_ALIGN Vertical Alignment (2 or 3 Bits), BIFF4-BIFF8
The vertical alignment consists of 2 bits (BIFF4) or 3 bits (BIFF5-BIFF8) and is part of a specific data byte. Vertical alignment is not available in BIFF2 and BIFF3. Value 4

Record XF, BIFF8: Offset 4 Size 2 Contents Index to FONT record (5.45) Index to FORMAT record (5.49) XF type, cell protection, and parent style XF: Bit Mask Contents 0007 H XF_TYPE_PROT XF type, cell protection (see above) 2-0 FFF0 H Index to parent style XF (always FFF H in style XFs) 15-4 Alignment and text break: Bit Mask Contents 07H 2-0 XF_HOR_ALIGN Horizontal alignment (see above) 08H = Text is wrapped at right border 70H 6-4 XF_VERT_ALIGN Vertical alignment (see above) 80H = Justify last line in justified or distibuted text XF_ROTATION: Text rotation angle (see above) Indentation, shrink to cell size, and text direction: Bit Mask Contents 0FH 3-0 Indent level 10H = Shrink content to fit into cell C0H 7-6 Text direction: 0 = According to context35; 1 = Left-to-right; 2 = Right-to-left Flags for used attribute groups: Bit Mask Contents FCH 7-2 XF_USED_ATTRIB Used attributes (see above) Cell border lines and background area: Bit Mask Contents 0000000F H Left line style (2.5.11) 3-0 000000F0 H Right line style (2.5.11) 7-4 00000F00 H Top line style (2.5.11) 11-8 0000F000 H Bottom line style (2.5.11) 15-12 007F0000 H Colour index (5.74) for left line colour 22-16 3F800000 H Colour index (5.74) for right line colour 29-H 1 = Diagonal line from top left to right bottom H 1 = Diagonal line from bottom left to right top 31 Bit 6-0 13-7 20-14 24-21 31-26 Bit 6-0 13-7 Mask 0000007F H 00003F80 H 001FC000 H 01E00000 H FC000000 H Mask 007F H 3F80 H Contents Colour index (5.74) for top line colour Colour index (5.74) for bottom line colour Colour index (5.74) for diagonal line colour Diagonal line style (2.5.11) Fill pattern (2.5.12) Contents Colour index (5.74) for pattern colour Colour index (5.74) for pattern background
Left-to-right, if the text starts with a character from a left-to-right script, or right-to-left, if the text starts with a character from a right-to-left script.

6 Drawing Objects

Drawing Objects

7 Charts

Charts

7.1 Document Integration

This chapter describes how a chart is integrated into a spreadsheet document.
7.1.1 Chart Document (BIFF2-BIFF4)
Definition: Chart document A BIFF2-BIFF4 chart document is a special type of a worksheet document ( 2.1.2) that contains exactly one chart. The default file extension of chart documents is XLC. The source data may be embedded in the chart or linked to another worksheet document. Definition: Chart Stream The entire chart document consists of the Chart Stream (similar to the Worksheet Stream in a regular worksheet document, 2.3.3, 4.1.1). The BOF record of the stream contains the type identifier of a chart stream. Records in the Chart Stream, BIFF2-BIFF4: BOF EOF Type = chart (5.8) Chart records

Record CHAREAFORMAT, BIFF8: Offset Size Contents Not used (Excel writes RGB colour of the pattern, but ignores it on import) Not used (Excel writes RGB colour of the pattern background, but ignores it on import) Fill pattern (2.5.12) Additional flags (see above) Colour index (5.74) for pattern colour Colour index (5.74) for pattern background

7.7 CHAXISLINE

BIFFH 2do BIFFH BIFFH BIFFH BIFF8 1021H
7.8 CHBEGIN Begin of Block
BIFF2 1033H BIFFH BIFFH BIFFH BIFFH
This record has no content. It indicates the begin of a chart record block (7.2.2).

7.9 CHEND End of Block

BIFFH BIFFH BIFFH BIFFH BIFF8 1034H
This record has no content. It indicates the end of a chart record block (7.2.2).

7.10 CHFRAME

BIFF2 1032H BIFFH BIFFH BIFFH BIFFH
This record is the header record of a CHFRAME Block (7.3.3) that describes the border and area formatting of a chart object. Record CHFRAME, BIFF2-BIFF8: Offset Size Contents Frame format: 0 = Standard, 4 = Shadowed Option flags: Bit Mask Contents 0001 H 1 = Frame size is calculated automatically H 1 = Frame position is calculated automatically 1

7.11 CHLINEFORMAT

BIFFH BIFFH BIFFH BIFFH BIFF8 1007H
This record describes the formatting attributes of a line or a border. Record CHLINEFORMAT, BIFF2-BIFF5: Offset Size Contents RGB colour of the line (2.5.4) Line pattern: 0000H = Solid 0005H = None (invisible) 0001H = Dashed 0006H = 25% grey pattern 0002H = Dotted 0007H = 50% grey pattern 0003H = Dash-dotted 0008H = 75% grey pattern 0004H = Dash-dot-dotted Line weight: FFFFH = Hairline (BIFF3-BIFF8 only) 0000H = Thin 0001H = Medium 0002H = Thick Additional flags: Bit Mask Contents 0001 H 1 = Automatic line format, ignore all other fields above H 1 = Axis is enabled (only used in CHAXISLINE Block, 7.3.7) 2
Record CHLINEFORMAT, BIFF8: Offset 10 Size 2 Contents Not used (Excel writes RGB colour of the line, but ignores it on import) Line pattern (see above) Line weight (see above) Additional flags (see above) Colour index (5.74) for line colour

7.12 CHSOURCELINK

BIFF2 1004H BIFFH BIFFH BIFFH BIFFH
This record links a chart object to cells in a sheet. It is used to specify the source of data series or to link text boxes (for instance the chart title) to a cell. The record is always part of a CHSOURCELINK Block (7.3.4). Record CHSOURCELINK, BIFF2-BIFF4: 2do Record CHSOURCELINK, BIFF5-BIFF8: Offset Size Contents Link target: Link type: 0 = Series title or text box 2 = Category names for a series 1 = Values for a series 3 = Bubble size values (BIFF8) 0 = No link present, use generated default values 1 = Constant text or values 2 = Linked to worksheet
Option flags: Flag Mask 0001 H 0

OpenOffice.org HowTo:

Formula Command Reference
Formula Command Reference 0.4 First edition: 05 June 2003 First English edition: 05 June 2003

Contents

Table of Contents
Contents.... 2 Overview.....3 Copyright and trademark information....3 Feedback..... 3 Acknowledgments....3 Modifications and updates.... 4 Command Reference....5 Commands.....5 Unary / Binary Operators....5 Relational Operators.... 6 Set Operations.... 7 Functions..... 8 Operators..... 9 Attributes.... 10 Others..... 12 Brackets..... 13 Formats.... 14 Characters.....15 Greek.... 15 Special..... 15
OpenOffice.org Formula Command Reference

Overview

OpenOffice.org's burgeoning popularity has increased the need for a comprehensive set of HowTos to aid users.
Copyright and trademark information
Copyright 2003 Fred Saalbach. Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Lesser General Public License, Version 2.1. All trademarks within this guide belong to legitimate owners.

Feedback

Please direct any comments or suggestions about this document to: dev@documentation.openoffice.org

Acknowledgments

This document was inspired by the de.OpenOffice.org German language Formula How-To. Layout is in accordance with the OpenOffice.org Style Guide for U.S. Documentation
Modifications and updates
This is the first edition. Place any modifications and updates in this section. Fred Saalbach 27 Mar 2004

Document Revision

06/05/03

Description of Change

Initial edition issued for comment Added table Commands, attributes continued. showing font colors, and commands switching from serif to sans serif fonts. Fred Saalbach Minor corrections to additions to above. Ian Laurenson Revised table of contents. Fred Saalbach

0.2 0.3 0.4

03/27/04 03/31/04 04/04/04

Command Reference

Commands

Unary / Binary Operators

Operation
+sign -sign +/- sign -/+ sign Boolean not Addition + Multiplication dot Multiplication (X) Multiplication (*) Boolean and Subtraction (-) Division (fraction) Division (operand) Division (slash) Boolean or Concatenate

Command

+1 -1 +-1 neg 1 neg a a+b a cdot b a times b a*b a and b a-b a over b a div b a/b a or b a circ b

Display

ab ab ab ab

ab a /b ab ab

Table 1Commands, unary & binary.Relations

Relational Operators

Is equal Is not equal Approximately Divides Does not divide Less than Greater than Similar to or equal Parallel Orthogonal to Less than or equal to Greater than or equal to Similar to Congruent Less than or equal to Greater than or equal to Proportional Toward Arrow left Double arrow left and right Arrow right Table 2Commands, relations.
a=b a <> b a approx 2 a divides b a ndivides b a<2 a>2 a simeq b a parallel b a ortho b a leslant b a geslant b a sim b a equiv b a <= b a >= b a prop b a toward b a dlarrow b a dlrarrow b a drarrow b
ab ab ab a~b ab ab ab a b a b ab ab ab

Set Operations

Is in Is not in Owens Empty set Intersection Union Difference Quotient Aleph Subset Subset or equal to Superset Superset or equal to Not subset Not subset or equal Not superset Not Superset or equal Natural Numbers Set Set of Integers Set of rational numbers Set of real numbers Set of complex numbers Table 3Commands, set operators.
a in b a notin b a owns b emptyset a intersection b a union b a setminus b a slash b aleph a subset b a subseteq b a supset b a supseteq b a nsubset b a nsubseteq b a nsupset b a nsupseteq b setN setZ setQ setR setC

a b a b a b

ab ab a b a /b

ab ab ab ab ab ab ab ab

Functions
Exponential Natural logarithm Exponential function Logarithm Power Sine Cosine Tangent Cotangent Square root Arcsine Arc cosine Arctangent Arc cotangent n root Hyperbolic sine Hyperbolic cosine Hyperbolic tangent Hyperbolic cotangent Absolute value Arc hyperbolic sine Arc hyperbolic cosine Arc hyperbolic tangent Arc hyperbolic cotangent factorial Table 4Commands, function.
func e^{a} ln(a) exp(a) log(a) a^{b} sin(a) cos(a) tan(a) cot(a) sqrt{a} arcsin(a) arccos(a) arctan(a) arccot(a) nroot{a}{b} sinh(a) cosh(a) tanh(a) coth(a) abs{a} arsinh(a) arccosh(a) arctanh(a) arccoth(a) fact(a)

ln a exp a log a

sin a cos a tana cot a
arcsin a arccos a arctan a arccot a
sinh a cosh a tanh a coth a
arsinh a arcosh a artanh a arcoth a a!
Operators All operators can be used with the limit functions (from and to)
Limit Sum Product Coproduct Limits from and to (shown with intigral) Intigral Double intigral Tripple Intigral Lower limit shown with summation symbol Curved intigeral Double curved intigeral Tripple curved intigeral Upper limit shown with product symbol Table 5Commands, operators.
Lim(a) sum(a) prod(a) coprod(a) int from {r_0} to {r_t} a
int{a} iint{a} iiint{a} sum from{3}b lint a llint a lllint a prod to{3} r

a a a b

Attributes
Acute accent Grave accent Reverse circumflex Breve Circle Vector arrow Tilde Circumflex Line above Dot Wide vector arrow Wide tilde Wide circumflex Double dot Line over Line under Line through Ripple dot Transparent (useful to get a placeholder of a given size) Bold font Italic font
acute a grave a check a breve a circle a vec a tilde a hat a bar a dot a widevec abc widetilde abc widehat abc ddot overline abc Underline abc overstrike acb dddot a phantom a bold a

ital a

a a a a a a a a a a

abc abc abc

abc abc acb
Resize font Table 6Commands, attributes.

size 16 qv

1 Unquoted text that isn't a command is considered to be a variable. Variables are, by default, italicized. OpenOffice.org Formula Command Reference 10

Attributes Continued

Following item in sans serif font2 Following item in serif font Following item in fixed font Make color of following text cyan Make color of following text yellow Make color of following text green Make color of following text blue Make color of following text white Make color of following text red Make color green returns to default color black Brace items to change color of more than one item
font sans qv font serif qv font fixed qv color cyan qv color yellow qv color green qv color blue qv color white qv color red qv color green X qv color green {X qv}

qv qv qv

X qv X qv
Table 7Commands, attributes - continued.
2 There are three custom fonts, sans serif (without kicks), serifs (with kicks), and fixed (non proportional). To change the actual fonts used for custom fonts and the fonts used for variables (unquoted text), numbers and functions, use: Format > Fonts. OpenOffice.org Formula Command Reference 11

Others

Infinity Partial Nabla There exists For all H bar Lambda bar Real part Imaginary part Weierstrss p Left arrow Right arrow Up arrow Down arrow Dots at bottom Dots at middle Dots vertical Dots diagonal upward Dots diagonal downward Table 8Commands, others.
infinity partial nabla exists forall hbar lambdabar re im wp leftarrow rightarrow uparrow downarrow dotslow dotsaxis dotsvert dotsup dotsdown

Brackets

Round Brackets Square Brackets Double Square Brackets Single line Double line Braces Angle Brackets Operator Brackets Group brackets (used for program control) Round brackets scalable (To make brackets scalable add the word left before a left bracket and right before a right bracket Square brackets scalable Double square brackets scalable Line scalable Double line scalable Brace scalable Angle bracket scalable Operator brackets scalable Over brace scalable Under brace scaleable
(a) [b] ldbracket c rdbracket lline a rline ldline a rdline lbrace w rbrace langle d rangle langle a mline b rangle {a} left ( stack{a # b # z} right )

a [b] c a a {w} d ab

left [ b right ] left ldbracket c right rdbracket left lline a right rline left ldline d right rdline left lbrace e right rbrace left langle f right rangle left langle g mline h right rangle {The brace is above} overbrace a {the brace is below}underbrace {f}

[ b] c

d {e } f gh
The brace is above the brace is below

Table 9Commands, braces.

Formats
Left Superscript Center Superscript
a lsup{b} sum(a)a csup{b}
Right Superscript Left subscript Center subscript Right subscript Align character to left Align character to center Align character to right Vertical stack of 2 Vertical stack, more than 2
a^{b} a lsub{b} a csub{b} a_{b} (alignl(a)) (alignc(a)) (alignr(a)) binom{a}{b} stack{a # b # z}

a a a

a b a b z a c

Matrix stack

matrix{a # b ## c # d}
Common mathematical arrangement New Line Small gap (apostrophe) Large gap (tilde) Table 10Commands, formats.

matrix{a # "="b ## {} # "="c}
asldkfjo newline sadkfj stuff`stuff stuff~stuff

asldkfjo sadkfj

stuff stuff stuff stuff

Characters

%ALPHA

%DELTA

%BETA B

%EPSILON

%LAMBDA

%GAMMA

%KAPPA %IOTA I

%OMEGA

%NU N %PSI %XI

%OMICRON

%PHI %THETA

%MU M %PI %UPSILON

%RHO P %ZETA Z %chi %iota %omega %sigma %varphi %xi

%SIGMA

%alpha
%delta %kappa %omicron %tau %varpi %zeta
%epsilon %lambda %phi %theta %varrho
%eta %mu %pi %upsilon %varsigma
%gamma %nu %rho %varepsilon

%vartheta

Table 11Characters, Greek.

Special

%angle %noelement %notequal %element %or %identical

%perthousand

%infinite %strictlygreatert han

%strictlylessthan

%tendto
Table 12Characters, special.