XOR Function in Excel
Logical formulas rule the most in excel functions. One or other ways we need to use logical functions in excel. We all must have used IF, AND, OR conditions in combinations. But we have not seen much of other underutilized excel function “XOR”.
If you have already used OR function in excel then XOR function becomes easy. OR function returns TRUE if any one of the provided logical tests is satisfied and if all of the provided logical tests are not satisfied then only it gives FALSE as the result. More importantly, if all the provided logical tests are satisfied then we get TRUE only. However, XOR is slightly different here, if all the provided logical tests are satisfied then we will get FALSE.
- In order to get the result as TRUE at least any one of the provided logical tests must be FALSE.
- If all are FALSE then the result is FALSE only, so in simple words in order to get TRUE as the resulting formula should contain at least one TRUE and at least one FALSE results.
Syntax
XOR syntax is as same as OR & AND functions in Excel. 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 don’t require any special explanations. Now we will move to examples of XOR function.
Examples to use XOR Function in Excel
Example #1
Just to start off with we will show you a simple example of XOR function. Consider the below data.
- Step 1: From this data, we need to identify whether Number 1 & Number 2 are less than 21. So open the XOR function in the Status column.
- Step 2: Select Number 1 as a logical1 argument.
- Step 3: Our logical test is whether the selected number is <21 or not, so provide the test as A2 < 21.
- Step 4: For logical2 argument also apply the same logical test by selecting Number 2.
- Step 5: Press Enter to get the answer.
Here, 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 a logical condition of 21 so the result is TRUE. Since logical results are a combination of the TRUE and FALSE final result is TRUE.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
Step 6: Drag the formula to other cells as well.
Now look at the third result, in this Number 1 is 20 which is less than 21, so the result is TRUE and Number 2 also 20 which is also less than 21, so the result is TRUE. Since all the logical results are TRUE our XOR final result is FALSE.
So in order to get TRUE at least one TRUE and one FALSE result should be there.
Example #2
XOR function works slightly differently when we apply more than 2 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 and out these five if 1, 3, 5 logical tests are TRUE then the final result is also TRUE. If out of 5 logical tests, 4 logical tests are TRUE then the final result is FALSE.
For example, look at the below formula.
In the above example, we have applied the XOR function to test whether all the numbers are >5 or not.
In the first formula, we got TRUE because look at each number.
- Num 1 is less than 5 so the result is FASLE,
- Num 2 is less than 5 so the result is FALSE,
- Num 3 is less than 5 so the result is FASLE,
- Num 4 is greater than 5 so the result is TRUE and
- Num 5 is less than 5 so the result is FALSE.
In 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) 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 count final result is also TRUE if the count of the TRUE result is even final result is FALSE.
Things to Remember
- XOR function available from the 2013 version.
- XOR can accept 254 logical tests.
- It works differently when we apply more than 2 logical tests in excel.
- Wherever we get the odd number of TRUE count final result is also TRUE.
- If the count of the TRUE result is even final result is FALSE.
Recommended Articles
This has been a guide to XOR Function in Excel. Here we discuss how to use XOR Function in Excel along with the examples and downloadable excel sheet. You can learn more about excel function from the following articles –
- Reverse Order in Excel
- Excel Logical Operators List
- Nested If Function in Excel
- Insert Function Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
