XOR Function in Excel Tutorial

Excel Tutorial

Introduction

The XOR function in Excel performs an exclusive OR logical test. It returns TRUE when an odd number of supplied conditions are TRUE. It returns FALSE when an even number of conditions are TRUE.

XOR is useful when you want to check whether exactly one condition is TRUE, or evaluate multiple conditions where the number of TRUE results matters.

What Is the XOR Function in Excel?

XOR stands for Exclusive OR. It evaluates two or more logical conditions and returns a TRUE or FALSE result based on how many conditions are TRUE.

  • An odd number of TRUE conditions returns TRUE.
  • An even number of TRUE conditions returns FALSE.

XOR Function Syntax

=XOR(logical1, [logical2], ...)

Arguments:

  • logical1: The first condition you want to test.
  • logical2: Additional conditions you want to test. This argument is optional.

Practical Examples of XOR Function in Excel

Question 1: Check Whether Exactly One Employee Has Completed Training

Problem Statement: A company tracks two training modules. Check whether an employee has completed exactly one of the two modules.

Data:

EmployeeExcel TrainingPower BI Training
RahulCompletedPending
PriyaCompletedCompleted
AmitPendingCompleted
NehaPendingPending

Excel Formula:

=XOR(B2="Completed",C2="Completed")

Expected Result:

EmployeeExcel TrainingPower BI TrainingXOR Result
RahulCompletedPendingTRUE
PriyaCompletedCompletedFALSE
AmitPendingCompletedTRUE
NehaPendingPendingFALSE

Explanation: XOR returns TRUE when exactly one training status is Completed. When both or neither are Completed, it returns FALSE.

Concept Covered: Checking exactly one TRUE condition.

Question 2: Check Whether an Employee Has Used Exactly One Leave Type

Problem Statement: An employee can take Casual Leave or Sick Leave. Check whether the employee has used exactly one of these leave types.

Data:

EmployeeCasual Leave UsedSick Leave Used
RahulYesNo
PriyaYesYes
AmitNoYes
NehaNoNo

Excel Formula:

=XOR(B2="Yes",C2="Yes")

Expected Result:

EmployeeCasual Leave UsedSick Leave UsedXOR Result
RahulYesNoTRUE
PriyaYesYesFALSE
AmitNoYesTRUE
NehaNoNoFALSE

Explanation: XOR checks both leave conditions. It returns TRUE when only one leave type has been used.

Concept Covered: Comparing two text-based conditions.

Question 3: Check Whether Exactly One Sales Target Has Been Achieved

Problem Statement: A sales employee has two targets: Product A sales of at least ₹50,000 and Product B sales of at least ₹30,000. Check whether exactly one target has been achieved.

Data:

EmployeeProduct A SalesProduct B Sales
Rahul6000020000
Priya7000040000
Amit3000035000
Neha4000015000

Excel Formula:

=XOR(B2>=50000,C2>=30000)

Expected Result:

EmployeeProduct A SalesProduct B SalesXOR Result
Rahul6000020000TRUE
Priya7000040000FALSE
Amit3000035000TRUE
Neha4000015000FALSE

Explanation: XOR checks whether each sales target has been achieved. It returns TRUE when only one target is achieved.

Concept Covered: Checking numeric conditions.

Question 4: Check Whether Exactly One Payment Method Has Been Selected

Problem Statement: An online store allows customers to select Cash on Delivery or Online Payment. Check whether exactly one payment method has been selected.

Data:

Order IDCash on DeliveryOnline Payment
ORD101YesNo
ORD102NoYes
ORD103YesYes
ORD104NoNo

Excel Formula:

=XOR(B2="Yes",C2="Yes")

Expected Result:

Order IDCash on DeliveryOnline PaymentXOR Result
ORD101YesNoTRUE
ORD102NoYesTRUE
ORD103YesYesFALSE
ORD104NoNoFALSE

Explanation: XOR returns TRUE when one payment method is selected. It returns FALSE when both or neither are selected.

