Go Back

One or the other not both


If you want to SELECT ONE OR THE OTHER BUT NOT BOTH of two items, the XOR function can be used. In the example below, Cell D7 is worked out with the basic formula below.


The XOR function operates by returning as TRUE when either FOOTBALL or GOLF contains an “X.” Only one criterion is permitted to be fulfilled.

The function returns as FALSE when both FOOTBALL and GOLF contains an “X” and when FOOTBALL and GOLF contains a different alphabet as shown in the example below.


Step 1

  • Input your data

Figure 1- How to Select One or the Other NOT Both

Step 2

  • Click on Cell D7
  • Type this. =XOR(B7="x",C7="x")
  • What you have should be similar to figure 2

Figure 2- How to Select One or the Other Not  Both

Step 3

  • Click on the ENTER button
  • Your result should be as shown in figure 3 below

Figure 3- How to Select One or the Other Not Both

To get the result for the other cells, simply change B7 and C7 as shown on the formula to the Cells under Football and Golf respectively.

You can also use the drop-down feature by clicking on CELL D7 and drag down.

Figure 4- How to Select One or the Other Not Both


Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I have two excel sheets open - In one of them I pasted images from one tab to another, now both excel sheets will not save or close - keeps saying 'reference not valid' or just re-selects the images
Solved by G. Q. in 19 mins
Hi, I needed an excel formula to compare two columns, with either both not blank or one cell is blank in each case? Please any help?
Solved by X. L. in 26 mins
I am filtering two time columns in Excel for Mac 2011. One column filter allows (in the "Choose One" list) "Greater than" / "Greater than or equal to" etc whereas the other column filter (in the "Choose One" list) lists only "Begins with" / "Does not begin with" etc Both columns are formatted for Time in hh:mm format Why the discrepancy and how to solve it I want both to have "Greater than" / "Greater than or equal to" etc
Solved by O. Q. in 30 mins

Leave a Comment