FORMULA Function (LibreOffice Calc)

FORMULA retrieves the underlying formula text from a referenced cell, instead of returning the result of that formula. It is useful for inspecting formulas, documenting calculation logic and spotting differences between similar rows.

=FORMULA(Reference)

If cell B2 contains the formula =SUM(1;2;3) (which calculates 6), put the following in C2:

=FORMULA(B2)

The returned value is a string representing B2’s formula, not the number 6. The current LibreOffice Help displays the example string with semicolons. Native Calc 25.2.3.2 in the tested headless UNO environment returned =SUM(1,2,3) (commas) from the same source. Both illustrate a key OpenFormula rule: the returned string’s serialization is implementation-defined. See LibreOffice Information Functions Help (FORMULA) and OASIS OpenFormula 1.4 §6.13.12, checked 2026-10-13.

A spreadsheet cell has a stored formula and a calculated result. =SUM(1;2;3) has a result of 6. FORMULA reads the stored calculation expression and makes it available as text in another cell. The returned value is already text; the original calculation cell remains unaffected. The function is especially useful when auditing a workbook that repeats the same kind of calculation over many rows.

This is not the same as TEXT, which formats a value, nor the same as ISFORMULA, which simply checks whether a cell contains a formula. FORMULA’s argument is a cell reference, not an arbitrary formula-like text string. A cell containing the text =SUM(1;2;3) is not necessarily a formula cell; the distinction matters for imported CSV data and formulas pasted with a leading apostrophe.

The native tests confirmed that FORMULA can read a formula whose calculated value itself is an error: a source =NA() still produced the textual string =NA(); a source =1/0 returned =1/0. It does not automatically propagate those source errors, consistent with OpenFormula 1.4 §6.13.12.

FORMULA(Reference)

No optional parameters or default arguments exist. In the tested Calc build, =FORMULA() produced Err:511, and a two-argument call produced Err:508. Passing a multi-cell range returned #VALUE! in one native diagnostic; a one-cell reference is the documented, portable input. Some unusual arguments (such as INDEX returning a reference) may work in the tested build, but that behavior is not established as universal in every spreadsheet program or edition.

ODF/OpenFormula name: §6.13.12 of OpenDocument 1.4 defines intrinsic FORMULA(Reference X) with return type String. The exact representation of the returned string is implementation-defined; do not assume semicolons, commas, capitalization or whitespace necessarily match the user’s original typing. The ODF definition is explicitly intended for debugging and says the function does not propagate the referenced cell’s error value.

1. Open LibreOffice Calc and enter the formula =SUM(1;2;3) into cell B2. Press Enter: B2 evaluates to 6.
2. Enter =FORMULA(B2) in C2. You should see a text representation of B2’s formula, beginning with =.
3. Edit B2 to =SUM(5;10) and recalculate. C2 should reflect the formula’s text, not the new result 15. LibreOffice Help states that FORMULA is recalculated on recalculation, although refreshing/serialization behavior can depend on workbook and calculation settings.

In the headless LibreOffice 25.2.3.2 native test, the first source formula was stored as =SUM(1;2;3) but FORMULA’s output was the text =SUM(1,2,3). In another locale, UI configuration, or newer release, the displayed delimiters may differ. Do not treat a formatting difference as numerical corruption. See the detailed locale discussion below.

Small reproducible comparison table

Create the following source expressions in column B, then enter =FORMULA(Bn) into corresponding cells in column C. The rightmost outputs below are native observations from the specifically tested 25.2.3.2 environment, not a promise for every locale.

The exact source-cell addresses correspond to the saved LibreOffice_FORMULA_Native_Validation.ods. The FORMULA tests are based on 31 actual native output strings, not text copied from Excel or generated by a mock spreadsheet interpreter.

Assume B2 contains =SUM(1;2;3) and evaluates to 6:

