fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn

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

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

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