Purpose
Return value
Syntax
=XOR(logical1,[logical2],...)- logical1 - An expression, constant, or reference that evaluates to TRUE or FALSE.
- logical2 - [optional] An expression, constant, or reference that evaluates to TRUE or FALSE.
How to use
The XOR function performs what is called "exclusive OR", in contrast to the "inclusive OR" performed by the OR function. Whereas the OR function returns true if any input is TRUE, XOR only returns TRUE in specific cases. In the simplest case, with just two logical statements, XOR returns TRUE only if one of the logicals is TRUE. If both values are TRUE, XOR returns FALSE.
The concept of exclusive OR is more common in the world of programming. In plain English, you can think of a sentence like this: "I'm either going to visit New York or San Francisco this summer". Nothing prevents the speaker from visiting both, but the meaning is clearly that they plan to visit only one or the other. If they visit one or the other, the original statement is TRUE. If they visit neither or both, the original statement is FALSE.
Example #1 - two values
In the example shown, the formula in D5, copied down, is:
=XOR(B5,C5)
At each row, XOR only returns TRUE when B5 and C5 contain a single TRUE or equivalent value.
Example #2 - more than two values
With more than 2 values, the behavior of XOR is a bit different. With three or more logicals, XOR only returns TRUE when the number of TRUE values is odd, as shown in the following example:
In this example, XOR is given a range with five values in a single argument instead of five separate arguments. The formula in G6 copied down is:
=XOR(B6:F6)
The result is TRUE only when the number of TRUE values columns B through F is an odd number.
Notes
- Logical arguments must evaluate to TRUE or FALSE, 1 or 0, or be references that contain logical values.
- XOR returns #VALUE! if no logical values are found.
- With more than two logicals, XOR returns TRUE when the number of TRUE logicals is odd, and FALSE if not.
- XOR was introduced in Excel 2013.
XOR function examples
One or the other not both
Related functions
OR Function
The Excel OR function is a logical function used to test multiple conditions at the same time. OR returns TRUE if any condition is TRUE. If all conditions are FALSE, the OR function returns FALSE. The OR function is often used with the IF function and can be combined...
NOT Function
The Excel NOT function returns the opposite of a given logical or Boolean value. When given TRUE, NOT returns FALSE. When given FALSE, NOT returns TRUE. Use the NOT function to reverse a logical value.
AND Function
The Excel AND function is a logical function used to test multiple conditions at the same time. AND returns TRUE only if all the conditions are met. If any conditions are not met, the AND function returns FALSE. The AND function is commonly used with other...
IF Function
The Excel IF function performs a logical test and returns one result when the logical test returns TRUE and another when the logical test returns FALSE. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF...
