WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » XOR in Excel

XOR in Excel

XOR Function in Excel

Logical formulas rule the most in excel functions. Of other ways, we need to use logical functions in excel. We all must have used IF, AND, OR conditions in combinations. But we have not seen much of other underutilized excel function “XOR.”

If you have already used the OR function in excel, then the XOR function becomes easy. OR function returns TRUE if any one of the provided logical tests is satisfied and if all of the provided logical tests are not satisfied, then only it gives FALSE as a result. More importantly, if all the provided logical tests are satisfied, then we get TRUE only. However, XOR is slightly different here. If all the provided logical tests are satisfied, then we will get FALSE.

  • In order to get the result as TRUE, at least any one of the provided logical tests must be FALSE.
  • If all are FALSE, then the result is FALSE only, so in simple words, in order to get TRUE as the resulting formula should contain at least one TRUE and at least one FALSE results.

Syntax

XOR syntax is as same as OR & AND functions in Excel. Below is the syntax of the XOR function.

XOR Syntax

  • Logical 1: what is the first logical test?
  • Logical 2: what is the second logical test?

Like this, we can apply 256 logical tests. Since it is the same syntax as the other two logical excel functions don’t require any special explanations. Now we will move to examples of XOR function.

Examples to use XOR Function in Excel

You can download this XOR Function Excel Template here – XOR Function Excel Template

Example #1

Just to start off with we will show you a simple example of XOR function. Consider the below data.

XOR in Excel Example 1.1

  • Step 1: From this data, we need to identify whether Number 1 & Number 2 are less than 21. So open the XOR function in the Status column.

XOR in Excel Example 1.2

  • Step 2: Select Number 1 as a logical1 argument.

XOR in Excel Example 1.3

  • Step 3: Our logical test is whether the selected number is <21 or not, so provide the test as A2 < 21.

XOR in Excel Example 1.4

  • Step 4: For the logical2 argument, also apply the same logical test by selecting Number 2.

XOR in Excel Example 1.5

  • Step 5: Press Enter to get the answer.

Example 1.6

Here, Number 1 is 22, which is greater than the logical condition of 21, so the result is FALSE, and Number 2 is 14, which is less than a logical condition of 21, so the result is TRUE. Since logical results are a combination of the TRUE and FALSE final result is TRUE.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Step 6: Drag the formula to other cells as well.

GIF

Now look at the third result, in this Number 1 is 20, which is less than 21, so the result is TRUE, and Number 2 also 20, which is also less than 21, so the result is TRUE. Since all the logical results are TRUE, our XOR final result is FALSE.

So in order to get TRUE, at least one TRUE and one FALSE result should be there.

Example #2

XOR function works slightly differently when we apply more than 2 logical tests.

Excel XOR function returns TRUE when more than two logical tests TRUE result is odd. For example, if there are 5 logical tests and out of these five, if 1, 3, 5 logical tests are TRUE, then the final result is also TRUE. If out of 5 logical tests, 4 logical tests are TRUE, then the final result is FALSE.

For example, look at the below formula.

Example 2.1

In the above example, we have applied the XOR function to test whether all the numbers are >5 or not.

In the first formula, we got TRUE because look at each number.

  • Num 1 is less than 5, so the result is FASLE,
  • Num 2 is less than 5, so the result is FALSE,
  • Num 3 is less than 5, so the result is FASLE,
  • Num 4 is greater than 5, so the result is TRUE and
  • Num 5 is less than 5, so the result is FALSE.

In this overall count of the FALSE result is 4, and an overall count of the TRUE result is 1. Since the count of the TRUE result is odd (i.e., 1), the final result is also TRUE.

For a better understanding, look at the below table.

Example 2.2

When we see wherever we get an odd number of TRUE count final result is also TRUE if the count of the TRUE result is even final result is FALSE.

Things to Remember

  • XOR function available from the 2013 version.
  • XOR can accept 254 logical tests.
  • It works differently when we apply more than 2 logical tests in excel.
  • Wherever we get, the odd number of TRUE count final result is also TRUE.
  • If the count of the TRUE result is even final result is FALSE.

Recommended Articles

This has been a guide to XOR Function in Excel. Here we discuss how to use XOR Function in Excel along with the examples and downloadable excel sheet. You can learn more about excel function from the following articles –

  • Reverse Order in Excel
  • Excel Logical Operators List
  • Nested If Function in Excel
  • Insert Function Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download XOR Function Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More