Data Validation Lists

Quick reference

• 00:04 One of my favorite ways to provide data validation is to provide users with
• 00:08 a list of valid items that they can choose from.
• 00:12 And I'm going to demo that to start with right here in this little area, where
• 00:16 we going to let the user choose whether or not the order has been filled, yes or no.
• 00:20 So I'm going to start by going to Data.
• 00:23 We're going to go to Data Validation, and I'm going to choose to provide a list.
• 00:28 There's a couple of options here, Ignore blank,
• 00:31 it's intended to make sure that when you actually go and delete a value in a cell,
• 00:35 will it actually allow you to do that or will it not?
• 00:38 Does it actually have to force a value in place here?
• 00:40 The In-cell dropdown will allow you to have a little dropdown list that shows up
• 00:44 beside the cell indicating there's a list to choose from.
• 00:47 I'm going to type in my own list here, it's going to be yes.
• 00:50 We separate the elements by putting in a comma, so
• 00:53 we've got a comma separated list of yes or no.
• 00:56 At this point, I'm going to say OK.
• 00:58 And what you'll see is that I get a nice little dropdown arrows
• 01:02 of that shows up here, which gives me my list.
• 01:04 Now, as far as typing here, as of office 365, so
• 01:09 Excel 2021, and earlier, not included.
• 01:13 If you start typing yes,
• 01:14 it will automatically start filtering the list down.
• 01:17 Even though I've typed a lowercase y, I can actually go and hit Tab and
• 01:22 it will commit it to whatever the item was from that list, this is new.
• 01:27 In previous versions, Excel 2021 and earlier, you would actually have to type
• 01:31 exactly what was showing in here and these were case sensitive.
• 01:35 So now, it's not quite as case sensitive as it was, which is actually really nice.
• 01:39 But in the past, what you'd see is people would actually put in an uppercase yes,
• 01:43 lowercase yes, uppercase no, lowercase No.
• 01:46 In order to be able to cater to all these different options, but
• 01:48 then the challenge was they showed up in the list.
• 01:50 So this is much better,
• 01:52 we see an improvement to data validation in office 365.
• 01:56 That is allowing us to go and provide a command separated list.
• 02:00 What if I want to provide a list that shows up for the items in a range or
• 02:05 a table here?
• 02:06 So I can do the same thing.
• 02:08 What I can do is I can come over here and I can say data validation.
• 02:12 I'm going to choose to use a list again.
• 02:15 And the source, I'm going to click my little arrow and go and
• 02:18 pull it out of my table column.
• 02:19 And you'll notice that when I go back here, it starts with equals.
• 02:24 If it starts with equals, it's going to pull from a range.
• 02:26 If it doesn't start with equals,
• 02:28 it's going to be hard coded values that show up in your list.
• 02:31 I always recommend that you use hard coded references for this.
• 02:34 And this way, you can copy your data validation rules around without any issue.
• 02:38 When I now go and say OK, what you'll see is that I get a dropdown list, and
• 02:42 it gives me the individual values from my table that I may want to use.
• 02:46 So this is actually pretty awesome.
• 02:48 In addition, if I were to come back in here and say, hey, you know what, we've
• 02:53 got, I don't know, pizza or something like that, that's going into this at 14.95.
• 02:58 When I go back to my data validation, you'll notice that pizza is there.
• 03:02 And this is because even though we pointed to a hard coded range that indicates
• 03:07 the table column, the table column automatically expands.
• 03:11 Now, one of the things that I like to do to make my list a little bit more readable
• 03:15 is I actually like to use a named range to refer to this particular area.
• 03:19 So what I'm going to do here is I'm going to go, and
• 03:21 I'm going to make myself a new named range.
• 03:23 I select all the items in the products column except for the header.
• 03:26 And I'm going to come over here and I go with DVal,
• 03:29 because that is my data validation list.
• 03:32 There we go, DVal list_ products, and
• 03:34 that just helps me understand what I'm working with here.
• 03:37 So I've hit Enter, that goes away, but because it's a named range,
• 03:42 I can go and pick up my DVal list products.
• 03:46 And here we go, we can pick that up nicely, there we go.
• 03:49 If I come back over into this area and go to Data Validation,
• 03:53 unfortunately, there's no prompting here.
• 03:56 So I have to remember exactly what I typed.
• 03:59 But when I go and put this in place, it makes it a little bit easier to
• 04:02 see what ranges that I'm actually working with.
• 04:05 And you'll notice once again, that we can actually pick these things up.
• 04:08 And of course, if I go and delete something from my table,
• 04:12 let's just delete that table row.
• 04:14 If I come back over here again, we can see that that is no longer in my data
• 04:18 validation list, so it's working quite nicely.
• 04:20 Just remember with this though, this is important.
• 04:23 Had I actually put, let's say, bread into my table here and
• 04:28 deleted this row, it's not going to take away the original piece here.
• 04:34 So having your if error statements, and x lookups, and v lookups written properly
• 04:38 here to realize that there's some missing data is still very,
• 04:42 very important as you're building up your different forms.

Lesson notes are only available for subscribers.