5. The spreadsheet formula editor


As of XMLmind XML Editor v5.8, the integrated spreadsheet engine is implemented as an add-on. Therefore, what follows is available only when add-on "Integrated spreadsheet engine" has been installed.


Type a formula in this text area.

Unlike in most spreadsheet software,

  • A formula can contain spaces.

  • Use of local variables and intermediate formulas is allowed.

  • Comment lines are allowed and must start with '#'.

  • Use newlines to separate intermediate formulas and comment lines.


vat = `document("tutorial/VATrates.html#france_vat", .)`
# Trim the '%' sign at the end of vat.
= left(vat, len(vat) - 1)


Do not use names for your local variables that look like cell references. Example: 'x' will work, but not 'x1'. That is, [a-zA-Z][a-zA-Z]*[1-9][1-9]* (as well as true/TRUE/false/FALSE — the formula language is case insensitive) are reserved identifiers.

Help about selected function

Select a function name in the Formula text area and click on this button to switch to the other tab and display online help about the selected function. Shortcut: F1.

Evaluate selected text

Select an expression in the Formula text area and click on this button to evaluate it and display a dialog box containing the result of this evaluation. Shortcut: F2.

This is handy if you want to experiment with a predefined function you don't really understand.

Evaluate formula

Click on this button to evaluate the whole formula and display a dialog box containing the result of this evaluation. Shortcut: F3.


The format fields are useful to separate calculation from formatting.

Use case: let's suppose your formula computes an amount of money. You need to insert in the document this amount nicely formatted, preceded by string "Total: " and followed by string " (excluding taxes)".

Of course, this can be done like this:

amount = ...
= "Total: " & numbervalue(amount, "0.00") & " (excluding taxes)"

An alternative is to use the Format fields. First field contains a prefix which is prepended to the formatted result. Second field contains a date or number format used to format the raw result. A combobox allows to specify the locale used to interpret this format. Third field contains a suffix which is appended to the formatted result.

Therefore, specify "Total: " in the first field. Choose "#,##0.00" from the combobox. Specify " (excluding taxes)" in the third field.

Change value of attribute

A formula computes a value. This value can be used to add/replace the text node immediately after the formula or this value can be used to add/replace an attribute of the element containing the formula.

If you want to use a formula to change an attribute, check this toggle and use the associated combobox to specify the name of this attribute.


Check this toggle to disable the formula being edited.

Disabling a formula means passivating it. That is, it is no longer used to update the document. In some cases, this may be a handy alternative to removing it.


In the styled view, formulas are represented by a small F icon. Clicking on this icon with the middle button of the mouse allows to switch the state of the formula from enabled to disabled and vice-versa.