XOR Function in Excel: Exactly One Condition True

The XOR function in Excel answers the question "is exactly one of these true?". It returns TRUE when one condition is TRUE and the other is FALSE, and FALSE when both match. That makes it a quick way to spot mismatches, such as an order that was paid but never shipped. This guide shows how XOR works, how it differs from OR, and the one trap with three or more conditions.

What does the XOR function do?#

XOR stands for "exclusive OR": one or the other, but not both. Its syntax is:

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

Each argument is a logical test, which is any expression that gives TRUE or FALSE, such as B2="Yes" or C2>100. You can give up to 254 of them. XOR is available in Excel 2013 and later, including Microsoft 365 and Excel for the web.

With two conditions, the result follows this truth table:

The only row where OR and XOR differ is the first one: when both conditions are TRUE, OR says TRUE and XOR says FALSE. The guide to AND, OR and NOT covers the other logical functions.

XOR example: find orders that don't match#

XOR is most useful for finding rows where two things should agree but don't. In this order list, every order should be both paid and shipped, or neither:

In D2, type this and fill down to D7 (Windows: Ctrl+D; Mac: ⌘D):

=XOR(B2="Yes",C2="Yes")

The results are FALSE, TRUE, FALSE, TRUE, FALSE, TRUE. Orders 1002 and 1006 are paid but not shipped, and order 1004 was shipped without payment. Orders where both columns agree, whether Yes and Yes or No and No, return FALSE.

The same idea works for two reviewers who should agree (=XOR(B2="Approve",C2="Approve")), or a form where a person should tick exactly one of two boxes.

How to use XOR with IF#

TRUE and FALSE are fine for checking, but a label is easier to read. Put XOR in the test of IF:

=IF(XOR(B2="Yes",C2="Yes"),"Check","OK")

Now the column shows Check for orders 1002, 1004 and 1006, and OK for the rest. IF returns its second argument when the test is TRUE and its third when the test is FALSE. If you want to filter or count the problem rows later, a clear word like Check is easier to work with than TRUE. For more on combining logic with IF, see IF with OR and IF with AND.

XOR with numbers#

The tests inside XOR can be any comparison, not only text. Say a bonus applies to a rep who beat the sales target or the new-customer target, but not both, because hitting both earns a different, bigger award:

=XOR(B2>=50000,C2>=10)

If B2 is 62000 and C2 is 8, only the first test is TRUE, so XOR returns TRUE. If B2 is 62000 and C2 is 12, both are TRUE, so XOR returns FALSE. To test a value inside a range rather than above a line, see IF between two numbers.

The trap: XOR with three or more conditions#

With three or more conditions, XOR returns TRUE when an odd number of them are TRUE, not when exactly one is. That surprises many people:

So =XOR(TRUE,TRUE,TRUE) returns TRUE. If you need "exactly one of these three cells says Yes", count the matches instead:

=COUNTIF(B2:D2,"Yes")=1

This returns TRUE only when one cell in B2:D2 is Yes. For tests that aren't simple matches, add them up. In Excel, TRUE counts as 1 and FALSE as 0 when you add them:

=(B2>100)+(C2>100)+(D2>100)=1

XOR vs OR and other ways to write it#

Use OR when "at least one" is the rule, and XOR when "one but not both" is the rule. The OR function guide covers OR in detail, and the article on AND vs OR compares those two.

For two conditions, you can also write XOR without the function. Two tests are "exclusive" when their results differ, so <> (not equal to) does the same job:

=(B2="Yes")<>(C2="Yes")

This works in every version of Excel, which helps if a file must open in Excel 2010. XOR is easier to read, though, so use it when you can.

To count the mismatched orders in one cell, count each kind of mismatch and add them:

=COUNTIFS(B2:B7,"Yes",C2:C7,"No")+COUNTIFS(B2:B7,"No",C2:C7,"Yes")

That gives 2 + 1 = 3.

Common XOR mistakes#

These are the problems that come up most:

1. Expecting "exactly one" with three or more tests. XOR checks for an odd number. Use COUNTIF or added tests instead.
2. Forgetting quotes around text. B2=Yes gives #NAME?. Write B2="Yes".
3. Using XOR where OR is meant. If both conditions being TRUE should also pass, XOR gives the wrong answer for those rows.
4. Old Excel versions. XOR returns #NAME? in Excel 2010 and earlier. Use the <> version there.

Where XOR helps in real sheets#

XOR is not an everyday function, but it shines in data checks. Three common uses:

• Mismatch flags. Two columns that should agree, like Paid and Shipped. =XOR(B2<>"",C2<>"") also flags rows where only one of two cells is filled in, such as a start date with no end date.
• Either-or rules. A discount for members or for coupon holders, but not both.
• Reviewer disagreement. Two approvers who should reach the same decision.

In a test, the question rarely names XOR. Look for wording such as "one or the other but not both" or "exactly one of the two". For two conditions, that is XOR.

Practise it#

XOR is easy once AND, OR and NOT feel natural. Practise AND, OR and NOT in a live spreadsheet: you type each logical test and the lesson checks it instantly. Then try the exercise on this page and flag the order that was paid but not shipped.