The Excel XOR function is the “exclusive or”: with two tests it returns TRUE when one or the other is true, but not both. It answers questions plain OR can’t — “did exactly one of these happen?” — like a customer who used a discount code or a coupon, but isn’t allowed to stack both. With three or more arguments the rule generalizes in a way that surprises people, so read on.
Syntax
| Argument | Description | |
|---|---|---|
logical1 | Required | The first condition or value that evaluates to TRUE or FALSE. |
logical2, ... | Optional | Up to 254 further conditions. Ranges are allowed — every cell in them counts as one logical value. Text and empty cells in a range are ignored; numbers count (0 = FALSE, anything else = TRUE). |
Available in: Excel 2013 and later, Excel for Microsoft 365, and Excel for the web. Excel 2010 and earlier don’t recognize the name — use the classic workaround =(A2<>0)<>(B2<>0) there.
One or the other — not both
The textbook picture is a hallway light wired to two switches: flipping either switch toggles the light, and the light is on only when the switches disagree.
| A | B | C | |
|---|---|---|---|
| 1 | Switch 1 | Switch 2 | Light on? |
| 2 | FALSE | FALSE | FALSE |
| 3 | TRUE | FALSE | TRUE |
| 4 | FALSE | TRUE | TRUE |
| 5 | TRUE | TRUE | FALSE |
A business version — one promotion allowed per order, not two, not zero applied by mistake:
Compare the three logical workhorses on the same pair of tests: AND needs both, OR accepts either or both, XOR insists on exactly one.
Try it: the odd-count rule, live
Tick any combination of the three boxes. XOR returns TRUE whenever an odd number of them are TRUE — one or three, never zero or two.
Three or more arguments: the odd-count rule
The key clarification: with 3+ arguments, XOR is not “TRUE when exactly one is TRUE.” It returns TRUE when the count of TRUE arguments is odd — 1, 3, 5… So =XOR(TRUE, TRUE, TRUE) is TRUE (three TRUEs, odd), even though all three are on.
That’s because Excel chains the pairwise operation: XOR(a,b,c) = XOR(XOR(a,b),c). Each extra TRUE flips the running answer, so the result tracks parity. Handy in its own right — parity checks, toggle states, alternating flags:
If what you actually want is “exactly one of these is TRUE,” count instead:
The -- double minus turns TRUE/FALSE into 1/0 so SUM can count them — the trick is explained on the TRUE page.
Errors & common pitfalls
#NAME? — Excel 2010 or earlier. XOR arrived in Excel 2013. In older versions emulate the two-argument form with =(A2<>0)<>(B2<>0), or for logicals simply =A2<>B2.
#VALUE! — no usable logicals. If the arguments contain no values that can be read as TRUE/FALSE (for example, only text), XOR returns #VALUE!.
Pitfall: assuming “exactly one” with 3+ arguments. XOR is an odd-count (parity) test. =XOR(TRUE,TRUE,TRUE) → TRUE. For a strict “exactly one,” use =COUNTIF(range,TRUE)=1.
Pitfall: text booleans in ranges are skipped. Cells containing the text “TRUE” (not the logical value) are ignored when passed inside a range, which can silently change the count. Typed directly as arguments, the words TRUE/FALSE do convert.
Practice workbook
Frequently asked questions
What does XOR mean in Excel?
=XOR(a, b) is TRUE when exactly one of them is TRUE and FALSE when they match — both TRUE or both FALSE.How does XOR behave with three or more arguments?
=XOR(TRUE, TRUE, TRUE) returns TRUE.How do I test that exactly one condition is TRUE?
=COUNTIF(A2:D2, TRUE)=1, or =SUM(--(tests))=1 for computed tests. XOR only matches “exactly one” when you have just two arguments.Which Excel versions have XOR?
=(A2<>0)<>(B2<>0) as a two-argument substitute.What's the difference between OR and XOR?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class