Lesson 6 – Quick and Easy Data Validation (Drop Down Lists, Avoiding Fatal Data Errors, Lists)

Table of Contents:

• 1. Introduction
• 2.Breakdown of Box
• 3.Dropdown list
• 4.Text contains
• 5.Conclusion

Introduction

By learning data validation, you are starting to learn some of the more advanced techniques. What data validation essentially does is check to see if the data entered in cells matches the criteria you specified when building the spreadsheet. This can have many good uses, from rejecting data you don’t want, or giving people a dropdown list of data options to choose from. For example, something you could do is tell the spreadsheet to not accept numbers greater than 5. If someone enters in 6, you could have the spreadsheet reject it. In a way, this can work as a “safety” feature to your spreadsheet, so you can make sure that no bad data could ruin your spreadsheet. 

Breakdown of Box

To access data validation, you go to Data, and select “Data Validation.” This will bring up the following box: 

This is the Data Validation box, where you set all the settings. I will go over what each section means. 

Cell Range: The cell range determines the cells to which these conditions are applied to. You can have the data validation work for one cell, a column, or a random area you decide. The way you select the area to apply the criteria to is by clicking the rectangle divided in four, and highlighting the area on the spreadsheet you want to use.

Criteria: This box changes depending on what type of data validation you choose from the drop-down menu. This type displayed is number, which checks to see if a number matches the criteria you want it to (similar to conditional formatting). If we look at this example, it checks to see if a number is between 10 and 100.

On Invalid Data: This decides what you do with the data if it does not match your criteria. You have two options, show a warning, and reject data. If you choose the first, the cell will display a warning if your data is incorrect. If you choose the latter, it will reject the data completely. 

Appearance: Appearance is less important than the rest, but can still be handy. By clicking the checkbox, you can have the cell display a message if you enter data in that doesn’t match your criteria. You can choose whatever text message you want. In this example, it will tell you “Number Invalid” if it does not match the criteria.

Like with conditional formatting, you need to try to read these boxes as sentences. In this example shown, you would read it like the following. “For Cell B2 on Sheet 1, check if the number is between 10 and 100. If it is not, then reject the data and display the message “Number is not between 10 and 100”.” Here is a video of it functioning

We will now teach you some of the handy data validation types to learn. The ones we do not go over now and somewhat self explanatory, and use skills that you have already learned in the lessons prior. 

Dropdown List

Something neat you can do with Data Validation is create a drop down menu, with options to choose from. If you are confused on how this would look, watch the video below.

There are two ways to go about creating a dropdown menu, we will show you both. 

List of Items

With a list of items, you can give a list of options people can choose from. For example, look at this photo

Here, we entered a list of different foods, from Pizza to French Fries. We could enter anything we want in that list, you just have to make sure each item is separated by a comma. Now, if we read this box like a sentence, it would go like this. “For Cell C3, show a dropdown list of the Items Pizza, Hot Dogs, French Fries, and Soup. If the entered data is not in the list, reject it and show the message “Invalid Food”.”

Now, here is how it would look in a spreadsheet once we apply this:

If you need further clarification on how to create a dropdown list, please feel free to reference Google’s support article here.

List From Range

Another way you can create a list, is by making the options be whatever is entered in cells on a different part of the spreadsheet. With this, you could enter words into one part of your spreadsheet, and it will show up on your list. Let’s go over the data validation box for this type of data validation. 

What this table shows, in sentence form, is the following: “For cell C2, look at the items written in cells E3-E6, and display them in a dropdown list. If the data entered doesn’t match the criteria, reject it, and display the message “Invalid Food”.”

So with this, you can enter whatever you want in the cells you specify in your criteria range, the list will automatically update. This one is a bit more confusing than the rest, so we have included a video going over the steps, also showing how it automatically updates.

Text Contains

The rest of types of data validation are fairly self explanatory, but we wanted to leave you with one more example.

Here for cells B2 – B5, if the data entered does not contain the word yes, it will reject it, and display the message “Enter text that contains “yes”.” Let’s see what it does.

Conclusion

As always, we hope you found this lesson helpful. Data validation is a skill that is more advanced but can be truly useful in your spreadsheet making abilities, as well as good to impress someone. If you feel lost, please feel free to watch our commentated video below. In addition, we have linked an article by Google on Data Validation here, Lastly, as always, happy spreadsheeting!! 🙂