How do I validate input values in Google Sheets?

What is data validation?

Data validation is a system that makes sure values in your spreadsheet conform to expectations, and thus validate input values in Google Sheets. It works like a gate, only allowing cells to be filled with data that match a defined rule.

Simply put, data validation works by giving a range of expected values to a cell. If the value entered is not in the correct format, or outside of the expected range, Sheets will show an error to the user that says their input is invalid. You can also reject invalid inputs all together.

The other thing data validation in Google Sheets can do is create drop-downs within a cell. A great time saver, because it means that you don’t have to memorize long lists like SKU numbers or how to spell all the names in your spreadsheet.

Why validate input values in Google Sheets?

Google Sheets is built for teams. Collaboration is one of G Suite’s strong suits and differentiators when compared to similar softwares. Because it was built for the web from conception, it easily handles multiple users in a single spreadsheet, whether or not you’re working at the same time.

However, many users working on the same spreadsheet can more easily cause errors. When you don’t know who made an error or why they made it, your interpretation of the data is compromised. Errant data can also break complex formulas that take time and resources to fix. Moreover, if you enter wrong data that erases important information, you might not be able to recover it.

On the other hand, when everything works correctly, spreadsheets are a wonderful tool. For example, I have this monthly sales data for a wholesale company. On the side, I made a little chart that tracks total sales for each sales agent. Make a copy of the data here to follow along.

The more you do with your spreadsheets, the more formulas you use, the more likely they are to break when you enter data incorrectly. Formulas are finicky. In large spreadsheets, one missing or mistyped cell can create a string of errors that take valuable time to trace back to the source.

Take a look at the example below. I mistyped an SKU number for one of Cathy’s sales. Not only does it generate errors for that sale, it breaks her total sales chart too.

Formatting isn’t always clear, and it’s difficult to spot errors like a single mistyped digit. Instead of remembering and manually typing each time a sales team member makes a sale, let’s create a dropdown list with all SKU numbers for our products.

That’s where Data validation comes in. Keep reading to find out how we solve this problem

How to access the Data validation menu

Find the Data validation menu either in the Data tab in the menu bar, or right-click on the range you want to validate and select Data validation from there.

Once you’ve opened the menu, you’ll see the different values you need to supply in order to have a working data validation for your spreadsheet.

Data validation menu options

Take a look at the screenshot below. Here is how we filled in the various boxes for our sample data.

Underneath the image, I explain each section and what it means for our set of data.

Cell range

This is the range to which you want the validation to apply. This automatically fills with the range you had selected when you opened the data validation menu.

If you don’t have any range selected, type it here directly, or click the Select Data Range button on the right of the Cell Range dialogue box. Because we had our column with SKU data selected when we opened the menu, it auto-populated.

Criteria

The criteria value defines both the type of range and size of the range of valid data. We’ll go over each option in detail below.

For now, because we have our unique SKU numbers and their corresponding prices listed on our Prices tab, we’ll go with the List from a Range option and use the select data range button to pick our SKU numbers from the Prices page. It should generate =Prices!$A$2:$A$11 when you click OK.

Show warning or reject input

This option is a switch that lets you either reject a value that falls outside of the valid range, or let the invalid data be kept and just show a warning that the data is invalid.

Opting for rejecting invalid inputs shows the validation help text in a popup. Opting to show a warning will show the help text as a dialogue box when the cell is selected. We’ll go with Show Warning for our example.

Show validation help text

This is the message displayed when a cell with a data validation parameter is selected. You can customize the message to say whatever you want. This is especially helpful when you’re passing the spreadsheet off to someone who is less familiar with Google Sheets.

This way, you remind them what value goes where without wasting time training and reminding others.

In our example, we want to show validation help. And instead of it saying Click and enter a number from the range, we want it to say “Click and enter a valid SKU” so our users know which range we’re talking about.

Data criteria types and functionality

Each of the options in the criteria menu has a unique use case. All of these options can be valuable depending on the situation.

Here is how each functions and when you might want to use them.

1. List from a range

This is the criteria we chose in the previous example. It suits best for a set of values that you have elsewhere in your spreadsheet.

You can also select a range that extends past your list of values. Whenever you then add a new item to your list, it automatically adds up to the dropdown of your data validated range.

2. List of items

Like a list from a range, a list of items creates a dropdown menu for your cells. Instead of having to have the list of items you want in your dropdown somewhere else on your spreadsheet, you can list them out right in the criteria option box from the data validation menu.

3. Number

A number range lets you set limits on the number values entered in the specified range.

You can use all the traditional comparatives from mathematics (less than, less than or equal to, not equal to, etc.). You can also use between and not between to require or exclude values that fall in a range of numbers.

4. Text

Text is a uniquely powerful option in the data validation criteria list. With it, you validate strings that contain or don’t contain certain characters or strings of characters.

It also includes rules that require inputs to be valid URLs or email, something that could be useful if you’re using a Google Sheets add-on like YAMM.

5. Date

The date value criteria does more than you might expect.

Not only can you require values to be entered as a date, but you can also require values to be: before, after, between or not between specified dates.

6. Checkbox

The checkbox criteria is essentially the regular checkbox cell format, found in the Insert menu dropdown.

The only difference is that you can alter the checked and unchecked values for cells. By default, they’re set to true and false however.

7. Custom formula is

Last but not least, we have the Custom formula is option. This is the most powerful of the options because it allows you to write a custom formula.

For example, imagine our wholesale company has a minimum order number of 500 for their products which cost less than $10.00 per unit. We want to check that either the PPU was over $10.00 or the number of units ordered was over 500.

The formula for that is =OR($G2>10,$F2>500). We want to only warn, not reject, invalid data because it is possible that a salesperson made the mistake of under selling on a low-cost product.

We also include the help text of “Minimum Order Size of 500 on products under $10.00 per unit,” so that it’s clear why the value was not validated.

When we apply that validation to the Order size column, we see that one sale wasn’t validated.

Next, we want to either double-check that the information entered was correct, or fix the mistake if we know the correct values.

Wrapping it up

Now we’re able to collaborate with our sales team and they are able to manage their data without accidentally breaking the spreadsheet.

You can also protect certain cells or entire pages in Google Sheets. This solution only works if you want to make sure that only certain people can alter certain cells. Read through this blog post to find out how.