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.

  • 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.

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

  • 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. Select “Number 1” as a logical1 argument. Our logical test is whether the selected number is <21 or not, so provide the test as A2 < 21. For the logical2 argument, apply the same logical test by selecting “Number 2.” Press the “Enter” key to get the answer. 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.” Drag the formula to other cells as well. 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.”

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.”

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.”

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.

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 FALSENum 2 is less than 5, so the result is FALSENum 3 is less than 5, so the result is FALSENum 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.

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 excelLogical Tests In ExcelA logical test in Excel results in an analytical output, either true or false. The equals to operator, “=,” is the most commonly used logical test.read more.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.”

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: –

  • Reverse Order in ExcelReverse Order In ExcelIn Excel, reverse order is just flipping the data such that the bottom value is on top and the top value is at the bottom.read moreExcel Logical Operators ListExcel Logical Operators ListLogical operators in excel are also known as the comparison operators and they are used to compare two or more values, the return output given by these operators are either true or false, we get true value when the conditions match the criteria and false as a result when the conditions do not match the criteria.read moreNested If Function in ExcelNested If Function In ExcelIn Excel, nested if function means using another logical or conditional function with the if function to test multiple conditions. For example, if there are two conditions to be tested, we can use the logical functions AND or OR depending on the situation, or we can use the other conditional functions to test even more ifs inside a single if.read moreInsert Function ExcelInsert Function ExcelThe Insert function in Excel is not a real function; rather, it is a wizard box that helps users find out what type of function they need in their data. This wizard box can be found under the formula tab in newer versions of Excel.read more