How to Get “Either-Or” Condition in Data Validation?

In a recent consulting assignment I had a tricky data validation problem. The customer wanted to have an either-or condition in the data validation, like this:

My initial reaction to this requirement was “hmm… that is not possible“. But before shooting the email back to client, I got curious and checked if excel data validation can actually do this. And of course we can do this in Excel with ease.

First see the demo of how this would work:

Now to the specifics:

1. Select both cells where you want this data validation to be applied.
2. Now go to data validation (Data Ribbon > Data Validation or Data Menu > Validation)
3. Specify validation type as “Custom” and use a formula like COUNTA() to check count of cells with a value (see the illustration)
   
4. Optional: Use Error Message settings to set a message you prefer.
5. That is all. Now your Either Or Data Validation set up is done.

Download the example file:

Click here to download the example file with this kind of data validation setup. Play with it and learn how to do this on your own.

Learn more about Data Validation in Excel:

Read more about adding a drop down list validation or advanced data validation tricks or all of them.
Related: Writing XOR (either or) formulas in Excel