XOR Function (LibreOffice Calc)
The XOR function in LibreOffice Calc evaluates multiple logical conditions and returns TRUE only when an odd number of conditions are TRUE. Learn syntax, examples, common errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✖ |
| Wps | ✔ |
| Zoho | ✔ |
What the XOR Function Does ▾
- Evaluates two or more logical conditions
- Returns TRUE when an odd number of conditions are TRUE
- Returns FALSE when an even number of conditions are TRUE
- Works with numbers, text, dates, and formulas
- Helps enforce mutually exclusive logic
- Integrates with IF, AND, OR, and NOT
It is ideal for validation rules, mismatch detection, exclusive choices, and error checking.
Syntax ▾
XOR(condition1; condition2; ...)
;) to separate arguments.
Arguments
- condition1, condition2, …
Logical expressions that evaluate to TRUE or FALSE.
Examples:A1 > 10B2 = "Yes"C3 <= D3
Basic Examples ▾
TRUE when exactly one condition is TRUE
=XOR(A1 > 10; B1 > 10)
Returns TRUE when only one of the two conditions is TRUE.
TRUE when an odd number of conditions are TRUE
=XOR(A1 > 0; B1 > 0; C1 > 0)
Returns TRUE when 1 or 3 conditions are TRUE.
Detect mismatched values
=XOR(A1 = "Yes"; B1 = "Yes")
TRUE when only one of the cells contains “Yes”.
Advanced Examples ▾
XOR inside IF (exclusive logic)
=IF(XOR(A1 = "Gold"; A1 = "Platinum"); "Valid Tier"; "Invalid")
TRUE only when A1 matches exactly one tier.
Validate mutually exclusive options
=XOR(B1 = "Online"; B1 = "In‑Store")
Ensures B1 contains exactly one valid option.
XOR with AND and OR
=XOR(AND(A1 > 0; B1 > 0); C1 = "Override")
TRUE when:
- both A1 and B1 are positive OR
- C1 equals “Override”
but not both.
XOR with calculations
=XOR(SUM(A1:A5) > 100; AVERAGE(B1:B5) < 50)
TRUE when exactly one aggregated condition is met.
XOR for data validation
=XOR(A1 <> ""; B1 <> "")
TRUE when exactly one of the two fields is filled in.
Common Errors and Fixes ▾
XOR always returns FALSE
Possible causes:
- An even number of conditions are TRUE
- A text comparison includes hidden spaces
- A number is stored as text
Fix:
Convert text to numbers using:
Data → Text to Columns → OK
XOR always returns TRUE
Often caused by:
- Only one condition is TRUE every time
- Incorrect comparison operators
- Hidden characters in text values
Err:508 — Missing parenthesis
Occurs when:
- A nested XOR inside IF is missing a closing
) - Commas are used instead of semicolons
Err:509 — Missing operator
Occurs when:
- A comparison operator is missing
- A condition is incomplete (e.g.,
A1 >)
Best Practices ▾
- Use XOR when exactly one condition should be TRUE
- Avoid using XOR for more than 3–4 conditions unless necessary
- Combine XOR with IF for exclusive decision-making
- Use helper cells for readability when logic becomes complex
- Ensure consistent data types (numbers vs text)
Related Patterns and Alternatives ▾
- Use OR when any condition may be TRUE
- Use AND when all conditions must be TRUE
- Use NOT to invert a logical test
- Combine XOR with COUNTIF, MATCH, or VLOOKUP to build exclusive validation rules
By mastering XOR and its combinations with other logical functions, you can build precise, exclusive logic structures in LibreOffice Calc that prevent errors and enforce clean data entry.