In this post we’re going to look at Data Validation, what is it?, what’s it used for? and how do you set it up? I think the very name puts people off and it sounds like something complicated when really it’s not.
The main use is check that data entered into cells meets a criterion you’ve set. For example, to check it’s a date, or to check the number is within a certain range. It’s also useful, as it allows you to create drop-down menus in your cells, where the user can select possible options rather than typing them in every time. This makes it quicker for the user to enter information, whilst controlling the type of information they are entering.
Setting up data validation to check for a valid date
Here, we have a simple table where I want to record the hours worked by teachers on different dates. I want to make sure a date is entered in the Date column.
Select the cells you to add the data validation to.
Right click on at the bottom of the menu, select “Data validation…”.
This opens the Data validation dialogue box. In summary, this allows us to set the criteria we want, set what we want to happen if the user doesn’t enter data that meets the criteria, and we can set up some help text to tell the user what to do if they enter something wrong.
By default, the criteria is set to “list from a range”, click on this and select “Date”.
This will change the criteria options. This is the option we want, i.e. we want to check that the date entered is a valid date.
But first, let’s just see what other options there are. Click on “is a valid date” and you’ll see that we can check to see if the date is before, after and between specific dates.
Next, we can set what happens if the user inputs something that isn’t a date. There are two choices, either we can show a warning in the cell, or we can make it impossible for them to enter something other than a date, by rejecting the input and displaying a message. For now, let’s leave it on “Show warning”.
If they do enter something wrong, we can set up a help message, which will tell them what they need to input.
Tick the “Show validation help text” and you’ll see a default message related to the criteria below. You can edit it if you want.
Then click the blue “Save” button.
Back on the sheet, we can see that we can enter a date with no problems, but when we enter something like the number 2, we get a red triangle in the cell, warning us it doesn’t meet the criteria.
Hovering over the cell, we can see the help message.
Checking data meets a numbers criteria and rejecting invalid entries
This time let’s make sure the hours inputted in the Hours column are between 0 and 24. Select the range, right click and select Data validation from the menu as before.
This time choose “Number” from the criteria.
This changes to the Number criteria. It defaults to choosing between two numbers.
If we click on “between” we can see other possible options.
We want the between option. Enter the range we want, i.e. 0 to 24.
This time we’re going to reject any input that isn’t between 0 and 24, so select “Reject input”.
Set up the help text by ticking “Show validation help text”. Again the default is fine.
Here I’ve typed in 24 with no problems.
When I try to type in 25, nothing is entered in the cell and it displays an error message, along with the help message.
We finally end up with the table we want.
Setting up a “List of items” validation
Here, we have a different table where I want to record the name of a teacher that has been substituted due to illness and I want to record the number of hours the substitute did. I could just type the teachers’ names every time, but I want to just select their names from a pre-made list.
Select the range and open the data validation.
This time, select “List of items” for the criteria.
Now we need to enter the list we want in the box, separating each teachers’ name with a comma.
Here I’ve entered the 4 teachers I have. Then underneath, you have the option to display the list as a drop-down menu in the cell. Tick “Show drop-down list in cell”.
On the sheet we can see some little triangles have been added to our cells.
Click on one, and we can see our list is displayed allowing us to select a name.
This is a great way to allow the user to quickly enter information, whilst controlling what they enter.
Setting up a “List from a range” validation
The list of items approach is more suited for short lists, but usually I find it’s more useful to add the list on a separate sheet and refer to that. This, as we’ll see below, allows you to do things like sort the list alphabetically and to continually update the list with new entries.
Using the same example as above, let’s add the list of possible substitute teachers to the Substitute column. Select the range and go to the Data validation dialogue.
The default criteria is a list from a range, so all we need to do is tell it where our list is.
Here, I’ve written the list of teachers on a separate sheet (which often I hide), called “ref”.
Click on the grid in the in the box where it says “e.g. Sheet!A2:D5”. This will then prompt you for where the list is.
Go to the ‘ref’ page and select the list.
This updates the What data? box with the range. Click OK.
Tick the “Show drop-down list in cell” box, so we have a drop-down menu.
Back in our table, when we click on the little triangle, this opens the drop-down menu with the list of teachers, as we saw above.
NB. In practice, as the lists of teachers is the same for both columns (normal teacher & substitute), I would have normally selected both columns and set the data validation for both using the same list.
Above we used the range A1:A4, where our list of teachers was. The only problem with this is that if we add teachers to the list, we also have to remember to update the range in the data validation, otherwise the newly-added teachers won’t appear on the list.
The better way is to create an open-ended range right from the start. Here, we use A1:A. This we look at the whole column A from A1 downwards, and add anything in that column to the list. So, if we add new teachers to the list, it will automatically add them to the drop-down menus.
Drop-down menu in alphabetical order
Having the list in a column rather than entered into the Data validation box, also allows us to sort it alphabetically and then use that sorted list for the drop-down menu.
Here, we have our short list of teachers as before.
In cell B1, I’m going to add this function:
The SORT function sorts the range, in this case, alphabetically. See my post on this function for more details.
As we can see, it’s sorted the list in column A alphabetically.
We then refer to the list in column B.
As we can see, we now have a list in alphabetic order.
Creating a drop-down list which can be added to
Sometimes lists aren’t predefined ahead of time and develop over time and you want the user to be able to add to the list if what they want isn’t on the current list, and then the list gets updated automatically with what they’ve entered.
Here, we have a book inventory, where we will have a list of books and the number associated with that book.
On the ‘ref’ sheet I’ve entered the following the formula in cell D1. This uses the UNIQUE function which looks at the range and returns one instance of each different item. In other words, if we have multiple copies of the same book, it will only display the name once on the list. This is then wrapped up in the SORT function, to put it in alphabetically order (again check out my post on both SORT and UNIQUE).
Depending on your situation, but you might be better off including an open-ended range, i.e. J3:J, particularly if you’re going to be adding more books to the list later on.
Select the range and go to Data validation.
Click on the grid to enter the range for your data.
Enter the following in the box. Note, you could also use ref!D:D if you don’t have any headers in your data. Click OK.
This has added the menus but at the moment they are empty.
Type the first book in.
Now when we click on a menu, it’s already added the book as an option.
On the ‘ref’ sheet we can see it’s added it to our list in column D.
Enter a second book.
And we can see the list is being updated on the ‘ref’ sheet,
and in the drop-down menu.
As time goes on, the majority of the books will be on our list, but it will still allow us to add a new book and update the list.
On the ‘ref’ sheet, we can see the UNIQUE function only shows the single instances of each book.
Finally, another thing I like about these drop-down menus is that, apart from opening the menu and scrolling down the list to find what you want, you can type in the first letter(s) and this will filter the list, allowing you to find what you want more quickly.
Here, I’ve typed “E” and it shows me there are two options, “English File – Pre-Int” or “English file – Upper-Int”. I can then select one of them.
eBooks available on Drive, Forms, Sheets, Docs, Slides, Sheet Functions, and Apps Script:
- “Beginner’s Guide to Google Drive” – iBooks store / Kindle store
- “Beginner’s Guide to Google Forms” - iBooks store / Kindle store
- "Beginner's Guide to Google Sheets" - iBooks store / Kindle store
- "Beginner's Guide to Google Docs" - iBooks Store / Kindle store
- "Beginner's Guide to Google Slides" - iBooks Store / Kindle store
- "Google Sheet Functions - A step-by-step guide" - iBooks Store / Kindle store
- "Beginner's Guide to Google Apps Script" - iBooks Store / Kindle Store