Textual formula inspection is useful for auditing but not automatically authoritative as a durable expression snapshot: a linked cell’s formula can be edited, and the FORMULA result will normally update on recalculation. To keep a fixed audit record, copy the resulting text and paste as values, recording the workbook version or date. A pasted value is no longer dynamically connected to the source formula.

LibreOffice Help states that if Reference is invalid or its cell does not contain a formula, FORMULA returns #N/A. This was confirmed natively for a plain number, plain text and an empty cell. An expression that evaluates to a number, such as =FORMULA(2+2), also produced #N/A in the tested build because it did not supply a qualifying formula-containing cell reference. A string such as =FORMULA("Examples.B2") returned #N/A in the same diagnostic run. The malformed-reference expression =FORMULA(#REF!) instead returned #REF!; the Help wording does not describe every parser-error case.

Safer inspection pattern for users who would prefer a friendly message:

=IF(ISFORMULA(B2);FORMULA(B2);"No formula in B2")

ISFORMULA and the conditional are documented Calc functions. This whole combined expression is an editorial suggested pattern rather than one of the 31 direct native FORMULA assertions; its behavior should be checked in the reader’s Calc edition. The separate direct native checks and official function specifications support the intended control flow.

Alternatively, return a text note for invalid input:

=IFERROR(FORMULA(B2);"Not a formula cell")

This is a similar documentation-derived combined pattern (not individually executed in the saved native workbook). It also catches other errors that might arise from the function, so use ISFORMULA when you specifically want to distinguish formula storage from general evaluation errors.

Suppose a budget table calculates totals down column B. A typical calculated total might be =SUM(D2:F2) in B2, =SUM(D3:F3) in B3, and so on. Add =FORMULA(B2) in C2, then fill downward to view the expressions as text. A surprising literal value, different function name or unexpected cell reference can become easier to spot.

Avoid a naïve direct text comparison between different rows. The formulas for rows 2 and 3 are supposed to have different addresses, so comparing their text strings character-for-character may identify harmless differences. Audit meaning and references, not just exact text equality. For a fixed reference pattern, you can visually inspect rows or use a separately validated normalization strategy; this guide does not claim that a universal text replacement detects all spreadsheet errors.

Inspect a formula on a different sheet

The native validation workbook contains a source formula on its Examples sheet. From another sheet, the diagnostic expression:

=FORMULA(Examples.B2)

returned =SUM(1,2,3). Reference notation can vary with locale, sheet name and whether the name contains spaces; quote or escape sheet names according to your Calc environment. If a sheet name has spaces, rely on Calc’s own cell-selection interface to insert the correct cross-sheet reference instead of guessing punctuation.

Combine with ISFORMULA for a review dashboard

A workbook audit can expose two independent facts: whether a cell contains a formula, and what the formula is. One column can hold =ISFORMULA(B2) and another can hold =IFERROR(FORMULA(B2);""). The result allows scanning for hard-coded values in columns that should be calculated. However, beware that valid manual-input rows may be intentional. Treat flagged cells as a review queue, not proof of an error.

Text extraction is not formula execution

FORMULA returns text; the returned string itself is not evaluated into a new numerical result. For example, if B2 contains =SUM(1;2;3), =FORMULA(B2) does not compute the SUM again independently or verify the formula’s numerical correctness. Nor does the returned text include every workbook dependency, named range definition, external link permission or macro needed to reproduce the calculation elsewhere. Never paste or execute formula text from an untrusted workbook without reviewing it.

The LibreOffice Help example explicitly shows a string like =SUM(1;2;3) when a cell contains that formula. In our native 25.2.3.2/Linux/UNO run, cell.Formula exposed the semicolon-separated source expression, but =FORMULA(cell) returned a string with comma-separated function arguments. For example:

The final example is especially important: semicolons within quoted text remained semicolons, while argument delimiters were represented as commas. An audit tool must not globally replace all punctuation without parsing strings correctly. The revised test script independently models only this observed delimiter convention and checks 31/31 returned source-formula strings. This checks serialization/property consistency, not independent recalculation of SUM, IF or other embedded functions.

