This function is a notch higher than everyday functions, but is nevertheless used by many Excel pros. At first, the function might seem confusing, but we’re here to break it down and help you master it. Read on to find out what the XOR function is and how you can use it, along with some examples.
What Is the XOR function in Excel?
The XOR function in Excel is a logical test within a range of values. The XOR function returns either TRUE or FALSE as output. The syntax for this function is as below:
You must enter at least one argument for XOR to work. If you enter a range of cells as an argument, then the XOR function will count cells with values in them as TRUE, and will ignore cells containing text and blank cells. The XOR function’s ultimate output is either TRUE or FALSE.
TRUE: If there’s an odd number of cells that meet the conditions, the TRUE statement is returned. FALSE: If there’s an even number of cells that meet the conditions, or if there are no cells that meet the conditions, the FALSE statement is returned.
Understanding the XOR function in Excel might be a bit tough at first, but with a couple of examples and experiments, you’ll get the hang of it.
How to Use the XOR Function in Excel
Let’s start with an educational example to better understand XOR. Once you get a good understanding of how this function works, we’ll finish up with a practical example.
In this example, we have six numbers in a table. These numbers are 0, 1, and 2. The goal is to use the XOR function to see whether we have an odd number of cells containing 1, or an even number of them. We’re going to use this function for two ranges, so that you can see both TRUE and FALSE scenarios.
Select the cell where you want to output the XOR results. In the formula bar, enter the formula below: =XOR(A1:B2=1) This formula calls on the XOR function to check how many cells in the range A1:B2 equal 1. If the answer is an odd number, the formula will return TRUE. If the answer is an even number, including zero, the formula will return FALSE. Press Enter.
As you can see, since there are three cells containing 1 the formula returns TRUE. Now let’s try it on an extended range.
Select the cell where you want to display the XOR output. Enter the formula below in the formula bar: =XOR(A1:B3=1) Just like the previous formula, this formula calls on XOR to test how many cells in the range A1:B3 equal 1. If the answer is odd, TRUE is returned. If the answer is even or zero, FALSE is returned. Press Enter.
Since there are four cells containing 1 in this range, the formula returns FALSE. You can experiment with different arguments and ranges to better grasp the XOR function.
XOR Function Example
Now that you’ve got a good idea of how XOR works in Excel, let’s see it in action with a practical example. In practice, the XOR function in Excel is mostly used along with other Excel functions. A popular combination of the XOR function is with the IF function in Excel.
Combining IF and XOR, you can have two logical tests: XOR will check the values and return either TRUE or FALSE, and the IF function will read this output, and depending on whether the output from XOR is TRUE or FALSE, will return a custom output.
Suppose that we’re coordinating a local football tournament. The rules are in a way that if a team wins its first two matches, they proceed to the next stage and won’t have to do a third match in that stage. If a team loses both its first two matches, they are eliminated, and won’t do a third match.
However, if a team wins a match and loses another, then they will have to do a third match to determine whether they will proceed to the next stage or not.
In this sample Excel spreadsheet, we have a list of the football teams, and the outcome of their two matches. The goal is to use a combination of the XOR and IF functions in Excel to determine which teams will have to do a third match.
To break it down further, we’re going to look for the term Loss in each row using XOR. Here’s how the XOR function will behave:
If a team has won all its matches, then the count of cells containing Loss will be zero, which will output FALSE. If a team has lost all its matches, then the count of the cells containing Loss will be two, which is an even number and will output FALSE. If a team has won one match and lost the other, the count of the cells containing Loss will be one, which is an odd number and will output TRUE.
This way, the XOR function is enough on its own to determine whether a team will have to do a third match or not. To improve the looks of the spreadsheet, we’re going to use the IF function to translate the scary full caps TRUE and FALSE into gentler Yes and No. Let’s get started.
Select the first cell where you want to output the XOR results. In this example, that will be the first cell under the “Will Need Third Match” column. In the formula bar, enter the formula below: =XOR(B2:C2=“Loss”) This formula will call on the XOR function to look through the range B2:C2 and count how many cells contain the phrase Loss. If the result is an odd number, the formula will return TRUE. If not, then the formula will return FALSE. Press Enter.
You will now see whether the first team requires a third match or not. You can go ahead and use the autofill tool in Excel to see the result for the rest of the teams as well. But our job here is not done yet. We’re going to couple XOR with the IF function to get Yes or No as results.
Select the first cell under the “Will Need Third Match” column. In the formula bar, change the formula to below: =IF(XOR(B2:C2=“Loss”), “Yes”, “No”) You already know what the XOR function does here, but this time it is used as a logical test inside the IF function. If the result of the XOR function is TRUE, then the IF function will return Yes. Otherwise, it will return No. Press Enter.
Now you’ll see the results from the XOR and IF functions as either Yes or No. Grab the fill handle and drop it on the cells below to see the results for them as well. Finally, you can also add some conditional formatting to your Excel cells to make them look better, and improve their readability.
True or False? XOR Knows
XOR is a bit different from some of the more common Excel functions, but it is an efficient function in scenarios where the odd or even counts of cells are important. The XOR function is often used in conjunction with other Excel functions, such as IF, and now you know how to use it. Once you learn to use the XOR function and experiment with it, you’ll soon see yourself using it in many Excel scenarios.
