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

Posted on May 4th, 2010 in Learn Excel - 8 comments

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:

Either Or Data Validation for Input Values in Excel - How to do it?

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:

Either Or Data Validation - Demo in Excel

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)
    Data Validation Settings for Either Or Input condition in Excel
  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

Written by Chandoo
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

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

  1. Jon Peltier says:

    You should also use some kind of validation downstream that inserts some kind of error instead of doing the calculations.

    =if(counta($I16,$K16)=1,,)

  2. Gerald Higgins says:

    You can also expand it, like this
    =COUNTA($A1:$D1)<3

  3. Pankaj Verma says:

    That was cool, Chandoo. Good stuff.

  4. Stevo says:

    On you, Chandoo. Hay, Chandoo- can-do! Never say never!

  5. Daniel Ferry says:

    Hi, Chandoo.

    In C4's validation I would just enter:

    =LEN(e4)=0

    and vice versa.

    If you wanted one formula to do the entry simultaneously for both cell's validation:

    =LEN(c4&e4)=0

    I like to keep it short and focused. And LEN() is about the fastest function there is...

    Regards,

    Daniel Ferry
    excelhero.com

  6. Ross Parker says:

    Boy am I glad you've abbreviated 'please' to 'pls.' Just think of the keystrokes saved!

  7. [...] example, Chandoo describes how to make ‘either-or’ type validation, meaning you can enter any one of two inputs, but not [...]

Leave a Reply