In this Excel XOR Tutorial, you **learn how to use the XOR function in your worksheet formulas for purposes of returning a logical Exclusive Or of the applicable arguments**.

The Excel XOR Tutorial is accompanied by an Excel workbook containing the data and formulas I use in the examples below. You can** get immediate free access to this example workbook** by subscribing to the Power Spreadsheets Newsletter.

Use the following Table of Contents to navigate to the section you're interested in.

## Related Excel Tutorials

The following Tutorials may help you better understand and implement the contents below:

- Formulas and functions:
- Learn how to work with the IFERROR function here.

- Macros and VBA:
- Learn how to use worksheet functions in macros here.

You can find additional Tutorials in the Archives.

## #1: XOR with 2 conditions

### XOR with 2 conditions formula

To **use the XOR function with 2 conditions in Excel**, use a formula with the following structure:

`=XOR(Condition1,Condition2)` |

### XOR with 2 conditions process

To **use the XOR function with 2 conditions in Excel**, specify 2 conditions (Condition1 and Condition2).

### XOR with 2 conditions formula explanation

#### Item: XOR

The XOR function returns an Exclusive Or of the 2 conditions (Condition1 and Condition2). In other words, the XOR function does the following:

- Carries out 2 conditional tests (Condition1 and Condition2); and
- Returns TRUE if a single argument (Condition1 or Condition2) is TRUE, and FALSE otherwise. The following truth table illustrates the possible results returned by the XOR function when working with 2 conditions:

Condition1 Condition2 XOR(Condition1,Condition2) TRUE TRUE FALSE TRUE FALSE TRUE FALSE TRUE TRUE FALSE FALSE FALSE

#### Item: Condition1 and Condition2

The Logical1 and Logical2 arguments of the XOR function (Condition1 and Condition2) are the conditions you want to test. The following are the main requirements that apply to these arguments:

- The arguments may be logical values, expressions, arrays, or references.
- The arguments must return a logical value (either TRUE or FALSE). When an argument of the XOR function is an array or reference, the applicable array or reference should contain the logical values or the expressions that are evaluated to logical values.
- If you use an array or reference as argument(s), and it contains text or empty cells, those value are ignored.
- If the arguments don't contain logical values, the XOR function returns an error (#VALUE!).

### XOR with 2 conditions formula example

The worksheet formulas below return an Exclusive Or (XOR) of 2 conditions as follows:

- Condition1: The logical value specified in column A (A6 to A9).
- Condition2: The logical value specified in column B (B6 to B9).

No. | XOR formula |

1 | `=XOR(A6:B6)` |

2 | `=XOR(A7:B7)` |

3 | `=XOR(A8:B8)` |

4 | `=XOR(A9:B9)` |

### Effects of using XOR with 2 conditions formula example

The following image illustrates the results returns by the XOR formula when working with 2 conditions. As expected, the formulas (in cells C6 to C9) return the following logical values:

- TRUE: If a single argument (Condition1 or Condition2) is TRUE (cells C7 and C8).
- False: Otherwise, as follows:
- If both arguments are TRUE (cell C6); or
- If both arguments are FALSE (cell C9).

## #2: XOR with more than 2 conditions

### XOR with more than 2 conditions formula

To **use the XOR function with more than 2 conditions in Excel**, use a formula with the following structure:

`=XOR(ConditionList)` |

### XOR with more than 2 conditions process

To **use the XOR function with more than 2 conditions in Excel**, specify the conditions you want to consider (ConditionList).

### XOR with more than 2 conditions formula explanation

#### Item: XOR

The XOR function returns an Exclusive Or of the conditions you pass as arguments (ConditionList). In other words, the XOR function does the following:

- Carries out as many conditional tests as required (ConditionList); and
- Returns TRUE or FALSE as follows:
- TRUE: If an odd number of conditional tests return TRUE.
- FALSE: If an even number of conditional tests return TRUE.

#### Item: ConditionList

The Logical1, Logical2, … Logical# arguments of the XOR function (ConditionList) are the conditions you want to test. The following are the main requirements that apply to these arguments:

