Locked lesson.
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:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Data Validation Lists.xlsx18.2 KB Data Validation Lists - Completed.xlsx
17.5 KB Data Validation Lists - Extra Practice.xlsx
18 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
- 00:04 In this video we're going to look at data validation lists as a feature to prevent
- 00:09 garbage from getting into our model.
- 00:11 And therefore, messing up our end results.
- 00:15 If we look at this particular form, we have a form for
- 00:18 a bakery that's trying to trade maintain holiday treat orders.
- 00:21 So they're taking pre-orders of different things from their customers and
- 00:26 the way it works is that we've got a table of products with prices over here.
- 00:29 We're using a VLOOKUP formula nested in the middle here to look up the appropriate
- 00:34 price for whatever product's been thrown in column A.
- 00:37 And that formula runs all the way down this table at which point
- 00:40 the value will multiply the price by the quantity and total it up for us.
- 00:45 Now, what we want at this point in time, is we want our users to come along and
- 00:49 type in the name of the products that they're looking for.
- 00:52 So coffee cake, they might wanna get one of those and everything would work great.
- 00:57 But what if they decide to come along and put in something that
- 01:02 doesn't work like sticky coffee pudding that is not even spelled correctly.
- 01:08 Right now, our formula returns not found.
- 01:10 But this isn't ideal, we would really rather force the user so
- 01:15 that they can only enter these particular items.
- 01:18 We've got a couple of ways of doing this.
- 01:21 I'm gonna select all the input cells right now.
- 01:24 And I'm gonna go to data validation.
- 01:25 This is on the data tab.
- 01:27 We're gonna go data validation.
- 01:28 And you'll see that we have the option to provide a validation criteria.
- 01:32 And one of the things we can allow is a list.
- 01:37 Now, I could type in the items that I want,
- 01:42 like coffee cake and sticky toffee pudding.
- 01:48 And I could hit Enter.
- 01:49 These are comma separated.
- 01:50 And what you'll see is that when I do this, I get, as soon as I click on one
- 01:54 of the cells in that area, I get this little drop down arrow.
- 01:58 And you'll notice that it gives me the values that I put in separated by commas.
- 02:02 So here we go,
- 02:02 sticky toffee pudding, if I choose that now it'll actually validate correctly.
- 02:06 Because I've got this typed in the same as what was over here.
- 02:10 But we obviously have a lot of different items over here, and
- 02:13 I really don't want to have to go and type in each one of these separated by a comma.
- 02:17 That's an awful lot of work.
- 02:19 If I'm trying to only do two or three different things,
- 02:22 like yes or no, things like that, then it might not be a big deal.
- 02:26 But if I'm trying to do a whole big product list,
- 02:28 it's going to be a bit of a nightmare.
- 02:29 So what I'm gonna do is I'm gonna go back and gonna select all these cells here.
- 02:33 I'm gonna go back to data validation.
- 02:36 And at this point, instead of using this comma separated list,
- 02:40 I'm gonna delete this.
- 02:42 And I'm gonna go click the little ref edit box here.
- 02:44 And on my worksheet I'm going to select all of the products that are in
- 02:48 my table there.
- 02:50 We'll say OK.
- 02:50 And it gives me the range of F4 to F8.
- 02:55 Now we can say OK.
- 02:56 And what you'll see now is that when I go and
- 02:59 grab the next line it's picking up all of those products.
- 03:03 So I can order butter tarts and I could order some cookies.
- 03:06 And all the prices are coming in so now as I go and, and use these items
- 03:12 They're all going to extend just fine.
- 03:14 So that's really kinda nice.
- 03:17 Now a trick that you're gonna want to be aware of is that if you try and
- 03:20 use this table and move it to another page
- 03:23 Your data validation may not work properly and whatnot.
- 03:27 The best thing to do is to actually refer to this area,
- 03:31 instead of using F4 to F8, is to actually give it a name.
- 03:35 And that way we can be sure that we're always targeting back to the portion of
- 03:38 the table that we need.
- 03:40 So, the name of this particular table, we can see, is Products,
- 03:44 by looking at the table tools.
- 03:45 And we can refer to this first area here,
- 03:48 and if we look at it in the formula, I can say equals and select this.
- 03:52 And you'll notice that it comes back with ProductsProducts.
- 03:55 So, this is sort of a name.
- 03:57 I'm gonna go and Ctrl+C to copy that and then just Escape from here.
- 04:02 Now what I'm gonna do is I'm gonna go to the FORMULAS tab.
- 04:05 And I'm gonna go to the Name Manager.
- 04:08 And I'm gonna say New.
- 04:10 And we will call this one, and
- 04:12 I always like to to start off with something like dval
- 04:15 And it's just a, a, a shortcut term that I use so
- 04:19 that I can look this up elsewhere and know what it is.
- 04:21 And I'll call this dval products.
- 04:25 And that's just a name that I'm gonna use somewhere.
- 04:27 And in the refers to area,
- 04:29 I'm gonna copy in this thing that I pasted, ProductsProducts.
- 04:33 And at this point I'll have a name that points to that range.
- 04:35 So we'll say OK.
- 04:38 And you'll see that we have it listed here Dval Products and close.
- 04:41 And you'll see that now from the name box I can quickly jump to it and
- 04:45 it will select that for me as well.
- 04:47 The beauty of this is that now I can go back and I can go into my data validation.
- 04:54 And I can rename this guy here to dval_products.
- 04:59 So this will always be pointed now, at this list.
- 05:02 So if I go and add something like bread, for
- 05:06 $3.95, at this point, you'll see that it now shows up.
- 05:12 And this will work no matter which worksheet this particular table
- 05:16 is actually on.
Lesson notes are only available for subscribers.