The OASIS OpenFormula 1.4 §6.13.12 explicitly states that the specific syntax of the returned text is implementation-defined, so the observed delimiter difference is not by itself evidence of an OpenFormula violation. A different Calc language pack, version, cell interface or import/export path might produce different text. Do not represent a locale-specific test as a universal transformation rule or rely on a returned string being immediately pasteable in every user installation.

What if a source cell contains a formula that returns an empty string?

A formula such as ="" is still a formula even though the cell may look blank. The saved native test confirmed FORMULA returned the formula text for ="". Do not rely on an empty-looking display to determine whether the cell contains a formula; use ISFORMULA and inspect the formula bar.

Does FORMULA work on errors?

OpenFormula explicitly says FORMULA does not propagate the referenced cell’s error value. Native testing confirmed this for sources =NA() and =1/0. However, an invalid reference or a non-formula reference produces an error as documented. These are different cases: a cell can contain a valid formula expression whose computation happens to fail.

• LibreOffice Calc executed: Native test engine 25.2.3.2 Build 520, Linux, using the UNO desktop service and calculateAll(). 31/31 returned-string observations matched the recorded 25.2 delimiter behavior when compared to source formula properties. 14 additional diagnostic observations were recorded separately; none are counted as successful numerical tests.
• LibreOffice Help checked: The current Information Functions page (checked 2026-10-13) documents FORMULA’s single reference argument, string output, no-formula #N/A, and recalculation. Its illustrative text uses semicolons, while the tested UNO serialization used commas.
• ODF standards comparison: OpenDocument 1.4 Part 4 §6.13.12 includes FORMULA(Reference) as an intrinsic named function. Its returned textual syntax is deliberately implementation-defined. The ODF standard is not a guarantee of character-for-character cross-application compatibility.
• Locale: Function names, argument delimiters and string serialization can differ with locale and import/export context. Formulas on this page use standard English Calc function names and semicolon-separated input syntax; all reported native text outputs are specifically labelled as tested UNO outputs.
• Other software not tested: No Microsoft Excel, Google Sheets, Apache OpenOffice, ONLYOFFICE, macOS or Windows native execution was conducted. Do not assert equivalent behavior simply because another program offers a similarly named feature or can open ODS files.

• ISFORMULA — identify formula-containing cells, whether or not they currently calculate to errors.
• IFERROR — substitute a friendly message when FORMULA reports an error.
• IF — conditionally display a formula or a review message.
• INDEX — use a supported reference-returning expression when a dynamic cell selection is necessary; verify before relying on it.
• SUM — the function used in the official FORMULA example; see the difference between storing an expression and returning its value.
• COUNTIF — one additional formula shape tested for text-serialization differences.

1. LibreOffice Help, Information Functions, “FORMULA”: what it returns, syntax FORMULA(Reference), invalid/nonformula #N/A, recalculation, official SUM worked example. https://help.libreoffice.org/latest/en-US/text/scalc/01/04060104.html (accessed 2026-10-13).
2. OASIS OpenDocument 1.4, Part 4: OpenFormula, §6.13.12 FORMULA, standard approved 6 October 2025. Confirms Reference input, string output, error non-propagation and implementation-defined text serialization. https://docs.oasis-open.org/office/OpenDocument/v1.4/os/part4-formula/OpenDocument-v1.4-os-part4-formula.html (accessed 2026-10-13).
3. Native validation evidence: native_formula_validation.py, native_formula_results.json, LibreOffice_FORMULA_Native_Validation.ods in the saved dated evidence. Includes 31 successful native output-text/property consistency assertions, 14 diagnostic observations, actual expression strings and error codes. A mathematical calculation was not independently executed for each source formula; this is a test of the FORMULA function’s returned text.