Google Sheets is built around the central fabric called collaboration. Therefore, it is a commonplace that multiple people work on a single Google Sheets file. And when doing so, we may have to restrict people from entering unwanted values in cells, accidentally or otherwise. In this article we explain how to validate input values in Google Sheets before they are entered into the cells.

Why validate input values in Google Sheets?

Let us consider an example to understand the usefulness of validating the input values. Below is a snapshot of a simple money returns calculator. The formula in cell B7 considers numeric inputs from cells B3 through B5 and accordingly produces output in the cell B7.

Example Validate input values in Google Sheets - Illustration 1

So far so good. What if somebody inadvertently tries to input text values into any of these input cells? Let’s find out.

Example Validate input values in Google Sheets - Illustration 2

The formula just returned an error as it cannot process text values for calculations. This is where the data validation can help us restrict the type of input(s) that goes into any particular cell(s). Let us try and apply the validation to cells B3 through B5, so that we will only be able to enter numeric values. To do so, we select the corresponding cells and navigate to Data > Data validation.

Example Validate input values in Google Sheets - Illustration 3

We configure the following criteria in the Data validation window pane that pops up, and click on the Save button.

Example Validate input values in Google Sheets - Illustration 4

Now, let us try inputting a non-numeric value in one of the cells we applied data validation for.

Example Validate input values in Google Sheets - Illustration 5

List of values

This Data validation functionality offers multiple ways to validate input values in Google Sheets. We could enforce only text or date values within cells. Or, we can even configure such that it allow only a list of values within the cells. We can accomplish the latter with the following configuration.

Example Validate input values in Google Sheets - Illustration 6

Now, the cell B1 will now have a drop down that lists the values we’ve entered as part of the criteria. If we try and enter any other value, it rejects the input with an error. Please note that if the list of values is huge, we can also fetch them from a range of cells with predefined values.

Example Validate input values in Google Sheets - Illustration 7

Custom data validation

Here’s a demonstration that we could also customize data validation using a formula. Let us try and enforce a validation rule that an email should have both ‘@’ and ‘.’ (dot) within the value. Here’s the formula that validates it.

=AND(NOT(ISERROR(FIND("@",A7))),NOT(ISERROR(FIND(".",A7))))

Example Validate input values in Google Sheets - Illustration 8

Having set the above configuration, we try to input some random value that isn’t a valid email address. And this is what happens.

Example Validate input values in Google Sheets - Illustration 9

Alternatively, we suggest you could also go with Google Forms where you can structure and validate the inputs in a better way. Here’s a blog post that can help you in this regard.