Excel XOR function

Summary

The Excel XOR function, known as “exclusive OR,” is a logical function that evaluates multiple conditions and returns TRUE if an odd number of conditions are true. Specifically, with two logical statements, XOR returns TRUE if exactly one statement is TRUE, and FALSE if both are TRUE or both are FALSE.
Syntax
				
					=XOR(logical1, [logical2], ...)
				
			
  • logical1: The first condition or logical value to evaluate.
  • logical2: [Optional] Additional conditions or logical values to evaluate.
Return value
TRUE if an odd number of the arguments evaluate to TRUE; otherwise, FALSE.

How to use

Use the XOR function when you need to check multiple conditions and want to return TRUE only when an odd number of those conditions are true. This function is particularly useful in scenarios requiring a strict one-condition-only logic or in creating toggles and switches in your data.

Examples

Simple XOR
Single Toggle Activation: To check if only one of two features is activated:
				
					=XOR(A1=1, B1=1)
				
			
This formula returns TRUE if either A1 or B1 equals 1, indicating a single feature is activated, but not both.
XOR and IF
Conditional Response Based on Single Criteria Met: To provide a response only if one specific condition is met out of two:
				
					=IF(XOR(C1>50, D1<20), "Unique Condition Met", "No Unique Condition Met")
				
			
This formula checks if either C1 is greater than 50 or D1 is less than 20, but not both, to provide a unique response.
XOR with Multiple Conditions
Odd-Numbered Activation Check: To determine if an odd number of cells contain the word “Active”:
				
					=XOR(E1="Active", F1="Active", G1="Active")
				
			
This formula returns TRUE if exactly one or all three cells (E1, F1, G1) contain the word “Active,” indicating an odd number of activations.

Additional Notes

  • XOR is useful for scenarios where you need a strict differentiation between conditions, ensuring only one, or an odd number, is met.
  • Remember, XOR evaluates to TRUE when the number of TRUE conditions is odd and FALSE when it is even, including zero.
  • Ensure all arguments provided to XOR can be evaluated as logical values. Non-logical inputs will result in an error.
  • XOR was introduced in Excel 2013, so it may not be available in earlier versions. Consider using a combination of AND, OR, and NOT for similar logic in those versions.

Related Functions

Excel IF function

The Excel IF function checks a condition to return values for TRUE or FALSE outcomes, ideal for dynamic decision-making.

Excel NOT function

The Excel NOT function inverts logical values, turning TRUE to FALSE and vice versa, crucial for dynamic conditional logic in formulas.

Excel AND function

The Excel AND function is a logical function that is used to check multiple conditions at the same time. AND returns TRUE if all conditions evaluate TRUE.

Excel OR function

The Excel OR function checks multiple conditions, returning TRUE if any are true, enabling flexible decision-making criteria.

Content Navigation