Locked lesson.

Upgrade

About this lesson

How to use data validation to create dropdown menus, and control what can be entered in specific cells.

Exercise files

The exercises below will open in a new tab. When signed in to your Google account, go to File --> Make a copy to get an editable copy of the file.

Data Validation Solution
Link Data Validation
Link

Quick reference

Data validation

Data validation is used to control what types of data can be entered into specific cells in Google Sheets, as well as create drop down menus.

When to use

When you only want to allow certain types of data to be entered into a range of cells in your sheet.

Instructions

Create a data validation rule

  • Select the cells you want to add the rule to
  • Click “Data”
  • Click “Data Validation”
  • Options:
    • Cell range: Change which cells the rule will apply to
    • Criteria: Set the rule which determines what data is allowed
      • List from a range: Select a range of cells somewhere else on the sheet; data much match one of these cells to be valid
      • List of items: Enter a list of valid entries separated by commas
      • Number: Allow a number matching specific rules
      • Text: Allow text matching specific rules
      • Date: Allow a date matching specific rules
      • Custom formula is: Create a custom formula
      • Show dropdown list in cell: Displays the acceptable data as a dropdown list; only works with “List from a range” and “List of items”
    • On invalid data: Set what happens when invalid data is entered in the cell
      • Show warning: Show a warning that the entry is invalid
      • Reject input: Show a warning window and do not allow the entry
    • Appearance: add help text to clarify what data is allowed
  • Click “Save”

Modify or remove a data validation rule

  • Select the cells you want to modify or remove the rule from
  • Click “Data”
  • Click “Data Validation”
  • Make changes or click “Remove Validation”
Login to download
  • 00:04 Let's take a look at how we can apply some data validation.
  • 00:09 In many cases, you may want to ensure that certain cells or
  • 00:13 certain columns only receive a particular type of data.
  • 00:18 In this example, I want to ensure that any subsequent entries within the Units
  • 00:23 column will only be a number.
  • 00:26 And I don't want to accidentally add some text within this column.
  • 00:31 So to do so,
  • 00:32 I'm gonna first select the cells where I would like this data validation to apply.
  • 00:37 I’m gonna go up to the menu and select Data, and at the very bottom,
  • 00:41 select Data validation.
  • 00:44 Now, here within this menu, we have already selected our cells.
  • 00:47 If we want to change those cells, we can do so here.
  • 00:51 Next, we get to select the Criteria.
  • 00:54 In this case,
  • 00:55 I want to make sure that only numbers are applied to these particular cells.
  • 01:00 We can choose if we want to select a range.
  • 01:03 In this case, I don't really want a maximum number, so
  • 01:07 I'm gonna select greater than.
  • 01:09 And I wanna make sure that it's a positive number, so
  • 01:12 I'm gonna say greater than zero.
  • 01:14 Next, we can choose if we want to show the user a warning if they
  • 01:19 input something other than a number.
  • 01:21 Or if we just want to reject their input outright,
  • 01:25 if they input something other than a number.
  • 01:28 In this case, I'd like to reject their input.
  • 01:31 I don't even want it to remain for a few seconds, so I'm gonna choose this option.
  • 01:37 Lastly, we can also choose if they would like to receive a warning.
  • 01:41 And I think that might be helpful in this case.
  • 01:43 So I'm gonna check this checkbox, and by default,
  • 01:46 it's going to give me the criteria.
  • 01:48 So in this case, it's gonna show the user, Enter a number greater than 0.
  • 01:53 Perfect. I think that explains that really well.
  • 01:56 Lastly, I'm gonna select Save, and
  • 01:59 now these cells have that data validation applied.
  • 02:03 So if I go to this first cell and I'm going to type in the word ten,
  • 02:08 and I'm going to hit enter.
  • 02:10 Immediately, I'm going to get this warning.
  • 02:12 There was a problem.
  • 02:14 Enter a number greater than 0.
  • 02:16 Okay, I'm gonna go back, and this time, I'm gonna enter the numerical value of 10.
  • 02:22 Perfect.
  • 02:23 Next, let's see how we can apply a dropdown to a different set of cells.
  • 02:29 Here, we're gonna be looking at the Item column.
  • 02:32 And I don't want someone to accidentally misspell Binder, or
  • 02:36 maybe input Pencil without a capital P.
  • 02:40 I want every item in here to look similar.
  • 02:44 So in this case, I want to apply a dropdown menu for the options here below.
  • 02:49 In order to create a dropdown option,
  • 02:52 you will first need a set of your options somewhere else on the spreadsheet.
  • 02:58 And here you can see, I have my five items listed here.
  • 03:01 This is gonna be important as we set up data validation for these cells.
  • 03:06 Once again, I'm gonna select the cells where I want this to be applied.
  • 03:09 I'm gonna go back to Data and select Data validation.
  • 03:13 Now in this case, I do want this first option, List from a range.
  • 03:19 I'm gonna select List from a range, and then I need to select that range.
  • 03:24 So by selecting this little icon,
  • 03:27 I can now go back to my list of items and select the five here.
  • 03:32 These are the five items I want the user to be able to choose from.
  • 03:37 I select OK, and we're returned to the pop-up menu.
  • 03:41 I'm gonna check the Show a dropdown list in cell.
  • 03:44 This is gonna put a little down arrow so
  • 03:46 the person knows that they can select from a dropdown list.
  • 03:49 And again, we have the similar options here.
  • 03:52 Do we want to show a warning if they input something else?
  • 03:55 Or reject the input?
  • 03:57 Once again, I'm gonna reject the input, and in this case,
  • 04:00 I'm not going to choose to show any help text.
  • 04:04 Lastly, I'm gonna hit Save.
  • 04:06 And now you see,
  • 04:07 we have a little dropdown arrow on the right side of all of these cells.
  • 04:12 So if I go to input a new entry here, all I need to do is select this arrow.
  • 04:18 And now, I have my five options, the same five that we have listed here.
  • 04:23 I can select Pen Set, I can go to the next one, maybe it's another Pen Set.
  • 04:27 I can make all the choices that I have listed here.
  • 04:30 So creating a dropdown list can be a very powerful way for
  • 04:35 you to add data validation as a part of your spreadsheet.

Lesson notes are only available for subscribers.

Gift this course