Excel Video Lesson: Data Validation

This Excel video lesson walks you through how to set up Data Validation rules.  The main purpose of Data Validation is to set up rules so that only certain types of information can be entered into cells (which helps to reduce manual entry error).

Make sure to download the most recent Master Workbook to follow along.

Let me know if you have any questions!

This entry was posted in Data Input & Manipulation and tagged , , , , , , . Bookmark the permalink.

13 Responses to Excel Video Lesson: Data Validation

  1. Anonymous says:

    hi i wanna use data validation and the situation is i have two countries names Canada and USA and another list is the name of different cities of both countries and i wanna use data validation for both that when i have name of Canada so i have only the names of Canada’s Cities in the list and same for USA. Can u help me please

  2. Anonymous says:

    Thanks Ben your tutorials are awesome.

  3. Brian says:

    Hi Ben,

    As with others I have found your tutorials informative. I have however run into an issue that should have an easy answer, but after several google hours I have come up empty.

    I am using nested IF functions in the start and end boxes of ‘between’ validation. The problem is that these formulas are lengthy and expand beyond the limits of the boxes. So when attempting to edit the equations, I need to scroll horizontally through the equation. How is this achieved?

    My first attempt intuitively was to use the scroll arrows, but this just added the selected cell to the formula and cursed through the cells as I hit the arrow. My second attempt was to left click & hold and allow the selection to scroll through then try to stop at the point needing editing… I needed to be very fast to make this work. My third attempt was to edit the equation in a blank cell then cut and paste it into the validation formula bar once it was working properly.

    I believe that I am missing something here as it should be quite easy to scroll through these formula bars.

    Thanks for your time.
    Brian

  4. Gab Etessami says:

    Hey,
    Is there a way to condition the cell to outline red when a bad input if entered?
    I know there are ways to do this off of formulas/conditioning but I’m wondering from within the context of the ‘Data Validations’ tool native in excel.

    Regards,
    Gab

  5. Rebekah says:

    Very helpful thank you!

    Could you tell me, is it possible to make a Validation Time Citeria that makes a user put time into military format and also use a colon (12:00AM – 24:00PM)?

  6. Anonymous says:

    My drop down box contents are empty on one of my pages…how do I copy the contents to another sheet?

  7. Anonymous says:

    How can I copy my data drop down boxes contents to my other sheets? Box contents are empty?

  8. Pingback: A Complete Introduction to Excel | MBAx.me

  9. Anonymous says:

    Awesome 🙂
    This site is so damn useful that I’m close to registering, despite how much I hate doing it lol.

    But I had a question/issue that came up as I was practicing this on my own that I would hope someone might be able to help clarify for me.

    When doing data validation – you can only have 1 list? It won’t let me combine ranges, so
    instead of being able to do for example:
    =(List_Fruits, List_Vegetables, List_Meats)
    I had to make one master list, List_Food for all the items.

    Heres what my range looks like for List_Food (each [ ] bracket being a cell)
    [–FRUITS–]
    [Apple]
    [Orange]
    [–VEGGIES–]
    [Tomato]
    [–MEATS–]
    [Steak]
    [Chicken]

    So now my master List_Food is a that entire range, vs each one which just included the items.
    The difference here being my List_Food range includes my header titles, like [–FRUITS–] … which I don’t want to be a “good valid data” choice. This also makes it harder to distinguish which item is which for later uses.

    The whole thing might be a little silly and unnecessary but I was just wondering if anyone had any ideas/solutions/similar problems they could share/discuss.
    I suppose I could have posted this on reddit for discussion, rather than here 😛

    Anyway, thanks again for such a great site!

    • Unfortunately after looking into it, it doesn’t appear that you can combine multiple ranges using Data Validation. The only options would be to combine into one list as you mentioned (either via copy/paste or a macro).

      Glad you’ve enjoyed the lessons!

  10. Ollie says:

    Thanks for these Ben, I am finding them really useful. Please keep them coming!

Comments are closed.