- The first argument (Logical1) is required. Subsequent arguments (Logical2, …, Logical#) are optional. XOR accepts a maximum of 254 arguments.
- The arguments may be logical values, expressions, arrays, or references.
- The arguments must return a logical value (either TRUE or FALSE). When an argument of the XOR function is an array or reference, the applicable array or reference should contain the logical values or the expressions that are evaluated to logical values.
- If you use an array or reference as argument(s), and it contains text or empty cells, those value are ignored.
- If the arguments don't contain logical values, the XOR function returns an error (#VALUE!).

### XOR with more than 2 conditions formula example

The worksheet formulas below return an Exclusive Or (XOR) of 5 conditions as follows:

- Condition1: The logical value specified in column A (A6 to A37).
- Condition2: The logical value specified in column B (B6 to B37).
- Condition3: The logical value specified in column C (C6 to C37).
- Condition4: The logical value specified in column D (D6 to D37).
- Condition5: The logical value specified in column E (E6 to E37).

No. | XOR formula |

1 | `=XOR(A6:E6)` |

2 | `=XOR(A7:E7)` |

3 | `=XOR(A8:E8)` |

4 | `=XOR(A9:E9)` |

5 | `=XOR(A10:E10)` |

… | … |

32 | `=XOR(A37:E37)` |

### Effects of using XOR with more than 2 conditions formula example

The following image illustrates the results returns by the XOR formula when working with more than 2 conditions. As expected, the formulas (in cells F6 to F37) return the following logical values:

- TRUE: If an odd number of arguments (columns A through E) is TRUE.
- False: If an even number of arguments (columns A through E) is FALSE.

The image below contains a helper column (H) which displays the number of arguments that are TRUE in each row.

## #3: XOR alternative to return TRUE when only 1 condition is TRUE

### XOR alternative to return TRUE when only 1 condition is TRUE formula

Excel's XOR function returns TRUE if an odd number of conditional tests returns TRUE. In some contexts, however, XOR is defined as a function that returns TRUE if only 1 of its arguments returns TRUE.

To **create an alternative to the XOR function that returns TRUE when only 1 condition is TRUE (regardless of the number of conditions you test)**, use a formula with the following structure:

`=COUNTIF(ConditionListRange,TRUE)=1` |

### XOR alternative to return TRUE when only 1 condition is TRUE process

To **create an alternative to the XOR function that returns TRUE when only 1 condition is TRUE (regardless of the number of conditions you test)**, follow these steps:

- Refer to the cell range containing the conditions you want to consider (ConditionListRange).
- Count the number of cells that return TRUE (COUNTIF(ConditionListRange,TRUE)).
- Test whether the number of conditions that return TRUE equals 1 (COUNTIF(…)=1)

### XOR alternative to return TRUE when only 1 condition is TRUE formula explanation

#### Item: COUNTIF

The COUNTIF function counts the number of cells in a range that meet a single criterion.

#### Item: ConditionListRange

The Range argument of the COUNTIF function (ConditionListRange) is the cell range containing the conditions you want to test.

When you create an alternative to the XOR function that returns TRUE when only 1 condition is TRUE, the cells in ConditionListRange should contain:

- Logical values (either TRUE or FALSE); or
- Expressions that evaluate to logical values.

#### Item: TRUE

The Criteria argument of the COUNTIF function (TRUE) is the condition that defines the cells in ConditionListRange that COUNTIF counts.

When you set the Criteria argument of the COUNTIF function to TRUE, COUNTIF returns the number of cells in ConditionListRange that return TRUE.

#### Item: =1

The equal to operator (=) does the following:

- Compares 2 values; and
- Returns TRUE or FALSE as follows:
- TRUE: If the compared values are equal.
- FALSE: If the compared values aren't equal.

When you create an alternative to the XOR function that returns TRUE when only 1 condition is TRUE, = compares the following 2 values:

- The number of cells in ConditionListRange that return TRUE, as returned by the COUNTIF function (COUNTIF(ConditionListRange,TRUE)); and
- The number 1.

Therefore, = returns TRUE or FALSE as follows:

- TRUE: If only 1 cell in ConditionListRange returns TRUE.
- FALSE: Otherwise. In other words, if either:
- No cell in ConditionListRange returns TRUE; or
- 2 or more cells in ConditionListRange return TRUE.

### XOR alternative to return TRUE when only 1 condition is TRUE formula example

The worksheet formulas below return TRUE when only 1 out of 5 conditions is TRUE as follows:

- Condition1: The logical value specified in column A (A6 to A37).
- Condition2: The logical value specified in column B (B6 to B37).
- Condition3: The logical value specified in column C (C6 to C37).
- Condition4: The logical value specified in column D (D6 to D37).
- Condition5: The logical value specified in column E (E6 to E37).

No. | XOR alternative formula |

1 | `=COUNTIF(A6:E6,TRUE)=1` |

2 | `=COUNTIF(A7:E7,TRUE)=1` |

3 | `=COUNTIF(A8:E8,TRUE)=1` |

4 | `=COUNTIF(A9:E9,TRUE)=1` |

5 | `=COUNTIF(A10:E10,TRUE)=1` |

… | … |

32 | `=COUNTIF(A37:E37,TRUE)=1` |

### Effects of using XOR alternative to return TRUE when only 1 condition is TRUE formula example

The following image illustrates the results returns by the alternative to the XOR formula that returns TRUE when only 1 condition is TRUE. As expected, the formulas (in cells I6 to I37) return the following logical values:

- TRUE: If only 1 argument (columns A through E) is TRUE.
- FALSE: Otherwise. In other words, if either:
- No argument (columns A through E) is TRUE; or
- 2 or more arguments (columns A through E) are TRUE.

The image below contains a helper column (H) which displays the number of arguments that are TRUE in each row.

## Learn more about working with the XOR function in Excel

### Workbook example used in this Excel XOR Tutorial

You can **get immediate free access to the example workbook that accompany this Excel XOR Tutorial** by subscribing to the Power Spreadsheets Newsletter.

### References to constructs used in this Excel XOR Tutorial

Use the following links to visit the appropriate webpage in the Excel help center:

- Work with the XOR function.
- Count the number of cells in a cell range that return TRUE with the COUNTIF function.
- Test whether the number of cells (that are TRUE) returned by the COUNTIF function is equal to 1 with the equal to (=) operator.

### Books about Excel and the Excel XOR function

Use the following links to visit the book's webpage in Amazon. PowerSpreadsheets.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to Amazon.

- Excel 2016 All-in-One For Dummies, by Greg Harvey.
- Microsoft Excel 2016 Step by Step, by Curtis Frye.
- Excel 2016 Bible, by John Walkenbach.
- Excel 2016 in Depth, by Bill Jelen.
- Excel Formulas and Functions for Dummies, by Ken Bluttman.
- Excel 2016 Formulas, by Michael Alexander and Dick Kusleika.
- Excel 2016 Formulas and Functions, by Paul McFedries.