How to Use the XOR Function in Excel
The XOR function in Excel counts the number of cells in a range that meet a specified condition and returns an output based on that number. If the number of cells that meet the condition is odd, XOR returns TRUE, otherwise, it returns FALSE.
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’re able to 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.
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.
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.
As you can see, since there are three cells containing 1 the formula returns TRUE. Now let’s try it on an extended range.
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 withthe 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:
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.
You will now see whether the first team requires a third match or not. You can go ahead and usethe autofill tool in Excelto 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.
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 alsoadd some conditional formatting to your Excel cellsto 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.
Excel isn’t only for business. Here are several Microsoft Excel formulas that will help you solve complex daily problems.
Anyone with more than a passing interest in motorsports must see these films.
You can block out the constant surveillance and restore your privacy with a few quick changes.
So much time invested, and for what?
The key is not to spook your friends with over-the-top shenanigans.
These plugins will make you wonder why you used Photoshop in the first place.
