Data validation Google Sheets thumb

in Google Sheets

Data validation in Google Sheets

Let’s talk about a practical situation, you have decided that from now on, you and your Finance department will register all the company expenses in Google Sheets. Because everyone can edit things can be simpler from now on.

Data validation Google Sheets 0

On the other hand, if anyone can now edit, you should also think about some data validation rules, in order not to let people write input the data however they want but rather however you want. In this post you will find a comprehensive guide on data validation in Excel.

Our example

You can see out budget (a part of it) in the image bellow. The data we put in the Expenses tab will be summarized in the Dashboard using a Pivot Table.

Data validation Google Sheets 2

What validation do we need

To make sure the data that is entered here is secured, we want to have the following validation rules:

  • Date column: to allow only dates
  • Contract no. column: only numbers greater than 0
  • Status column: to allow only the “Paid”, “Not paid”, “Canceled”, values

Lets begin

Validation #1 – date only

This is not rocket science really, I select column A and then I click on the Data Validation option that I can find in the Data menu.

Data validation Google Sheets 3

And after this, I tell Google Sheets to only allow date values, as you can see bellow:

Data validation Google Sheets 4

And we’re done, next time someone types Tuesday into the date column they will see a message like this:

Data validation Google Sheets 5

Validation #2 – number only

We now move into the Contract No. column, where we want to allow only numbers. To do this, after we select the column, and click on data validation we will have the following settings:

Data validation Google Sheets 6

Validation #3 – list of items

Last but not least, in the status column we want to be able to enter only one of 3 values. We have 2 options here, the first one is to select the values we want to allow in this column (list from a range).

Data validation Google Sheets 9

The second option is to write list elements instead of selecting a range of cells.

Data validation Google Sheets 10After we click save, the table is going to look a little different, like this:

Data validation Google Sheets 11

Other options

Besides these 3 that we covered, we have even more validation options. For example we can allow only text that contains certain characters, a text that is a valid email or URL, or define validation using a custom formula.

Data validation Google Sheets 12These are the options that we have as I am writing this blog post. As time goes by and Google sheets improves we will have more options, like the one to allow only whole numbers.



Write a Comment