How to Use XOR Function in Excel [With Example]

The XOR Function is a logical function that was introduced in Excel 2013 and later versions. The XOR Function if used with two conditions and one of the conditions evaluates to true, then the XOR Function returns true, this is similar in a way to the standard OR Function in this case. However if both of the conditions evaluate to false, then the XOR Function returns false, and if both of the conditions evaluates to true then the XOR Function returns false. This is where the significant difference in the way conditions are handled, comes into play.

When the XOR Function is used in conjunction with multiple conditions, then true is returned if there is an odd number of true conditions, and false is returned if there is an even number of true conditions.

So, let’s get started with a simple example to illustrate how to use the XOR Function

Introduction

We are going to use the XOR Function to test conditions in the case where there are two conditions. The source data is shown below.

Excel XOR Function - Image 1

Testing Two Conditions when one condition is true the other is false

1) So the first thing we want to do is test the situation where one condition is true and one is false so, in cell A7, we input the following formula:

=XOR(A5=”Apples”,A6=”Cherries”)

Excel XOR Function - Image 2

2) Upon pressing CTRL-ENTER, a value of TRUE is delivered, since the text in A5 was equal to Apples, but the text in A6 was not equal to Cherries. Remember when entering text directly into a formula always use quotation marks.

Excel XOR Function - Image 3

Testing Two Conditions when one condition is true the other is true

1) So in this case, the first thing we want to do is test the situation where one condition is true and the other one is true as well so in cell C7, we input the following formula:

=XOR(C5=”Apples”,C6=”Pears”)

Excel XOR Function - Image 4

2) Upon pressing CTRL-ENTER a value of FALSE is delivered, since the text in C5 was equal to Apples, and the text in C6 was equal to Pears. When the XOR Function picks up both conditions out of the two are true it delivers a value of FALSE.

Excel XOR Function - Image 5

Testing Two Conditions when one condition is false and the other is also false

1) So in this case, the first thing we want to do is test the situation where one condition is false and the other one is false as well so in cell E7, we input the following formula:

=XOR(E5=”Oranges”,E6=”Cherries”)

Excel XOR Function - Image 6

2) Upon pressing CTRL-ENTER a value of FALSE is delivered, since the text in E5 was equal to Oranges, and the text in E6 was not equal to Cherries. When the XOR Function picks up both conditions out of the two are false it also delivers a value of FALSE.

Excel XOR Function - Image 7

3) One can step through the formula, by selecting cell E7 and going to Formulas>Formula Auditing>Evaluate Formula, this is especially helpful in the case of the XOR Function.

Evaluating Multiple Conditions

We now want to see how the XOR Function handles multiple conditions. The source data is shown below.

Excel XOR Function - Image 8

Testing Four Conditions when one condition is false and the other three are true (an odd number of trues)

1)  So in this case, the first thing we want to do is test the situation where one condition is false and the other three are true, so in cell A9, we input the following formula:

=XOR(A5=”Lemons”,A6=”Pears”,A7=”Cherries”,A8=”Oranges”)

Excel XOR Function - Image 9

2) Upon pressing CTRL-ENTER a value of TRUE is delivered, since we had one condition that evaluated to false, namely Apples was not equal to Lemons and the other three evaluated to true, therefore the XOR function delivered a value of TRUE since the total number of TRUES was an odd number.

Excel XOR Function - Image 10

Testing Four Conditions when two conditions are false and the other two are true (an even number of trues)

1)  So in this case, the first thing we want to do is test the situation where two conditions are false and the other two are true, so in cell C9, we input the following formula:

=XOR(C5=”Lemons”,C6=”Limes”,C7=”Cherries”,C8=”Oranges”)

Excel XOR Function - Image 11

2) Upon pressing CTRL-ENTER a value of FALSE is delivered, since we had one condition that evaluated to false, namely Apples was not equal to Lemons and another condition that also evaluated to false namely Pears was not equal to Limes and the other two conditions evaluated to true, therefore the XOR function delivered a value of FALSE.

Excel XOR Function - Image 12

And there you have it.

Download Working Files

XOR-Function-Excel

Conclusion

The XOR Function is a logical function which can be a bit tricky to master at first, but once you practice and get the hang of it, it becomes easier to understand. The XOR Function has uses in certain sports industries and in the computer science domain.

Please feel free to comment and tell us if you use the XOR Function in your spreadsheets.

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS) and biofuels. She enjoys showcasing the functionality of Excel in various disciplines.

In her spare time when she’s not exploring Excel or Access, she is into graphic design, amateur photography and caring for her two pets, Pretzel and Snoopy.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve the Math * Time limit is exhausted. Please reload CAPTCHA.