The XOR function in Google Sheets performs a logical exclusive or across two or more expressions, returning TRUE if an odd number of the arguments are true, and FALSE otherwise. It's a less common but powerful complement to AND and OR for building precise conditional logic. Dive into our comprehensive guide to master its application.
Parameters
logical_expression1: The first logical expression or value to evaluate.[logical_expression2, ...]: [Optional] Additional logical expressions or values to evaluate.
Step-by-Step Tutorial
-
Using
XORwith two conditions:- Example:
=XOR(TRUE, FALSE) - Result:
TRUE
- Example:
-
Using
XORwith two true conditions:- Example:
=XOR(A1>10, A1<20)whereA1is15 - Result:
FALSE(both conditions are true, so they cancel out)
- Example:
Working with a Single Value, a List, and a Range
XOR accepts logical expressions one at a time, as a comma-separated list, or as a range — mix and match freely.
- Single value:
=XOR(A1)— returnsTRUEifA1isTRUE,FALSEotherwise. - List of values:
=XOR(A1, A2, A3)— evaluates each cell individually and returnsTRUEif an odd number of them areTRUE. - Range:
=XOR(A1:A3)— evaluates every cell in the range and returnsTRUEif an odd number of them areTRUE. IfA1:A3holdsTRUE, TRUE, TRUE, the result isTRUE(three, an odd count, are true). - Mixed:
=XOR(A1, A3:A6)— you can combine single cells and ranges in one call.
Use Cases and Scenarios
- Conditional Logic: Trigger an action only when exactly one of two conditions is met, not both.
- Data Validation: Flag records where exactly one of two required fields is filled in, but not both.
- Game Logic: Build toggle-style logic, such as determining a winner when exactly one of two criteria is satisfied.
Related Functions
AND: ReturnsTRUEif all supplied logical expressions are true.OR: ReturnsTRUEif any of the supplied logical expressions are true.IF: Returns one value if a logical expression is true and another if false.