XOR in Excel

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Download FREE XOR In Excel Template and Follow Along!
XOR Function Excel Template.xlsx

Table Of Contents

arrow

XOR Function in Excel

Logical formulas rule the most in Excel functions. For other ways, we need to use logical functions in Excel. For example, we all must have used IF, AND, OR conditions in combinations. But we have not seen much of the other underutilized Excel functions, "XOR."

For example, suppose we have 40 as “Number1” in cell A1 and 50 as “Number2” in cell B1. Using the XOR function in Excel, we would get the following result:

=XOR(A1<21,B1<21)

=FALSE

The XOR function becomes easy if you have already used the OR function in excel. The OR function returns "TRUE" if any of the provided logical tests are satisfied. On the other hand, if all of the provided logical tests are not satisfied, then only it gives "FALSE."

More importantly, if all the provided logical tests are satisfied, we get "TRUE" only. However, XOR is slightly different here. If all the logical tests are satisfied, we will get "FALSE."

  • To get the result as "TRUE," at least any of the provided logical tests must be "FALSE."
  • If all are "FALSE," the result is "FALSE." To get "TRUE," the resulting formula should contain at least one "TRUE" and at least one "FALSE" result.

Syntax

XOR syntax is as same as OR & AND functions in Excel. Below is the syntax of the XOR function.

XOR Syntax

  • Logical 1: what is the first logical test?
  • Logical 2: what is the second logical test?

Like this, we can apply 256 logical tests. Since it is the same syntax as the other two, logical Excel functions do not require special explanations. Now, we will move to examples of XOR functions.

Examples to use XOR Function in Excel

Example #1

We will show you a simple example of the XOR function. Consider the below data.

XOR in Excel Example 1.1

The steps to use the XOR function in Excel are as follows.

  1. From this data, we need to identify whether "Number 1" and "Number 2" are less than 21. So, open the XOR function in the "Status" column.


    XOR in Excel Example 1.2

  2. Select "Number 1" as a logical1 argument.


    XOR in Excel Example 1.3

  3. Our logical test is whether the selected number is <21 or not, so provide the test as A2 < 21.


    XOR in Excel Example 1.4

  4. For the logical2 argument, apply the same logical test by selecting "Number 2."


    XOR in Excel Example 1.5

  5. Press the "Enter" key to get the answer.


    XOR in Excel Example 1.6
    The "Number 1" is 22, which is greater than the logical condition of 21, so the result is "FALSE," and "Number 2" is 14, which is less than the logical condition of 21, so the result is "TRUE." Since logical results combine "TRUE" and "FALSE," the final result is "TRUE."

  6. Drag the formula to other cells as well.


    XOR GIF
    Now, look at the third result. In this, "Number 1" is 20, less than 21, so the result is "TRUE," and "Number 2" is also 20, which is also less than 21. So the result is "TRUE." Since all the logical results are "TRUE," our XOR's final result is "FALSE."

So to get "TRUE," at least one "TRUE" and one "FALSE" result should be there.

Example #2

The XOR function works slightly differently when we apply more than two logical tests.

Excel XOR function returns "TRUE" when more than two logical tests "TRUE" result is odd. For example, if there are 5 logical tests. Out of these five, if 1, 3, and 5 logical tests are "TRUE," the final result is "TRUE." On the other hand, if out of 5 logical tests, 4 logical tests are "TRUE," the final result is "FALSE."

Look at the below formula.

Example 2.1

We have applied the XOR function in the above example to test whether all the numbers are >5.

We got "TRUE" in the first formula because of looking at each number.

  • Num 1 is less than 5, so the result is FALSE
  • Num 2 is less than 5, so the result is FALSE
  • Num 3 is less than 5, so the result is FALSE
  • Num 4 is greater than 5, so the result is TRUE 
  • Num 5 is less than 5, so the result is FALSE

This overall count of the "FALSE" result is 4, and an overall count of the "TRUE" result is 1. Since the count of the "TRUE" result is odd (i.e., 1), the final result is also "TRUE."

For a better understanding, look at the below table.

Example 2.2

When we see wherever we get an odd number of "TRUE" counts, the final result is also "TRUE." If the count of the "TRUE" result is even, the final result is "FALSE."

Things to Remember

  • The XOR function is available from the 2013 version.
  • The XOR function can accept 254 logical tests.
  • It unusually works when we apply more than two logical tests in excel.
  • Wherever we get the odd number of "TRUE" counts, the final result is also "TRUE."
  • If the count of the "TRUE" result is even, the final result is "FALSE."