MS Excel: XOR function to evaluate logical conditions

When working with logical comparisons in Excel, most users are familiar with AND and OR. However, Excel also offers a lesser-known yet powerful logic function called XOR—short for "Exclusive OR".

The XOR function evaluates two or more logical conditions and returns TRUE only when an odd number of the conditions are true. It's particularly useful for scenarios where you want to ensure that only one of several conditions is true, but not both (or not all).

Syntax

=XOR(logical1, [logical2], ...)

Arguments:

Argument	Description
logical1	The first condition to evaluate
logical2	(Optional) Additional conditions

You can test two or more logical expressions. Excel supports up to 254 arguments.

Basic Example

=XOR(TRUE, FALSE)

Returns: TRUE Because only one of the two conditions is TRUE.

=XOR(TRUE, TRUE)

Returns: FALSEBecause both are TRUE, and XOR allows only one (or any odd number of TRUEs).

Use Case: Single Checkbox Logic

Imagine you have two checkboxes (linked to cells B2 and C2), and you want to ensure that only one is checked (not both, and not none):

=XOR(B2=TRUE, C2=TRUE)

Returns TRUE only if one checkbox is checked.

Truth Table Comparison

Condition A	Condition B	=XOR(A, B)
FALSE	FALSE	FALSE
FALSE	TRUE	TRUE
TRUE	FALSE	TRUE
TRUE	TRUE	FALSE

XOR is TRUE when the number of TRUE values is odd.

Multiple Conditions

=XOR(A1=100, B1=200, C1=300)

• Returns TRUE if 1 or 3 conditions are true

• Returns FALSE if 0 or 2 are true

Real-World Examples

Scenario	Formula	Description
Only one person signed off	=XOR(D2="Yes", E2="Yes")	Checks if only one of two people approved
One error type detected	=XOR(ISERROR(A1), ISBLANK(A1))	Returns TRUE if A1 is either blank or an error—but not both
Odd number of passed tests	=XOR(A1="Pass", B1="Pass", C1="Pass")	Useful in test validations

Common Mistakes

Mistake	What Happens	Tip
Using XOR like OR	Unexpected results when multiple TRUE values	Use OR if you want to return TRUE for any number of TRUEs
Misunderstanding "odd number" rule	May get FALSE even when some conditions are TRUE	Count the number of TRUEs—must be odd
Using text comparisons without quotes	Errors	Always wrap text values in double quotes

Summary Table

Feature	Details
Function Name	XOR
Category	Logical
Purpose	Returns TRUE if an odd number of conditions are TRUE
Common Uses	Validation, toggles, mutual exclusivity checks
Excel Version	Excel 2013 and later

Related Functions

Function	Use When
AND()	All conditions must be TRUE
OR()	At least one must be TRUE
NOT()	Reverse a logical value
IF()	Conditional branching

Final Thoughts

The XOR function offers a precise control mechanism in logical operations, especially when you want to ensure that only one condition is met. It’s excellent for validations, mutually exclusive options, and detecting inconsistencies.

Use XOR when two options should not be selected together, but one of them must be.