The Excel XOR function returns a logical “exclusive OR” of all arguments and provides TRUE or FALSE output.
If you use two logical tests, XOR will return TRUE if either logical test evaluates to TRUE and return FALSE if both logical values are TRUE.
XOR returns FALSE if neither logical is TRUE.
Syntax, Arguments, and return value
The function uses the following syntax:
=XOR(logical1, logical2,…..)XOR uses one required and up to a maximum of 254 optional arguments.
- logical1: Expression or constant that returns TRUE or FALSE.
- logical2 [optional]: Expression or constant that returns TRUE or FALSE.
All arguments will be evaluated to logical values such as TRUE or FALSE or in arrays or references that contain logical values.
Return value: We use the XOR function to perform exclusive OR, where the return value is TRUE or FALSE.
How to use the XOR function in Excel
Look at the differences between the XOR and the OR function. The XOR function provides exclusive OR, OR function provides inclusive OR. If you add two logical values as XOR function arguments, the function returns TRUE only if one of the logical is TRUE. If both logical are TRUE, the result is FALSE.
At first, the definition seems complicated, so here is a real-life example. For the sake of simplicity, we have two logicals:
- logical1: the output of “I’ll be at my office until 8 pm.“
- logical2: the output of “I’ll be at the cinema until 8 pm.”
=XOR("I'll be at my office until 8 pm", "I'll be at the cinema until 8 pm")Based on the XOR function definition, you can’t watch your favorite movie at the cinema and simultaneously work on your project at the office. (If you have superhero skills, you can do that, but set this case aside.)
If you are at the office until 8 pm OR at the cinema until 8 pm, the expression is TRUE. In any other cases, the XOR function returns FALSE.
Exclusive OR function with two logical values
If you are working with two arguments, the formula is the following:
=XOR(A1, B1)In this case, the result will be TRUE if only one of the arguments is TRUE. In all other cases, the result is FALSE.
Working with multiple logical values
Let us see what will happen if you use the XOR function with more than two arguments (logical values).
If you want to simplify the rule, keep in mind the XOR function returns TRUE when the number of TRUE arguments is odd and FALSE when the number of TRUE arguments is even.
In the example, enter the following formula in G3:
=XOR(B3,C3,D3,E3)
XOR will aggregate the logical test results in each row and return TRUE or FALSE. In G3, the result is TRUE because the number of TRUE arguments is odd (1). Following this logic, in cell G9, the result is TRUE since the number of TRUE values is odd (3). This method works based on the algorithm mentioned above in the case of 10+ logical values.
Summary
- Excel will evaluate the XOR function arguments to TRUE/FALSE or 1/0.
- XOR returns TRUE when the number of TRUE arguments is odd; else, it gets FALSE.
- The function returns a #VALUE error if the result does not contain logical values.
