Best value

How to Use Excel XOR Function (5 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

Excel XOR function is an exclusive OR function. The XOR function returns TRUE if an odd number of logical statements are TRUE and FALSE if an even number of logical statements are TRUE. In the case of two given arguments, the XOR function returns TRUE whenever one argument is TRUE and FALSE whenever both arguments are TRUE or FALSE.

Excel XOR Function-Quick View

In this article, you’ll get to learn how to use the XOR function with case-based examples.


Excel XOR Function: Syntax and Arguments

⦽ Function Objective:

Performing exclusive OR function

⦽ Syntax:

XOR(logical1, [logical2],…)

Syntax-Excel XOR Function

⦽ Arguments Explanation:

                    Arguments                 Required/Optional                       Explanation
                         logical1                       Required         Any Kind of Logical Expression
                         logical2                       Optional         Any Kind of Logical Expression

⦽ Return Parameter:

TRUE or FALSE, depending on the odd or even number of logical statements being TRUE or FALSE respectively.

⦽ Version:

Excel 2019


How to Use the Excel XOR Function: 5 Suitable Examples

Example 1: XOR Function Compares Two Numbers as Logical

The XOR function in-built takes logical statements that can be TRUE or FALSE. Afterward, the XOR function returns TRUE or FALSE depending on logical statements’ number of TRUE or FALSE.

We can come up with logical status for both values inserted as logical in TRUE or FALSE if one of them is TRUE or both are either TRUE or FALSE.

Enter the following logical operations with XOR functions in cells (i.e.,C6,C7,C8)

⭆(2>1,0<1)

⭆(2>1,0>1)

⭆(2<1,0>1)

Comparing two numbers-Excel XOR Function

The logical operations are built by comparing two numbers with a condition. If any logical statement satisfies the condition, it returns TRUE otherwise FALSE. Then XOR returns

🔄 FALSE; If both logical statements are TRUE (i.e.,2>1 is TRUE, and 0<1 is TRUE) or FALSE (i.e.,2<1 is FALSE, and 0>1 is FALSE).

🔄 TRUE; If one of the logical statements is TRUE or FALSE (i.e.,2>1 is TRUE, and 0>1 is FALSE).

Comparing two numbers Result


Example 2: Using XOR Function to Predict Real-Life Events

The XOR function can predict real-life events considering we give in. Suppose we want basketball match 3 to be held on the basis of prior two matches among four basketball teams.

Real life event

The conditions we impose are

🔄 Teams won both matches, won’t play the Match 3.

🔄 Teams that won any one match, will play Match 3.

🔄 Teams won none of the matches, won’t play the Match 3.

Paste the following formula in any adjacent cell (i.e., E6).

=IF(XOR(C6="Won",D6="Won"),"YES","NO")

Inside the formula,

The XOR function returns FALSE if both cell references match (i.e., Won and Won) or not (i.e., Lost and Lost) otherwise TRUE. Then IF shows “NO” or “YES” in case of FALSE or TRUE XOR passing respectively.

Formula

Hit ENTER and drag the Fill Handle to bring out other outcomes regarding our imposed conditions.

Prediction of real life events-Excel XOR Function

Now, we can cross-check the outcomes by comparing them with imposed conditions. We find any teams that lose one match will play match 3 otherwise won’t and this scenario matches with imposed conditions.


Example 3: Comparing Two Logicals

The XOR function consists of built-in logicals. In this case, we discuss what if we have two given logical outputs in TRUE or FALSE and XOR has to decide what will be the resultant statement TRUE or FALSE?

Type the below formula in any adjacent cell (i.e.,D6)

=XOR(B6,C6)

XOR assigns B6 and C6 as logicals.

Comparing two logicals

Press ENTER then Drag the Fill Handle. Any single TRUE or FALSE logicals results in TRUE otherwise FALSE as shown in the following picture.

Comparing two logicals-Excel XOR Function


Example 4: Comparing Multiple Logicals

To demonstrate XOR’s other characteristics, we take multiple logical statements. Then use the XOR to produce outcomes depending on the logicals.

As we mentioned earlier, XOR results TRUE if there is an odd number of TRUE logicals otherwise FALSE.

Write down the following formula in any blank cells (i.e., G6).

=XOR(B6:F6)

Comparing multiple logicals-Excel XOR Function

Hit ENTER and Drag the Fill Handle. You’ll see an odd number (i.e., 1,3,5) of logicals resulting in TRUE causes XOR function to evaluate them as TRUE otherwise FALSE.

Comparing multiple logicals result


Example 5: Customizing Return Value Depending on Duplicates

Let’s say, in an office, we have employees who enter or exit the office at any time. Now, we want to check if any employees are In or Out of the office depending on name entries.

Paste the below formula in any adjacent cell (i.e., D6).

=IF(XOR(B6=B$6:B6),"In","Out")

Inside the formula B6=B$6:B6 returns TRUE if the entry in B6 matches with the entry ranges from B6 to B6. Any even or double occurrence of any employee names results in FALSE.

Then IF returns “In” in case of a single occurrence or “Out” in case of double or duplicate occurrence of any names.

Customizing return value

Press ENTER then Drag the Fill Handle. The formula labels “Out” to the duplicates among the entries.

Customizing return value result -Excel XOR Function


⧭ Things to Keep in Mind

🔼 XOR results #VALUE if it finds no logical values.

🔼 The XOR function was first introduced in 2013.

🔼 The logical statements must result in TRUE (i.e.,1) or FALSE (i.e.,0) otherwise refer to anything containing the logical value.

🔼 XOR ignores blank cells and can be used as an array formula.


Download Excel Workbook


Conclusion

I hope the above-described uses of the XOR function intrigue you to use the function more efficiently. If you have further queries or feedback, please let me know in the comment section.


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo