One of the smallest, but most powerful group of Excel functions is that of logical functions. In this particular blog post, I focus on the XOR function. More particularly, I explain everything you need to know in order to work with the XOR function and, where appropriate, use an alternative formula.
The following table of contents lists the main sections of this Excel tutorial:
- 1 The Purpose Of The XOR Function In Excel
- 2 The XOR Function In Venn Diagrams
- 3 Syntax Of The XOR Function In Excel
- 4 An Alternative To The XOR Function: Return TRUE When Only 1 Argument Is True
- 5 How To Use The XOR Function In Excel: An Example With 2 Conditions
- 6 How To Use The XOR Function And Its Alternative In Excel: An Example With 5 Conditions
- 7 Conclusion
- 8 Do you use Exclusive Or when working with Excel?
- 9 Books Referenced In This Excel Tutorial
As mentioned above, XOR is one of Excel’s logical functions. As explained in Excel 2013 All-In-One for Dummies, logical functions generally return the Boolean values TRUE and FALSE when they’re evaluated.
The XOR function was introduced in Excel 2013. The other logical function introduced in that version of Excel was the IFNA function, which I cover here.
As explained in the Excel 2013 Bible, the XOR function in Excel:
Returns a logical exclusive OR of all arguments.
As mentioned in Excel Formulas and Functions for Dummies and Excel 2013 In Depth, XOR stands for “Exclusive Or” within the framework of Boolean logic.
If you’re not familiar with Boolean logic, this description may not make much sense.
Even if that’s the case, don’t worry.
By the time you finish reading this tutorial, you’ll have a good understanding of the XOR function and the meaning of Exclusive Or. You’ll also get an idea of what the XOR function (and an alternative that I introduce below) may help you with, and how you can apply this when working with Excel.
Let’s begin by understanding what, exactly, a XOR function and a logical Exclusive Or are…
The Purpose Of The XOR Function In Excel
In order to better understand the purpose of the XOR function in Excel, let’s start by making a parallel with another logical function:
Excel’s OR function.
Broadly speaking, you can use the OR function to test multiple conditions at once. More precisely, Excel’s OR function proceeds as follows:
- Step #1: It performs 1 or more logical tests.
- Step #2: If at least 1 of the logical tests returns TRUE, the OR function also returns TRUE. If all of the logical tests return FALSE, OR returns FALSE.
Notice that the OR function only returns FALSE if absolutely all of the logical tests return FALSE.
The XOR function in Excel proceeds in a similar fashion, as follows:
- Step #1: Just as OR, it carries out 1 or more Boolean tests.
- Step #2: XOR returns TRUE when an odd number (for example, 1, 3, 5) of the logical tests returns TRUE.
The XOR function returns false if (i) an even number (for example, 2, 4, 6) logical test returns TRUE, or (ii) no logical test returns TRUE.
The key difference between OR and XOR is, therefore, in the number of logical tests that must be TRUE for the function itself (OR or XOR) to return TRUE. To summarize:
- The OR function returns TRUE if 1 or more logical tests return TRUE.
- The XOR function returns TRUE if an odd number of Boolean tests return TRUE.
Both the XOR and OR functions return FALSE if absolutely no logical test returns TRUE. Therefore, the results returned by OR and XOR are the same if the number of logical tests that return TRUE is either of the following:
- 0, in which case both functions return FALSE.
- Any odd number, in which case both functions return TRUE.
On the other hand, the results returned by these functions (OR and XOR) vary whenever an even number of Boolean tests return TRUE.
When comparing the OR and XOR functions, Excel expert Jordan Goldmeier (in Advanced Excel Essentials) says that:
You can think of OR as being all inclusive because it does not constrain the amount of TRUE values required to return TRUE.
The main use of the XOR function isn’t very clear when you just look at the description above.
However, as you’ll see in the example below, the XOR function is particularly helpful when you want to check if exactly 1 condition (out of 2) is TRUE. This is XOR’s most common use.
In this case, the basic truth table of XOR, as shown by Excel expert Jordan Goldmeier in Advanced Excel Essentials, is:
In certain contexts and resources, XOR is actually defined like this: a function that returns TRUE if only 1 of its arguments returns TRUE. In fact, if you’ve worked with Exclusive Or in other contexts, you may be used to this definition of XOR and not the one I explain at the beginning of this section (which is the one used by Excel). For example, in XL: The 40 Greatest Excel Tips of All Time, Mr. Excel warns that:
Although Excel 2013 introduced XOR as an Exclusive Or, it does not work the way that accountants would expect. (…) XOR really counts whether you have an odd number of True values.
The reason why Excel’s XOR function works as it does (returns TRUE if an odd number of arguments is TRUE) is explained by Mr. Excel in Power Excel with Mr. Excel. Basically, in Excel, XOR works like the 7486 Chip Set. Since there are millions of such 7486 chips, the way the XOR function proceeds in Excel is useful for electrical engineering purposes. I explain the basics of this process followed by the XOR function in the next section.
However, if you need a formula that returns TRUE when only 1 of its (more than 2) arguments returns TRUE, I provide an alternative below.
Before moving on to the next topic, let’s take a look at the XOR function using Venn diagrams. I have found that this is one of the most straightforward ways to understand why the XOR function returns TRUE if an odd number of logical tests return TRUE.
The XOR Function In Venn Diagrams
Let’s start with the simplest, but perhaps most common, application of the XOR function: 2 inputs (or logical tests). As explained above, whenever you apply the XOR function to 2 inputs, the output is TRUE when 1 of the inputs is (also) TRUE and the other input is FALSE.
The following Venn diagram shows how this looks like:
In this image, the Exclusive Or (XOR) includes the cases where (i) A is TRUE but B isn’t and, vice-versa, (ii) B is TRUE but A isn’t. These elements are the ones represented by the pink-shaded regions in the Venn diagram above.
Notice how (in the Venn diagram above) the intersection of A and B (where both A and B are TRUE) is blank.
This case is relatively straightforward.
However, the diagram above may not be enough to understand why the XOR function returns TRUE whenever an odd number of logical tests return TRUE. To understand better how this happens, let’s take a look at how a Venn diagram of 3 inputs (A, B and C) looks like:
For purposes of this Excel tutorial, the key part of this diagram is the intersection of A, B and C. This is highlighted in the image below:
This intersection represents all 3 logical tests returning TRUE. As shown in the image above, the intersection itself returns TRUE (is filled).
The last item you need for purposes of understanding why the XOR function returns TRUE whenever an odd number of logical tests return TRUE is the process used by Excel to evaluate a XOR function with more than 2 arguments.
In the case of a XOR function with 3 arguments (such as that represented by the Venn diagram above), Excel proceeds as follows:
- Step #1: Obtain the XOR of the first 2 logical tests (A and B in the case above).
- Step #2: Obtain the XOR of the result obtained in step #1 above and the third logical test (C in this example).
Let’s take a look at this process using Venn diagrams:
Step #1: Venn Diagram For 2-Argument XOR Function
As explained above, the Venn diagram for a 2-argument XOR looks as follows:
Notice that, in this Venn diagram, the part where the intersection of A and B (pictured above) and C (the third argument added below) is currently empty. In other words, it’s currently FALSE.
Step #2: Venn Diagram for 3-Argument XOR Function
In the second step, and as explained in Excel 2013 In Depth, Excel:
- Takes the Venn diagram above.
- Cascades that Venn diagram “into an XOR with the third argument”.
As shown above, the Venn diagram of this situation looks as follows.
The intersection of the 3 arguments (A, B and C) is now filled. This means that, in such case, XOR returns TRUE.
Compare the 2 following images. Notice how, as shown below, the intersection where A, B and C are TRUE lies precisely where the first XOR (between A and B) returned FALSE. Since 1 of the inputs (the intersection of A and B) of this second XOR returns FALSE (is empty) and the other (C) returns TRUE (is filled), the second XOR returns TRUE.
It’s difficult to continue drawing Venn diagrams with more arguments. However, the illustrations above probably give you an idea of how the XOR function continues to proceed when applied to more arguments.
The general rule is relatively simple:
- When you have a Venn diagram with an even number of arguments, the intersection of all the arguments (all are TRUE) is empty (returns FALSE).
This is the case of the Venn diagram with 2 arguments (A and B) shown above.
- If you have a Venn diagram with an odd number of arguments, the intersection of all of them (all are TRUE) is filled (returns TRUE).
This situation is illustrated by the Venn diagram with 3 arguments (A, B and C) above.
As mentioned by Excel expert Bill Jelen (Mr. Excel) in Excel 2013 in Depth, by now you:
(…) can imagine how the intersection area flips to FALSE for an even number of circles and then back to TRUE for an odd number of circles.
Let’s go back to Excel, and take a look at how you actually write a formula that uses the XOR function:
Syntax Of The XOR Function In Excel
The syntax of the XOR function in Excel is as follows:
All of the arguments of the XOR function (logical 1, logical2, etc.) are logical tests or conditions. These arguments must generally meet the following requisites:
- Condition #1: They can be (i) logical values, (ii) arrays, or (iii) references.
- Condition #2: They must generally return either TRUE or FALSE.
If the arguments are arrays or references, the arrays or references must contain logical values themselves. When considering this, note the following 2 additional rules regarding how XOR proceeds:
Rule #1: If you make reference to a range that doesn’t contain logical values, XOR returns the #VALUE! error.
Rule #2: If the array or reference that you include contains text or empty cells, those cells are ignored.
- Condition #3: Your XOR formula must include at least 1 condition (logical1). On the other end, XOR can take up to 254 arguments.
In other words, the first argument (logical1) is required. All the other arguments (logical#) are optional.
You can find examples of how to apply the XOR function below.
Before we get into this topic, let’s see how you can solve a problem that arises due to the way the XOR function operates in Excel:
An Alternative To The XOR Function: Return TRUE When Only 1 Argument Is True
As explained throughout this Excel tutorial, the XOR function returns TRUE when an odd number of arguments returns TRUE. This is the case even when that number is higher than 1.
However, as mentioned at the beginning of this post, you may be in 1 (or more) of the following situations:
- You define Exclusive Or as a logical operation that returns TRUE only when 1 (and only 1) of its arguments is TRUE.
Therefore, Excel’s XOR function doesn’t match the definition of Exclusive Or you use.
- You need an Excel formula that returns TRUE when only 1 of its arguments is TRUE.
Fortunately, you can easily create a formula that returns TRUE when only 1 of the logical tests it evaluates returns TRUE by using the COUNTIF function and some Boolean logic. The basic syntax of such formula is as follows:
In this formula:
- “logicals” is the list of logical tests you want to apply the XOR function to.
Just as with the XOR function above, these logical tests must generally return TRUE or FALSE.
- TRUE is the TRUE logical value.
This formula proceeds, basically, as follows:
- Step #1: The COUNTIF function counts the number of logical tests that return TRUE.
- Step #2: The value returned by the COUNTIF function is compared with the number 1 by using the equality (=) operator.
- Step #3: If the number of logical tests that return TRUE (as counted by the COUNTIF function) is equal to 1, the function returns TRUE. Otherwise, it returns FALSE.
This Excel tutorial doesn’t cover the COUNTIF function. I may write about it in the future. If you want to be notified whenever I publish new material in Power Spreadsheets, please make sure to register for our Newsletter by entering your email address below:
Let’s take a look at this formula, and the XOR function itself, in action:
How To Use The XOR Function In Excel: An Example With 2 Conditions
In the following sections, I show you 2 relatively simple examples of how you can use the XOR function and the alternative I introduce above in practice.
Since this Excel tutorial focuses on the XOR function, I only use the logical values TRUE and FALSE in these examples. You can, however, create slightly more complex formulas by combining XOR (or its alternative) with other functions, such as the IF function.
This first example focuses on a case where you carry out 2 logical tests.
The section below shows an example where you use the XOR function on a larger number or arguments (5). In that section, I also show you how to use the XOR formula alternative using the COUNTIF function, and the different results you can obtain when applying that formula instead of the XOR function.
For purposes of this example, let’s assume that you own a company that specializes in animal transport services.
Every day, you receive a report that shows how all the cages owned by the company are going to be used the following day. More precisely, the report contains a table that lists the cages (identifying them by their number) and which particular animal(s) is(are) going to be carried within the cage.
For this first example, we’ll start with the table shown below. The first column contains the cage number. The second and third columns specify whether a cat and/or a dog is planned to be carried within that particular cage (TRUE) or not (FALSE).
This table is included in the Excel workbook that accompanies this tutorial. You can get immediate free access to this workbook by clicking here.
Due to company a policy, a particular cage can only carry a cat or a dog (but not both). Therefore, you must check the report every day, and ensure that no cage is planned to house both a dog and a cat.
If any cage isn’t in compliance with company policy, you must talk to your employees before the end of the day to reorganize the cage assignments.
In other words, you must read the whole table and check that there’s no row where both columns (Contains a Cat? and Contains a Dog?) have the logical value TRUE. You have to this every day. Additionally, you always have some time pressure, since you need to complete the analysis before day’s end.
In this particular example, the whole table contains 100 rows. In practice, you may face situations where the data set is much larger than this.
Regardless of the amount of data, you don’t want to go through all of the 100 rows comparing both columns to determine whether a particular cage is planned to house both a dog and a cat.
This looks like a situation where using the XOR function may come in handy. More precisely, you can use the XOR function to determine whether cages are or not compliant with the company policy that requires that each cage carry either a dog or a cat (but not both).
Let’s take a look at how you can do this in 3 easy steps:
Step #1: Add A Column To The Table
The first step is to add a new column to the table. This column is where the XOR function is implemented.
Step #2: Enter A Formula Using The XOR Function
I explain the syntax of the XOR function above.
In this particular case, the XOR function uses 2 arguments: (i) a cell from the second column (Contains a Cat?), and (ii) a cell from the third column (Contains a Dog?). Therefore, the formula for any particular row in the last column is:
The following screenshot shows how this looks like in the sample workbook accompanying this Excel tutorial:
Notice that, in this case, the XOR function for Cage No. 1 appears in cell E6. The formula within that cell is:
Step #3: Copy And Paste The Formula
In order to have Excel apply the XOR function you’ve created to all the other rows, simply copy and paste the formula across all the relevant rows. Notice how the results are as follows:
- If a cage is planned to carry a cat or a dog (but not both), the XOR function returns TRUE.
- The XOR function returns FALSE if a cage is (i) expected to be empty (both cells contain the FALSE logical value), or (ii) is planned to carry both a cat and a dog (both cells return TRUE).
The following image highlights some of this results within the sample workbook:
Now that you know how to apply the XOR function to a relatively simple case, let’s take a look at what can you do in situations where the number of conditions is larger:
How To Use The XOR Function And Its Alternative In Excel: An Example With 5 Conditions
Let’s assume that your animal transportation services company has (sort of) expanded its array services. In addition to having the ability to transport cats and dogs, you can now transport pigs, rabbits and birds.
However, you continue to have the same company policy regarding cages: a particular cage can only carry 1 cat, dog, pig, rabbit or bird. You can’t, for example, have a cage that carries both a dog and a pig, or a rabbit and a bird.
You continue to receive a daily report that shows how all of the cages owned by the company are going to be used the following day. An example of such a report is shown in the following image:
You can get immediate free access to the Excel workbook that accompanies this tutorial (and contains this table) by clicking here.
Luckily, you’ve not added additional cages yet. Therefore, the report continues to have 100 rows, as in the example above.
The problem is that you now have to check the 5 columns of each row and determine whether only 1 of them contains the logical value TRUE. In order to avoid doing this every day, you can use Excel formulas.
However, in this particular case, the XOR function itself isn’t very helpful. The reason for this is that XOR returns TRUE when an odd number of the logical tests return TRUE. In other words, when applied to the table above, XOR returns:
- TRUE if a particular cage carries 1, 3 or 5 types of animals.
- FALSE if the cage carries 2 or 4 types of animals.
Therefore, in the following sections I show you:
- The results obtained when applying the XOR function to the table above, and why this isn’t the best formula to use for solving the problem we’re analyzing.
- How you can apply the alternative formula I introduce above to check whether only 1 (and not more) of the logical tests returns TRUE.
For these purposes, I add 2 columns to the report table. The purpose of each of these columns is to store the formulas I explain below and display the results obtained with the XOR function and its alternative.
Let’s start by taking a look at the results obtained when applying the XOR function to this situation:
Example: How To Use The XOR Function With 5 Arguments
I explain the general syntax of the XOR function above. When working with 5 arguments, as in this case, the XOR function looks as follows:
The following image shows how the formula for Cage No. 1 looks like:
The formula, located in cell H6 of the worksheet is:
I copy and paste the formula for purposes of applying the XOR function to all the rows in the table. The following image shows the results returned by Excel:
Notice that, as expected, the results obtained by applying the XOR function aren’t precisely what you need in order to determine whether a particular cage is compliant with the company policy requiring that only 1 type of animal be carried in any cage.
The following image shows several examples of situations where the XOR function returns TRUE for cages that aren’t in compliance with this policy. Notice how, in all of the rows highlighted below, there is more than 1 TRUE in the columns containing the arguments of the XOR function.
In fact, there’s only a handful of cages that are compliant with the company policy. In other words, there are only a few rows where the arguments used for the XOR function include a single TRUE.
As it the above screenshots evidence, the XOR function isn’t appropriate for this situation. Therefore, let’s look at an alternative formula:
Example: Alternatives To Excel’s XOR Function
I introduce an alternative to the XOR Function above. This returns TRUE when only 1 (and not more) of the logical tests it evaluates returns TRUE.
For this example, the syntax of such formula is roughly as follows:
The following screenshot shows how the formula for the first cage (in cell I6) looks like:
As shown above, this formula is:
The following image shows how the Excel worksheet looks like after I copy and paste this formula in all the relevant rows:
Notice how, the results obtained when applying this alternative function are what you need to answer the question of whether a particular cage is compliant with the company policy. As shown below, most of the cages aren’t compliant (they’re planned to carry more than 1 type of animal) and, therefore, this alternative formula returns FALSE in most cases.
However, perhaps more importantly, this alternative formula returns TRUE in the few cases where the cages are compliant with company policy. As an example, the image below highlights 3 rows in which the formula returns TRUE.
Notice how, in all of these cases, there is only 1 TRUE in the 5 cells specifying whether a cat, dog, pig, rabbit or bird is going to be housed in the cage:
As shown by these results, you should use this alternative formula instead of XOR whenever you carry several logical tests and want Excel to return TRUE if only 1 of those tests returns TRUE.
In this particular example, the alternative to the XOR function returns a very small amount of TRUE logical values. This is an indication that, perhaps, your animal transport services company is growing fast and is time to buy more cages to house the increasing amount of animals you transport. Even though this may be a problem, it’s probably a good one to have.
In addition to the above, now you know how to use the XOR function and build an alternative Exclusive Or formula for those situations where you may need it. This means that you no longer have to manually determine whether each and every cage is compliant with the company policy. This is one less problem for you 🙂
After reading this Excel tutorial, you probably have a very good understanding of the XOR function in Excel.
This knowledge isn’t limited to just knowing what is the purpose and syntax of the function. In this blog post, you’ve also read about important matters surrounding the XOR function, such as:
- What is the process followed by Excel’s XOR function.
- Why, when working with more than 2 logical tests, the XOR function in Excel may not work as you expect.
- How to use the COUNTIF function and Boolean logic to create a formula that serves as an alternative to the XOR function when working with more than 2 logical tests.
As shown by the examples above, knowing how to use the XOR function (and its alternatives) in Excel may help you free up some of your time to focus on the things that matter the most. So, once you’ve starting saving some time when working with Excel, please make sure to take a minute to leave a comment below sharing:
Do you use Exclusive Or when working with Excel?
I’m quite interested to know whether you use the XOR function (or, more generally, formulas to implement the logical Exclusive Or) when working with Excel. If you do, please let the rest of us know the context in which you do so.
Also, if you use alternative formulas to implement the logical Exclusive Or, please make sure to share them with the rest of us. You never know who you can help with this.
Finally, if you’ve ever used the XOR function when working with more than 2 logical tests (as in the example above), please let me know the context in which you’ve done so. The reason I ask this is that I generally tend to agree with Excel authority Bill Jelen who, in Power Excel with Mr. Excel, states that:
(…) although the function says “XOR”, it really is a bizarre function to measure if there are an odd number of True values. I have no idea when you will need that.
So I’m very curious to know if any of you has found practical applications (outside of electrical engineering) for the XOR function when working with more than 2 logical tests.
Books Referenced In This Excel Tutorial
Click on any of the images below to purchase the book at Amazon.