In this tutorial I will explain you how to use Data Validation on Google Doc Spreadsheet. It is important to use data validation to restrict people from entering invalid data.
For example you can put date as 10.10.2012 or 10/10/2012. On a Spreadsheet the former one is in text format and the latter is in date format by default. So when you put date in text format, it will affect your calculation or sorting of data. So if you collaborate your Spreadsheet with other users let them entry only date on the date filed, not text. This we can achieve by using Data Validation on Google Doc Spreadsheet.
The above is only a single example on the use of Data Validation. Hope you understood the concept what is data validation in Google Doc Spreadsheet.
You can restrict data entry on cells in several ways using the tips below.
In a cell or range of cells;
– Restrict users to enter date only
– Restrict numbers only that also to meet certain conditions like number between, less than, greater than, equal to etc.
– Restrict text input only
– Restrict entering invalid data by providing an option to select from a list. This I will explain on the last para.
First I will explain you how to use Data Validation on Google Doc Spreadsheet?
We will start with date field entry. It will make the things clearer to you.
Select the cells you want to restrict invalid date entry.
Go to Data->Validation
On the criteria field select “Date” and on the right side of it select the criteria. “Is validate” will allow any date to be entered in the selected cells. On “equal to” will only allow to enter the specified date.
Then against “On invalid data” choose either “Show warning” or “reject input”. Then “Save”
Now try to enter any text filed on the selected area. It will show a warning message or clear the entered text immediately based on the above.
Similarly you can try with other validation options like text and numbers
But some times you may want people to select from a list instead of entering data. Means select from a list by clicking on the drop down arrow as below.
This you can achieve this way. Enter the list on any other part of the sheet. Then select the cells where you want to get the drop down list on the Spreadsheet. Now go to Data>Validation.
Select criteria as “List of Ranges” and against that select the list entered. Enable “Reject input” and “Display in-cell button to show list” and save. You will get the drop down list on your spreadsheet as above screenshot. This will also reduce errors in your spreadsheet. Because small typo will provide wrong output in a database spreadsheet as you may do sorting and lots of calculation based on the data entered.
For example if you entered “ISMC100” instead of “ISMC 100” it will provide different output if you use functions like “SUMIF”.
Have any doubt using data validation. Feel free to ask us. Use the comment form below to post your queries.