The XOR function in Excel
XOR (exclusive or) tests a list of conditions and returns TRUE when an odd number of them are TRUE. With two conditions, that means exactly one of them is TRUE. Use it when two options cannot both apply, such as a discount that is either a coupon or a loyalty reward.
XOR is available in Excel 2013 and later. It works with the same TRUE and FALSE values as AND and OR.
Syntax
=XOR(logical1, [logical2], ...)
| Argument | What it means |
|---|---|
| logical1 | The first condition: TRUE, FALSE, or a test such as =B2>10. |
| logical2, …optional | More conditions. XOR checks them all together. |
Examples
1. Exactly one option is chosen
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Has phone | TRUE | |||||
| 2 | Has email | FALSE | |||||
| 3 | Exactly one? | TRUE |
One condition is TRUE and one is FALSE, so XOR returns TRUE.
2. Both options chosen
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Paid by card | TRUE | |||||
| 2 | Paid by cash | TRUE | |||||
| 3 | Exactly one? | FALSE |
Both conditions are TRUE: an even number of TRUE values, so XOR returns FALSE. This is the case where OR would still say TRUE.
3. Three conditions
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Three flags are set | TRUE |
With three TRUE values the count is odd, so the result is TRUE. With more conditions, XOR counts the TRUE values.
Common mistakes
Expecting XOR to mean 'one or the other'. XOR returns FALSE when both are TRUE, which is what OR would not do.
Fix: Use OR when either option (or both) is acceptable, and XOR when only one may apply.
Text instead of TRUE or FALSE. A cell with the text "yes" is not a logical value and causes #VALUE!.
Fix: Compare the text first, e.g.
=XOR(B2="yes",C2="yes").
Frequently asked questions
Why does XOR return FALSE when both values are TRUE?
XOR is true only when an odd number of inputs are TRUE. Two TRUE values make an even count, so the result is FALSE.
Can XOR check more than two conditions?
Yes. It returns TRUE when an odd number of all the conditions are TRUE.