What is data validation in Google Sheets? 

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. 

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.

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. 

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.

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. 

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. 

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.

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. 

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.

Read more: How to wrap text in Google Sheets