The Excel XOR function, or ‘exclusive OR’ as it’s known in the programming world, performs a test on an array, range or one or more expressions. It was introduced in Excel 2013.
| Syntax: | =XOR( logical1, [logical2],...) | |
| logical1 | An expression (a formula that results in a single value), array or reference that evaluates to TRUE or FALSE, or their numeric equivalents of 1 and 0. | |
| logical2 | An optional additional expression, array or reference that evaluates to TRUE or FALSE or their numeric equivalents. Up to 254 conditions can be tested. | |
Note: If the specified range for XOR contains no logical values, XOR returns the #VALUE! error.
XOR results vary depending on…:
- If the number of TRUE logical arguments are odd, XOR returns TRUE
- If the number of TRUE logical arguments are even, XOR returns FALSE
- When all logical arguments are FALSE, XOR returns FALSE
Download Workbook
Enter your email address below to download the sample workbook.
Excel XOR Function Example
The table below illustrates the different scenarios that result in XOR evaluating to TRUE or FALSE.
XOR will also accept the numeric equivalents for TRUE and FALSE of 1 and 0 respectively. Interestingly, any positive or negative number other than zero will also be treated as TRUE*. Blank cells are ignored.
*Tip: I’ve found this is also the case for many other Excel functions that require a Boolean logical value, for example the IF function will treat any positive or negative number other than zero as TRUE, as will Conditional Formatting.
To demonstrate this with XOR, we can replace the TRUE and FALSE values shown above with numbers. In the table below you can see they yield the same results from XOR:
Practical Uses for XOR
The Excel XOR function is generally used in conjunction with other functions, like the IF function. A common example is a light that can be controlled by two switches, one at each end of a hall or staircase. The light is only on when the two switches are in different positions.
We can wrap XOR in the IF formula to return the light status (assumes both switches were originally in the Off/Down position):
Another example is finding the compatibility of fixings. The example below uses nuts and bolts, but it could equally be plugs with male or female connectors:
Lastly, let’s take a football tournament where each team has played 2 matches and we need to find out which teams will play a third match. A third match is required by teams who lost one game. Teams that won both games qualify for the final and teams that lost both games are out.
In the table below, 1 represents a win and 0 is a loss:
Learn More Functions
Check out our Excel Function Library where we cover all of the most important Excel functions and formulas you should know.
