🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

GoSkills
Help Sign up Share
Back to course

Data Validation Lists

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

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.

Exercise files

Download this lesson’s related exercise files.

Data Validation Lists.xlsx
17.8 KB
Data Validation Lists - Completed.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 tab --> 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 --> Define Name --> +
    • Enter the name and select the range
    • Use this name in your Data Validation list Source field
Login to download
  • 00:04 In this video, we're going to look at a specific type of data validation,
  • 00:08 which is called data validation lists.
  • 00:10 And the idea here is that I'd like to be able to fill in this form so
  • 00:13 that I can actually pick valid products off of my list, and only valid products.
  • 00:19 Now we can do this by going to Data Validation, Data Validation, and
  • 00:24 we can choose to create a list.
  • 00:26 Now the first way that we could do this is we could actually type these values in.
  • 00:31 So, I could say, Bread, Coffee Cake, for example,
  • 00:36 and you'll notice that I'm gonna ignore blank, so that's important if we don't
  • 00:40 mind if the cell is blank at all, and I'm gonna provide an in-cell drop down.
  • 00:44 When I say OK, you'll notice that when I select this cell, I get a little drop down
  • 00:48 box, and it allows me to choose from the two valid items.
  • 00:51 And there's a formula in the cell that's automatically populating.
  • 00:54 Now that's cool and everything else, but
  • 00:56 I don't really want to have to hand type this list.
  • 00:59 And the other thing is, is that it's not gonna allow me to put in bread right now
  • 01:03 because that's not a valid entry either.
  • 01:06 So you need to remember when you're filling this out
  • 01:09 that these are case sensitive when you're working with data validation,
  • 01:13 and that's gonna be a little bit of a challenge.
  • 01:14 So if I'd like to actually have bread, or bread accepted,
  • 01:17 I would have to do something like this, which is a little bit frustrating.
  • 01:21 The other alternative that I'd like to do though,
  • 01:24 is I'd actually like to provide this with the list directly from this cell.
  • 01:28 Now, I could go back and say, let's go into Data Validation and
  • 01:33 instead of using these values, I'll just go and
  • 01:37 select from this set of cells, and say OK, and OK.
  • 01:41 And you'll notice that, indeed, we get all of our items, which is great,
  • 01:45 cuz now we just get valid items.
  • 01:47 That's very cool, but there's something about this that I'm not really a big fan
  • 01:51 of, and that is that we've actually used a hard coded absolute reference.
  • 01:56 This is a table, and wouldn't it be nice if we could refer to that column of
  • 02:00 the table instead, because remember tables automatically expand as we add more data.
  • 02:05 This doesn't, and I don't really wanna have to come back and
  • 02:07 manipulate my data validation all the time.
  • 02:10 So I know when I look at tables, that I could equals or say equals,
  • 02:16 select this and it'll actually create this formula that goes with products,
  • 02:19 the name of the table, products, the name of the column.
  • 02:23 Well I'm going to go and grab this right now.
  • 02:25 So say CMD + C.
  • 02:27 Copy that one.
  • 02:27 I'm gonna go back to my data validation list, and I'm gonna type in that formula.
  • 02:34 Here we go, products, products.
  • 02:38 And it tells me that it's not a valid formula.
  • 02:40 We'll that's a little bit frustrating.
  • 02:42 So let me show you how you work around this and this can be really,
  • 02:44 really handy for working with tables.
  • 02:46 It is kind of silly though that you have to, but if we go to formulas, and
  • 02:51 go to define name, you will notice that there are a few names in this workbook.
  • 02:54 One of them is products table, the other one if for the practice and solution, but
  • 02:59 if I were to click plus and I was gonna say, let's create one called
  • 03:04 DVAL list, for data validation list, underscore products.
  • 03:11 Select the range of cells, I'm going to say CMD + V,
  • 03:13 we'll put in products products and we'll say OK.
  • 03:17 Before I do that, I am just going to go and copy this.
  • 03:22 If I now go back to the data validation, I can use the named range.
  • 03:31 I can't use the name of the table, which is frustrating as I say, but
  • 03:34 I can use the named range.
  • 03:37 That named range evaluates to the table and
  • 03:40 allows me to actually work with this, so that's good.
  • 03:42 Now, next question that you might want to know here is,
  • 03:45 how do I copy my data validation settings across from one place to another?
  • 03:50 So what I am going to do here is I've got a couple options,
  • 03:54 I can grab the entire range and go to data validation and
  • 03:57 it says, hey, you've got some cells here with out data validation,
  • 04:00 would you like to extend the data validation to all of these cells?
  • 04:03 I can say yes, that's one way that I could do it.
  • 04:06 The other way is I could actually copy the cell,
  • 04:10 grab the rest of the cells, right click, say paste special,
  • 04:15 and I could actually paste the validation settings.
  • 04:20 That would also work, and you'll notice now as I select these individual cells,
  • 04:26 that I can click them from the drop down list as well.
  • 04:30 One last thing that you want to know about data validation is how to get rid of it if
  • 04:34 you don't really want to have it anymore.
  • 04:36 The way to do that is to select the cell or cells that you don't want your data
  • 04:41 validation on, go back into data validation,
  • 04:43 and the option there is to clear all, and that will clear the data
  • 04:47 validation from the cell that you're actually selecting at that time so.
  • 04:51 So cool little trick.
  • 04:52 Great way to actually make use of things no matter where you're table is sitting.
  • 04:55 If it's on a different worksheet or not, after you name a range to it you'll be
  • 04:58 able to pull it in and I highly recommend that if you're trying to send something
  • 05:02 out that you want people to fill in from defined lists.

Lesson notes are only available for subscribers.

Data Validation
05m:44s
Styles
05m:00s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement