CELL Function (LibreOffice Calc)
The CELL function in LibreOffice Calc returns detailed metadata about a cell, such as its address, filename, format, column width, protection status, and more. It is essential for diagnostics, dynamic formatting, and advanced spreadsheet automation.
Verification status: Selected examples were executed in LibreOffice Calc 25.2.3.2. This does not establish that every formula and edge case was tested. How formula examples are checked
Availability and version history
- Introduced: Documented by LibreOffice 26.8.1.1; original introduction not established.
- Availability: Supported in LibreOffice 26.8.1.1, verified against its function registry. This identifies a supported release, not a final working version.
- Scope: Desktop Calc, using the English function name. Availability in other spreadsheet applications is separate.
- Checked: 2026-10-14 — function reference; versioned support. Documentation/source check; not a new native test. Existing example-specific test claims below retain their stated scope.
Native-tested visual example: Report a referenced cell’s column
A record’s numeric value is in cell B5. CELL tells you its column number.
Enter this formula in cell E5:
=CELL("COL";B5)
Expected result: 2. "COL" returns the column index of the reference, so B5 produces 2. It does not return the cell value 45.
Real screenshot captured from LibreOffice Calc 25.2.3.2. The selected E5 cell shows the calculated result and the formula bar displays the entered formula.
Download the editable CELL example (.ods) — change the sample input cells and watch the calculation update.
Why it works:
"COL"returns the column index of the reference, so B5 produces 2. It does not return the cell value 45. Watch for: CELL information-type keywords and behavior vary between spreadsheet programs; check Calc’s own documentation.
Verification: Example calculated in LibreOffice Calc 25.2.3.2. Screenshot is a genuine application capture, not a mockup.
What the CELL Function Does
- Returns metadata about a cell
- Supports multiple info types (address, filename, format, width, protection, etc.)
- Useful for diagnostics and dynamic logic
- Works with direct references and reference‑returning formulas
It is designed to be flexible, metadata‑rich, and essential for advanced workflows.
Syntax
CELL(info_type; reference)
Arguments
-
info_type:
A text keyword specifying the metadata to return. -
reference:
A cell reference to inspect.
Supported CELL Info Types in LibreOffice Calc
| Info Type | Description | Example Output |
|---|---|---|
| “address” | Absolute cell address | “$A$1” |
| “col” | Column number | 1 |
| “row” | Row number | 1 |
| “filename” | Full path + sheet name | “/path/file.ods#Sheet1” |
| “color” | 1 if cell is formatted in color for negative values | 0 |
| “contents” | Cell contents | Value of the cell |
| “type” | Type code: b (blank), l (label), v (value) | “v” |
| “format” | Number format code | “F0”, “D2”, etc. |
| “prefix” | Label prefix (’ for text) | “'” |
| “protect” | 1 if locked, 0 if not | 1 |
| “width” | Column width in units | Platform‑dependent |
Unsupported types return Err:502.
Basic Examples
Get the address of A1
=CELL("address"; A1)
Returns "$A$1".
Get the filename of the current document
=CELL("filename"; A1)
Get the column number
=CELL("col"; C5)
Returns 3.
Get the row number
=CELL("row"; C5)
Returns 5.
Get the contents of a cell
=CELL("contents"; A1)
Get the number format code
=CELL("format"; A1)
Advanced Examples
Detect if a cell is locked
=CELL("protect"; A1)
Useful for template protection logic.
Detect if a cell contains text vs number
=CELL("type"; A1)
Returns:
"b"→ blank"l"→ label (text)"v"→ value (number)
Get the sheet name from filename
=RIGHT(CELL("filename"; A1); LEN(CELL("filename"; A1)) - FIND("#"; CELL("filename"; A1)))
Build dynamic references using CELL + INDIRECT
=INDIRECT(CELL("address"; A1))
Detect negative‑number formatting
=CELL("color"; A1)
Returns 1 if negative numbers are formatted in color.
Get column width for layout logic
=CELL("width"; A1)
Use CELL to track active cell (Excel‑style behavior)
LibreOffice does not update CELL(“address”) based on active selection unless recalculated manually.
Use CELL to detect formatting changes
=CELL("format"; A1)
Useful for conditional formatting diagnostics.
Edge Cases and Behavior Details
CELL(“filename”) requires the file to be saved
Unsaved documents return an empty string.
CELL(“address”) always returns absolute references
"$A$1"
"$C$5"
CELL(“type”) returns:
"b"for blank"l"for text"v"for numbers
CELL(“contents”) returns the raw value
- Dates return serial numbers
- Times return fractional days
- Formulas return their result
CELL(“format”) returns internal format codes
These are not user‑friendly; they are engine‑level identifiers.
CELL does not auto‑update on selection change
Recalculate manually to refresh.
Unsupported info types return Err:502
LibreOffice supports fewer types than Excel.
Common Errors and Fixes
Err:502 — Invalid argument
Occurs when:
- info_type is unsupported
- info_type is misspelled
- info_type is not text
CELL returns outdated information
Cause:
- CELL does not auto‑refresh
- Requires manual recalculation
CELL(“filename”) returns empty string
Cause:
- File has not been saved yet
CELL(“contents”) returns unexpected values
Cause:
- Dates/times are numeric
- Formulas return results, not formulas
Best Practices
- Use CELL for metadata‑driven automation
- Use CELL(“filename”) for document‑aware logic
- Use CELL(“type”) to distinguish text vs numbers
- Use CELL(“protect”) for template protection workflows
- Recalculate manually when using CELL in dashboards
Related Patterns and Alternatives
- Use INFO for system‑level metadata
- Use TYPE for data‑type inspection
- Use ADDRESS and INDIRECT for dynamic references
- Use ROW and COLUMN for positional logic
- Use ISREF and ISERROR for validation
By mastering CELL and its companion functions, you can build metadata‑aware, dynamic, and highly automated spreadsheets in LibreOffice Calc.