MODE Function (LibreOffice Calc)
The MODE function in LibreOffice Calc returns the most frequently occurring value in a dataset. This guide explains syntax, examples, multi-mode behavior, errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the MODE Function Does ▾
- Returns the most frequent value in a dataset
- Works with numbers, ranges, and mixed arguments
- Ignores empty cells automatically
- Returns the first mode if multiple modes exist
- Works across sheets
- Useful for frequency and distribution analysis
MODE is ideal for identifying the most common value in a dataset.
Syntax ▾
MODE(number1; number2; ...)
LibreOffice Calc uses semicolons (
;) to separate arguments.
MODE ignores text unless it represents a valid number.
Basic Examples ▾
Mode of a range
=MODE(A1:A10)
Mode of multiple ranges
=MODE(A1:A10; C1:C10)
Mode of specific values
=MODE(5; 12; 19; 12)
Result: 12
Mixed values and references
=MODE(A1:A10; 25; C5)
Multi‑Mode Behavior ▾
If multiple values occur with the same highest frequency, MODE returns the first one it encounters.
Example dataset:
2, 3, 3, 5, 5
Both 3 and 5 occur twice.
MODE returns:
=MODE(A1:A5)
Result: 3
To extract all modes, use an array formula:
=IF(FREQUENCY(A1:A10; A1:A10)=MAX(FREQUENCY(A1:A10; A1:A10)); A1:A10)
(Advanced technique.)
Advanced Examples ▾
Mode across sheets
=MODE(Sheet1.A1:A10; Sheet2.A1:A10)
Mode ignoring errors (using AGGREGATE)
=AGGREGATE(13; 2; A1:A10)
Mode of visible cells only (filtered data)
=AGGREGATE(13; 1; A1:A10)
Mode with conditions (indirect)
LibreOffice has no MODEIF, but you can simulate it:
=MODE(IF(A1:A10="North"; B1:B10))
(Confirm with Ctrl+Shift+Enter in older Calc versions.)
Mode of top 10 values
=MODE(LARGE(A1:A100; ROW(1:10)))
Mode excluding zeros
=MODE(IF(A1:A100<>0; A1:A100))
Mode of text-like numbers
=MODE(VALUE(A1:A10))
Common Errors and Fixes ▾
MODE returns Err:502 (Invalid argument)
Occurs when:
- Non-numeric text is included
- A range reference is malformed
- No numeric values exist in the dataset
MODE returns Err:511 (Missing variable)
Occurs when:
- No values match the criteria in an array formula
- The dataset is empty
MODE returns wrong result with filtered data
MODE does not ignore hidden rows.
Use:
=AGGREGATE(13; 1; A1:A10)
MODE returns the wrong mode in multi-mode datasets
This is expected—MODE returns the first mode only.
Best Practices ▾
- Use MODE to identify the most common value in a dataset
- Use AGGREGATE for visibility‑aware or error‑tolerant mode calculations
- Use array formulas for conditional or multi-mode extraction
- Clean imported data before analysis
- Use named ranges for cleaner formulas
MODE is especially useful in quality control, surveys, and frequency analysis—anywhere you need to know the most common value.