Contact
12 programs
Explore the site

XOR Function (LibreOffice Calc)

Logical Intermediate LibreOffice Calc LibreOffice 4.0

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.

Verification status: Consult the documentation, examples and any stated test scope below. How formula examples are checked

Availability and version history

  • Introduced: LibreOffice 4.0.
  • 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.
The **XOR** function implements *exclusive OR* logic in LibreOffice Calc. It returns TRUE only when an **odd number** of conditions are TRUE. This makes `XOR` ideal for detecting mismatches, enforcing mutually exclusive rules, and validating data where exactly one condition should be met.

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; ...)
LibreOffice Calc uses semicolons (;) to separate arguments.

Arguments

  • condition1, condition2, …
    Logical expressions that evaluate to TRUE or FALSE.
    Examples:
    • A1 > 10
    • B2 = "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)
XOR is perfect for enforcing mutually exclusive choices, such as “choose one option only” or “only one field may be filled in.”
  • 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.

Reproducible example (tested in LibreOffice Calc)

This standalone example was calculated in LibreOffice Calc 25.2.3.2 and matched the expected value. It is a practical cross-check of one usage, not proof that every example elsewhere on this page is correct.

=XOR(TRUE();TRUE())

Expected result: FALSE.

Return TRUE for an odd number of TRUE conditions.

If the result differs, check the referenced cells, argument separators and number formatting. For more information, consult the LibreOffice Calc official help.