FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

XOR in Excel

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

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 excelOR Function In ExcelThe OR function in Excel is used to test various conditions, allowing you to compare two values or statements in Excel. If at least one of the arguments or conditions evaluates to TRUE, it will return TRUE. Similarly, if all of the arguments or conditions are FALSE, it will return FASLE.read more. 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 ExcelAND Functions In ExcelThe AND function in Excel is classified as a logical function; it returns TRUE if the specified conditions are met, otherwise it returns FALSE.read more. 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.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples to use XOR Function in Excel

You can download this XOR Function Excel Template here – XOR Function Excel Template

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

Recommended Articles

This article has been a guide to XOR Function in Excel. Here, we discuss using the XOR function in Excel along with the examples and downloadable Excel sheets. You can learn more about Excel functions from the following articles: –