Concept Covered: Validating exclusive choices.

Question 5: Check Three Conditions Using XOR

Problem Statement: A company checks three employee requirements: attendance of at least 90%, training completed, and performance score of at least 80. Check whether an odd number of these conditions are TRUE.

Data:

EmployeeAttendanceTrainingPerformance Score
Rahul95%Yes70
Priya92%Yes85
Amit80%No90
Neha95%No85

Excel Formula:

=XOR(B2>=90%,C2="Yes",D2>=80)

Expected Result:

EmployeeAttendanceTrainingPerformance ScoreXOR Result
Rahul95%Yes70FALSE
Priya92%Yes85TRUE
Amit80%No90TRUE
Neha95%No85FALSE

Explanation: XOR evaluates all three conditions. Rahul has two TRUE conditions, so the result is FALSE. Priya and Amit each have an odd number of TRUE conditions, so their results are TRUE. Neha also has two TRUE conditions, so the result is FALSE.

Concept Covered: Evaluating three or more logical conditions.

Important Things to Remember

  • XOR stands for Exclusive OR.
  • XOR returns TRUE when an odd number of conditions are TRUE.
  • XOR returns FALSE when an even number of conditions are TRUE.
  • XOR can evaluate two or more logical conditions.
  • It returns a logical result: TRUE or FALSE.
  • A single TRUE condition returns TRUE, while a single FALSE condition returns FALSE.
  • XOR does not mean that exactly one condition must be TRUE when there are three or more conditions.

Student Practice Tasks

Try these questions using the XOR function.

  1. Check whether exactly one of two employees has completed a project.
  2. Check whether exactly one of two products has stock available.
  3. Check whether exactly one of two sales targets has been achieved.
  4. Check whether an employee has completed exactly one of two assigned training programs.
  5. Evaluate four conditions and check whether an odd number of them are TRUE.

Final Student Challenge: Build an Employee Eligibility Report

A company checks three conditions for employee eligibility:

  • Attendance must be at least 90%.
  • Training must be completed.
  • Performance score must be at least 80.

Use XOR to check whether an odd number of these conditions are TRUE.

Given Data:

EmployeeAttendanceTrainingPerformance Score
Rahul95%Yes70
Priya92%Yes85
Amit80%No90
Neha95%No85
Karan85%Yes75

Your Task:

  • Use XOR to evaluate all three conditions.
  • Display the TRUE or FALSE result for each employee.
  • Identify employees with an odd number of TRUE conditions.

Expected Result:

EmployeeXOR Result
RahulFALSE
PriyaTRUE
AmitTRUE
NehaFALSE
KaranTRUE

Key Takeaways

  • XOR is an Excel logical function.
  • It evaluates two or more conditions.
  • An odd number of TRUE conditions returns TRUE.
  • An even number of TRUE conditions returns FALSE.
  • It is useful for checking exclusive choices, sales targets, training status, and business conditions.

Frequently Asked Questions (FAQs)

1. What is the XOR function in Excel?

The XOR function evaluates multiple logical conditions and returns TRUE when an odd number of conditions are TRUE.

2. What is the syntax of the XOR function?

=XOR(logical1, [logical2], ...)

3. When does XOR return TRUE?

XOR returns TRUE when an odd number of supplied conditions are TRUE.

4. When does XOR return FALSE?

XOR returns FALSE when an even number of supplied conditions are TRUE.

5. Can XOR check more than two conditions?

Yes. XOR can evaluate multiple conditions, including three, four, or more logical tests.

6. What is the difference between XOR and checking exactly one condition?

With two conditions, XOR returns TRUE when exactly one is TRUE. With three or more conditions, it returns TRUE when an odd number are TRUE, which may be three, five, or more.

7. What does XOR return in Excel?

XOR returns a logical value: TRUE or FALSE.

8. Which Excel versions support the XOR function?

XOR is available in Excel 2013 and later versions, including Excel 2016, 2019, 2021, 2024, and Microsoft 365.