Locked lesson.
About this lesson
Drop down lists avoid the risk of "Garbage Out" by preventing your users from putting "Garbage In"
Exercise files
Download this lesson’s related exercise files.
Data Validation.xlsx50.2 KB Data Validation - Completed.xlsx
50.5 KB
Quick reference
Data Validation
Working with Data Validation lists.
When to use
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 list
- To use a hard coded list, enter the values separated by commas. Eg: Dog, Cat, Hamster
- To use a list contained in a range, enter the address of the list. Eg: =$B5:$B25
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
- An 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
Hints & tips
- When creating a list against a range, use absolute cell references so that you can copy your data validation rules without issue
- Customize the Error Alert tab to provide useful feedback if someone enters a restricted value
- List items are case sensitive. If you want to allow Dog or dog, those items both need to be contained in the list
- 00:04 So we've got a cool chart that allows us to just change one cell and
- 00:08 restate the entire thing.
- 00:11 The problem we have here though, is that when we try and deploy this to our users,
- 00:14 they now have to try and
- 00:15 remember which category is it that they're actually trying to work with?
- 00:19 So, where they might be looking for sleeping bags, they'll go and
- 00:22 they'll type in three hoping to get close and realize shoot it's the wrong one.
- 00:26 It must have been two.
- 00:27 All right, there we go.
- 00:28 Now I've got the right one.
- 00:29 So that's not really the best user interface experience.
- 00:32 Wouldn't it be better to provide them with some kind of other piece like a spin box
- 00:37 or a drop down list that would allow them to choose the right value the first time?
- 00:40 And as it turns out we can do both.
- 00:43 In this module, we're going to focus on the drop down list.
- 00:47 Before we get started, we need to do one kinda weird little thing here though.
- 00:51 Remember my table?
- 00:52 If we go and click from the drop down list,
- 00:54 we can select product lines, well the first thing I'm going to
- 00:57 do before I go to set up my data validation list is I'm going to do that.
- 01:01 And then I'm going to go and create a name by selecting product lines here and
- 01:05 typing in D.V. products.
- 01:07 D.V. for data validation.
- 01:10 Interestingly enough, it shows me again that I have product lines, but
- 01:13 if I go back and choose D.V. products, I go to the same place.
- 01:17 Why would I do that?
- 01:18 Well here let me show you.
- 01:19 Under the formulas tab, under name manager, you'll see product lines refers
- 01:24 to this source date area, D.V. products the first two product lines product lines.
- 01:30 That seems silly.
- 01:31 It seems like a duplication.
- 01:32 But unfortunately, it's something that's necessary to work with the data properly
- 01:36 in the data validation area.
- 01:39 Now, we're going to select one of our sleeping bags.
- 01:42 And you'll notice we have a formula here.
- 01:44 We're going to forgo this.
- 01:45 We're going to go to the data tab and I'm going to find Data Validation.
- 01:51 Now mine is a small shrunk down button, because my ribbon is a bit compressed.
- 01:54 Yours will probably be full size.
- 01:56 Regardless, when you click on it you'll be taken into this particular window.
- 02:01 Now inside the Data Validation area you'll notice that the defaults is to allow
- 02:05 any value for a cell.
- 02:07 And this makes sense, because when you create a brand new worksheet you don't
- 02:10 want to have any restrictions on what can go into those cells.
- 02:13 But we have the ability to toggle that.
- 02:15 So now when I click on this, you can see that I can set and force someone to enter
- 02:19 a whole number or a decimal number, dates, times, things like that.
- 02:23 What I want to focus on is a list.
- 02:27 Now, you can set up a simple list really easily by making a comma separated list.
- 02:31 So, I could type in each of my individual items, separated by commas, and
- 02:35 that would work, but that just seems kind of mundane.
- 02:38 I don't really want to do that.
- 02:39 Instead, what I'd like to do is select my area of data.
- 02:44 And this is where I'm saying that we have this bit of an issue.
- 02:47 This pulls back this hard coated range.
- 02:49 It doesn't pull back the product lines reference.
- 02:52 And this is why I set up my nice little list for DV products, so
- 02:57 that I have a list that points back to the tables name.
- 03:00 And that way it will always expand.
- 03:02 I have options I can set.
- 03:04 Ignore blank, and install drop down.
- 03:06 I'll leave both of those as default for right now.
- 03:08 And now we'll say okay, and what you'll see is that we get a drop down list.
- 03:13 When I go and click on this little arrow,
- 03:14 you'll notice that it gives me a nice list of all of my items.
- 03:17 And I can now pick Tarps and Groundsheets, or I can even,
- 03:21 if I want to, type and hit enter, and it will still allow the correct input.
- 03:27 On the other hand if I were to go and try and type in something else, like cats,
- 03:31 it's going to come back and give me an error that says you can't do this.
- 03:35 You can retry as many times as you want, but I've got to tell you the cats is not
- 03:38 changing and therefore it's not going to work.
- 03:41 The only option that really works is cancel,
- 03:42 because help actually takes you to a page that doesn't offer any help whatsoever.
- 03:46 So we'll hit cancel here.
- 03:48 Can we do better than that message though?
- 03:50 Absolutely, let's go back into our Data Validation, we'll
- 03:54 select our Data Validation, and you'll notice that we actually have three tabs.
- 03:59 We have an Input Message, and this can show up every time you select the cell
- 04:03 with a little yellow box to tell people what to do.
- 04:05 I find in my experience that users get really tired of this really fast, so
- 04:10 it's better to just give them a lesson in person, and
- 04:12 then you don't have to worry about this particular piece.
- 04:15 But the part that I do like to send is these guys here.
- 04:18 We have error alerts.
- 04:20 Error alerts can be configured to be a stop style, a warning, or
- 04:25 an information style.
- 04:27 The difference is that information will pop up and
- 04:29 tell you what's going on and allow you to clear it.
- 04:32 Warning will pop up a message and say are you sure you want to proceed.
- 04:35 So you get the option to yes, no or cancel.
- 04:38 Stop completely prevents a user from putting in the wrong thing.
- 04:43 So in this case here, I would say let's put in something that says invalid data,
- 04:47 and we give a nice little error message that says,
- 04:49 please choose a value from the drop down list provided.
- 04:55 Something to give them a little bit of help, and now when we say okay,
- 04:59 what we'll find is when they go and select from the list, sleeping bags, it works.
- 05:04 If they type in something like tents, but if they come in and try and
- 05:09 put something else in there, like Cats, they'll get my custom error message
- 05:13 that tells them to please choose a value from the drop down list provided.
- 05:17 So this is a really useful function for driving your dashboards, because people
- 05:21 can type and they can select from a drop down list without getting it wrong.
- 05:25 Meaning that you don't have to worry about data that's being incorrect
- 05:28 entered into your spreadsheet.
Lesson notes are only available for subscribers.