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.
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.
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.
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
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.
And after this, I tell Google Sheets to only allow date values, as you can see bellow:
And we’re done, next time someone types Tuesday into the date column they will see a message like this:
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:
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).
The second option is to write list elements instead of selecting a range of cells.
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.