Log in
Sign up
  • Home

  • Productivity

  • App tips

App tips

3 min read

What is data validation in Google Sheets? 

How (and when) to use data validation in Google Sheets

By Justin Pot · August 3, 2021
A hero image for Google Sheets app tips with the Google Sheets logo on a green background

Spreadsheets are anarchy. You can type whatever you want in any cell, and no one can stop you. I could write "orange" in a column labeled Birthday.

Like most freedom, this is great until you make a mess. 

Data validation is a tool in Google Sheets that lets you add rules, only allowing certain kinds of information in a particular cell, row, or column. This could mean only allowing dates, offering a dropdown collection of words, or limiting numbers to a particular range. Think of it as a hedge against anarchy. 

Data validation is a great way to make sure you're only adding relevant information, and it's particularly useful while collaborating. Here's how to use it. 

How to get started using data validation

I need to collect information about my coworkers' pets, but it's important that this information be accurate. 

A spreadsheet with different attributes of pets

This, I imagine, is the exact scenario that data validation was made for, so let's talk about what I did. 

First: I wanted to limit the second column, Type, to only accept the four primary types of pets: dog, cat, chicken, and other. So I selected the column, then clicked Data > Data validation in the menu bar.

Selecting data validation in Google Sheets

This brought up the Data validation window, with the appropriate column already filled in under Cell range. Under Criteria, I chose List of items, then I typed the four kinds of pets, separated by commas. 

Entering a list of items into data validation

I had to decide whether there should be a dropdown, which I think in most cases you should offer. Then I had to decide whether typing something other than one of the four options would result in a warning or actually prevent that something from being added to the cell. (You can also optionally add help text, if you think the people filling out the spreadsheet will be confused. I did not think that.) 

I clicked Save, and now there's a dropdown for all cells in that column.

Dropdown for animal type

We're off to a good start. Let's move on to the various pet stats. 

As we all know, pet attributes are scored on a scale of 1 to 20. I want to enforce this, if only to prevent my editor, Deb at Zapier, from giving her dog, Winston, a 50 for charisma. I selected the column, clicked Data > Data validation in the menu bar, then used the Number criterion to define a range of 1 to 20. 

Range for data validation in Google Sheets

Deb cannot be trusted, so this time, I set it up to reject invalid inputs. I set the same validation for all four primary pet stats.

Finally, I wanted to establish whether each pet is Very Good or not, so I used one last validation: a checkbox. 

Checkbox for data validation in Google Sheets

This is straightforward enough but useful in all kinds of contexts. 

These are just a few examples of what you can do with data validation in Google Sheets. Going over every possible combination would take ages, so I suggest you dive in and push all the buttons if you really want to get a feel for things. 

How to automatically color code cells in Google Sheets

You might have noticed that my Type column is color-coded. This isn't a data validation feature—it's called conditional formatting—but I thought I'd mention it because it works well with the dropdown menu option. We have a whole article about how to use conditional formatting in Google Sheets, but here's how I did it for the dropdowns.

I selected the column I wanted to color code, then clicked Format > Conditional formatting in the menu bar. From there, I set up custom rules.

Setting up conditional formatting rules in Google Sheets

I wanted cat cells to be blue, so I chose the Text contains option in the dropdown, then wrote Cat. I made similar rules for the other pet types. 

Setting up more conditional formatting rules

It's not hard to imagine using this for non-pet purposes even if I, personally, will not.

Google Sheets is the most flexible tool on the web

We've talked about how spreadsheets aren't databases, and that's true, but data validation makes Google Sheets just a little like a database. It's powerful enough that you can build the tools you need instead of paying for them, which is probably no small part of why Zapier's Google Sheets integrations are so popular among our users.

Zapier is a no-code automation tool that lets you connect your apps into automated workflows, so that every person and every business can move forward at growth speed. Learn more about how it works.

Get productivity tips delivered straight to your inbox

We’ll email you 1-3 times per week—and never share your information.

mentioned apps

Related articles

Improve your productivity automatically. Use Zapier to get your apps working together.

Sign up
A Zap with the trigger 'When I get a new lead from Facebook,' and the action 'Notify my team in Slack'