How to Use Excel XOR Function (5 Suitable Examples)

Excel XOR Function: Syntax and Arguments

⦽ Function Objective:

Performing exclusive OR function

⦽ Syntax:

XOR(logical1, [logical2],…)

⦽ Arguments Explanation:

⦽ Return Parameter:

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

⦽ Version:

Excel 2019

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)

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

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

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

Suppose we want basketball match 3 to be held on the basis of prior two matches among four basketball teams.

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.

⧫ Enter the following formula in cell E6.

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.

IF shows “NO” or “YES” in case of FALSE or TRUE XOR passing respectively.

⧫ Press ENTER and use the Fill Handle for the remaining cells.

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 they won’t play match 3 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?

⧫ Enter the following formula in cell D6.

XOR assigns B6 and C6 as logicals.

⧫ Press ENTER and use the Fill Handle tool for the remaining cells. Any single TRUE or FALSE logicals results in TRUE otherwise FALSE as shown in the following image below.

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.

⧫ Enter the following formula in cell G6.

⧫ Press ENTER and drag down 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.

Example 5 – Customizing Return Value Depending on Duplicates

For example, in an office, we have employees who enter or exit the office at any time. We want to check if any employees are In or Out of the office depending on name entries.

⧫ Enter the following formula in cell D6.

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.

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

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

Download Excel Workbook

<< Go Back to Excel Functions | Learn Excel