Restrict People from Entering Invalid Data on Google Doc Spreadsheet

1
885
validation

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?

data validation

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.

create list on spreadsheet

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.

1 COMMENT

  1. The data validation… I see how to you use it in the spreadsheet, but when a form is connected there is no error message given to the user submitting the form

LEAVE A REPLY

Please enter your comment!
Please enter your name here