Locked lesson.

Upgrade

About this lesson

Use to restrict users ability to enter invalid data in cells by providing them with a drop down list of valid options.

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2013, 2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Data Validation Lists.xlsx
17.9 KB Data Validation Lists - Completed.xlsx
18.1 KB

Quick reference

Topic

Working with Data Validation lists

Where/when to use the technique

Use to restrict users ability to enter invalid data in cells by providing them with a drop down list of valid options.

Instructions

Basic setup

  • Select the cell you’d like to be validated
  • Go To Data --> Data Tools --> Data Validation
  • Choose to allow a List
  • Make sure “Ignore Blank” is checked if you’d like to allow users to clear the input cell

Providing a comma delimited list

  • Provide a list of valid options separated by commas in the Source field
    • Source:  Dog,Cat,Hamster
  • Remember these options are case sensitive.  If you’d like people to be able to enter Dog or dog, you’ll need to provide both those options
    • Source:  Dog,Cat,Hamster,dog,cat,hamster,DOG,CAT,HAMSTER

Providing a list from a range

  • Instead of using a comma separated list, you can also provide the range where a list exists
  • I.e. if you have a list of valid options in B5:B25, you can provide this address instead
    • Source:  =$B5:$B25
  • We recommend using absolute cell references so that you can copy your data validation rules without issue

More robust list sources using named ranges

  • It is a good practice to name your input lists instead of using cell referencing.  This allows
    • Use of table elements in data validation
    • And easy way to jump to the validation list (via the Name box)
  • To do this:
    • Select the range of valid items you want to show in your validation list
    • Go to Formulas --> Name Manager --> New
    • Enter the name and select the range
    • Use this name in your Data Validation list Source field
Login to download
  • 00:04 Data validation rules can be very useful for
  • 00:06 preventing people from putting in the wrong things.
  • 00:09 But they can also be useful when we actually wanna help people select
  • 00:13 the right things.
  • 00:15 In this case here we'd like to select from a list of valid products to go into our
  • 00:20 user form.
  • 00:21 So, what we're gonna do is, we're gonna go to data validation,
  • 00:23 and we're gonna check our drop down list here.
  • 00:26 And you'll notice we can set up for whole numbers and decimals and
  • 00:29 dates as you've seen before, but one of the other options we have is a list.
  • 00:34 Now, we have two ways of providing our list.
  • 00:37 We could actually set up our list as a commented limited list.
  • 00:40 So, say, coffee cake, and scones and butter tarts.
  • 00:47 But the problem is, is that if this list is really long, while it will work, let's
  • 00:53 just check and make sure that it does, we get a little drop down arrow here.
  • 00:56 If we choose coffee cake, it picks it off the list nicely.
  • 01:00 But the challenge is while that will work, it's a lot of work to set this up,
  • 01:05 particularly if we already have those values sitting in some cells.
  • 01:09 And for that reason we can actually go and say, don't give me the list from here.
  • 01:14 What I would rather have, is the list from my table.
  • 01:19 You'll notice that when we select it,
  • 01:21 it gives us an absolute reference to the cells.
  • 01:23 And we can say, okay.
  • 01:26 And at this point we now have a nice list that pulls back all of the items that
  • 01:30 are in the list from our table, and we can drop them off as we need.
  • 01:35 A couple of things that you wanna be aware of here though.
  • 01:38 Number one, right now, the data validation settings are set to ignore blank.
  • 01:42 This is important if you want something to be able to delete
  • 01:45 from these particular areas here and have a blank value in that cell.
  • 01:48 If you prevent that, then it's not gonna do that.
  • 01:51 It'll actually come back and provide an error.
  • 01:54 The little drop down arrow we get on the right hand side,
  • 01:57 comes by checking this box that says in cell drop down.
  • 02:00 When we check that box, we get the little drop down list.
  • 02:02 If we don't, then there's no list at all.
  • 02:05 It just actually validates against what's the table that's being used, but
  • 02:08 it doesn't give the users any advice.
  • 02:11 There are some challenges with this particular list feature.
  • 02:15 One of them is that, as you're actually going and
  • 02:18 typing in coffee cake for example, C-O-F.
  • 02:22 The auto-complete here is coming because it shows up above.
  • 02:26 So let's take a look at one of the other items, bread.
  • 02:28 We don't have bread in our list.
  • 02:30 If I start typing B-R, there's nothing here and
  • 02:34 I can't select it to get off this list now.
  • 02:36 So this is a little bit of a challenge.
  • 02:38 It would be nice if this supported hot typing, but unfortunately it doesn't.
  • 02:42 In addition, because we set up our list to point to an absolute range of cells here.
  • 02:47 We can't necessarily guarantee when we add more products for
  • 02:50 our table that they're gonna get pulled into our data validation list.
  • 02:54 For that reason,
  • 02:55 we actually recommend that you use named ranges when you're providing your list.
  • 03:00 And the way that we do that is we actually check the formula we need.
  • 03:03 To do it we say, equals in a cell, and we'll select the column of our table.
  • 03:07 And you'll notice that gives us the format that we need, equals products, products.
  • 03:12 So I'm gonna copy this.
  • 03:14 And press escape.
  • 03:16 And then I'm gonna go to the Formulas tab to the Name Manager.
  • 03:20 And I'm gonna create myself a new name range.
  • 03:23 You'll notice there's some in here already.
  • 03:24 And that's because the example for the practice and
  • 03:27 solution sets already had them.
  • 03:28 But we're gonna set one up called, DVallist_Products.
  • 03:34 So DVal list is the data validation list is the example that I used.
  • 03:38 And then I'm gonna paste in what I've originally copied here.
  • 03:42 Just gonna use control-V to paste it and that will now refer to products, products.
  • 03:47 Unfortunately the data validation feature can't be used to talk directly to a table
  • 03:52 structure at this point.
  • 03:54 But, by using a name range, you can.
  • 03:56 So, we'll say okay, and close.
  • 03:59 And now when we go back and we modify our data validation.
  • 04:06 We can change our formula to be DVallist_Products.
  • 04:14 And you'll notice that we still get all of the individual products here, and
  • 04:19 if we were to go and add a new value, like donuts.
  • 04:25 You'll notice that it shows up as well.
  • 04:28 So that's how we can use lists with data validation.

Lesson notes are only available for subscribers.

Gift this course