Exclusive OR with XOR

Excel Formulas › Logical

2013+XOR

XOR is TRUE when an odd number of conditions are TRUE — for two conditions, that means “exactly one, but not both.” Useful for either/or rules and parity checks.

Quick formula: TRUE when exactly one of A2, B2 is TRUE:
=XOR(A2, B2)
For two inputs, XOR is TRUE if they differ — one TRUE, one FALSE — and FALSE if they’re the same.

Functions used (tap for the full reference guide):

XOR

The example

Two flags compared with XOR.

ABC
1ABXOR
2TRUEFALSETRUE
3TRUETRUEFALSE

The formula

Exactly-one logic:

=XOR(A2, B2) // TRUE when they differ

How it works

XOR counts the TRUEs:

  1. XOR(conditions) is TRUE when an odd number of them are TRUE.
  2. With two conditions, that’s “exactly one” — one TRUE and one FALSE.
  3. Unlike OR, it’s FALSE when both are TRUE.
  4. Use it for “pick one” rules: e.g. flag rows where exactly one of two boxes is checked.

More than two inputs? XOR’s “odd count” rule means it’s not simply “exactly one” for three or more. To require exactly one of several conditions, use =SUMPRODUCT(--(conditions))=1 instead.

Try it: interactive demo

Live demo

Toggle two flags.

XOR:

Variations

Exactly one of many

Count the TRUEs:

=SUMPRODUCT(--(conditions))=1

Both TRUE (AND)

For comparison:

=AND(A2, B2)

At least one (OR)

Inclusive:

=OR(A2, B2)

Pitfalls & errors

Odd-count, not exactly-one. For 3+ inputs, XOR is TRUE for 1 or 3 TRUEs. Use SUMPRODUCT for strict “exactly one.”

2013 or later. XOR isn’t in Excel 2007 — use A2<>B2 for two booleans.

Inputs are boolean. Numbers are treated as TRUE if non-zero.

Practice workbook

📊
Download the free Exclusive OR with XOR practice workbook
An XOR sheet with the exactly-one, AND, and OR variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

What does the XOR function do in Excel?
XOR returns TRUE when an odd number of conditions are TRUE. For two conditions, that means exactly one is TRUE (they differ). Requires Excel 2013+.
How do I check that exactly one of several conditions is TRUE?
Use =SUMPRODUCT(--(conditions))=1, since XOR's odd-count rule isn't strict 'exactly one' for three or more inputs.
How is XOR different from OR?
OR is TRUE if any condition is TRUE (including both); XOR is FALSE when both are TRUE.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: IF with AND/OR · Count rows OR conditions · ISEVEN & ISODD

Function references: XOR