How to set up data validation in Google Sheets

In this post, we’re going to look at how to set up data validation in Google Sheets as a whole and how it can be used to validate different types of data being entered and make it easier for the user to enter the data.

This expands on my post, where I looked at creating drop-down menus, and this was possible by controlling the data that was entered into the range of cells.

We’re going to look at two examples: 1) Checking for a valid date; 2) Making sure numbers entered are within a certain range.

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, click “View more cell actions” and select “Data validation”.

This opens the “Data validation rules” sidebar, where we need to set the rules we want the data in the range we’ve just selected to follow.

Creating a new validation rule

Click “Add rule”.

By default, it’ll add drop-down menus, as we saw in the previous chapter.

Now, click on the drop-down menu below Criteria. Then select “Is valid date” from the list.

Adding help text

If they do enter something wrong, we can set up a help message by ticking “Show help text for a selected cell”, 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.

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 a warning”.

Then click the “Done” button at the bottom of the screen.

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.

Other data validation

We can also set other criteria for dates. From the Criteria menu on the sidebar, click on is valid date and you will see a whole range of criteria we can set for cells to meet. As you can see below, we can set the criteria to be a certain date, or to be before or after certain dates, etc.

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.

Next click “Add rule” from the sidebar. This time choose “Is between” from the criteria list.

This will invite you to add the two values you want the numbers to be between.

Enter the range we want, i.e. 0 to 24.

Rejecting an invalid input

This time we’re going to reject any input that isn’t between 0 and 24, so select “Reject the input”.

Set up the help text by ticking “Show help text for a selected cell”. Again, the default is fine.

Below I’ve typed in 24 with no problems but when I try to type in 25, nothing is entered in the cell. Instead it displays an error message, along with the help message.

We end up with the table we want with the data in the format we need it in, whilst preventing mistakes.

Multiple data validation rules on a sheet

On the sidebar, we can clearly see what data validation rules we’ve set up so far and in which cells they are.

Other uses

We can also use data validation to make sure emails and URLS are valid, and to make sure certain text formats are entered in the cells.

So, don’t be put off by the name, it’s a really useful tool!

---