We are glad to release the 'Data Validation' functionality in Zoho Sheet. Using this, you could restrict the data that is entered in cells to a specific condition that you set. If it is a numeric cell, you could add numeric constraints like the number should be less than 20, or between 40 and 60, etc. This could be really useful when you have a calculator like functionality on your spreadsheet and expect the input data to fall within a range. Similarly you can set rules on cells with Text and Date formats as well. This functionality is available under the Data tab on our toolbar.
Another great use of Data Validation is to restrict a cell to a pre-defined list of values. You could select the List option (see image above) and enter the pre-defined list of values or you could select the Cell range option to point to a cell range in the sheet that contains the list of allowed values. When you use either of these options, you will be presented with a drop down list when you are about to edit a cell. Type ahead (auto-completion) is also supported on these cells. This helps in quick data entry as well as helps to keep the data consistent. See screenshot below for the drop down list in action.
Error Alerts and Help text:
The error alert setting lets you choose how the spreadsheet should respond when you give an invalid input. You could choose to not allow it and prompt a message to retry or you could choose to accept it but show an informational tooltip. You could also choose to accept it as it is without showing any kind of message.
There is also an option to provide an Help text which will be shown when one focusses on a cell. This helps the person who is editing the spreadsheet to know what kind of input is accepted.
Manage Data Validation Settings:
We also provide a 'Manage Valdation' functionality that lists all the Data Validation rules on your spreadsheet in a single view. You could edit your existing rules, clear them or copy them to a new range. You could also filter the validation settings by Sheet or Cell Range.
Here is a sample spreadsheet with Data Validation in action. Try editing the values under the Status and Priority columns in the first 20 rows. As you focus those cells, you will notice the dropdown icon which will bring up the allowed list of values. And as you type in those cells, the list will be filtered and displayed allowing you to speed up data entry. Also try entering values which are not present in the list to see the error and information alerts.
We hope you find this really useful. Do try it out and share your feedback.
I have one question: I have a certain number of separate zoho sheets which basically use the same "template" in terms of data structure/arrangement. Assuming I would like to implement few data validation on the "template", if I do it brute force I will modify each file individually = time demanding + prone to error.
Instead doing this, is there an easy/intelligent way to deploy a same set of data validation across multiple zoho sheets file who shares the same data structure ?
Thanks to look at the above question and as a bonus let me add this one :
Is it possible to refer to a separate zoho sheet document to perform data validation on another zoho sheet ? The reason is obvious, maintaining a single list of data validation rules when using more than 1 file with the same template.
What kind of Data Validation settings are you using? Are you referring to a cell range for a drop-down list? Can you share any sample file of yours, if the data in it is not condidential? You can share the spreadsheet with firstname.lastname@example.org.
If you are creating spreadsheets from scratch each time, then you can probably create a new template in Zoho Sheet with the Data Validation settings applied. You can then create new spreadsheets from this template.
However in your case, I believe you already have a few spreadsheets and you need to apply the same validation settings to your existing spreadsheets. We will analyze this use case and then keep you updated.
For some reason I did not get notified of your answer and by chance today I decided to check manually...
My spreadsheet is very simple - I cannot share the data but simply imagine it's like a sales forecast management where different channels have access to their own worksheet using the same format and without sharing to the other (of course). However all have the same data validation rules - in some column like for Product name I use cell range for a drop-down list to restrict the potential input (i.e. avoid unknown products), but I also use for regular validation like for quantity field (validation of whole integer),... standard stuffs.
So some info like the product name is common to all channels and subject to growing/being updated/etc... so now if I need to update the list I need to go through each worksheet and apply the modification manually. Aside from the chance for error, it is simply not efficient at all so either I would like to find some automated rules application on several worksheets or bing able to refer to an external worksheet to provide data validation rules for a given spreadsheet.
Hope it makes sense for you. Let me know your ideas.
I cannot get Data Validation to work. I have a cell that needs a
dropdown list. I have tried to Add Validation to this cell. However, the
list of data that I enter does not save to the cell.
Steps I have taken:
- I have added each list item manually, but the Validation does not
save to the cell.
- I then have copied the list of text items that should populate
the validation list from another region of the spreadsheet and copied
that list of cells into the Add Validation list field. The list of
items does not save to the validation dropdown.
- I have tried to 'copy validation' from a range of cells within
the spreadsheet that contains the list of dropdown items. The list
still does not save to the dropdown.
When I go to 'Manage Validations', none of my created validation
rules appears. If I try to 'Create Validation' from the 'Manage
Validations' screen, no validation rules will save to the
spreadsheet, using any of the methods listed in the paragraph above.
In each of the above scenarios, the Data Validation 'Help Text'
that I enter manually does popup when I place my curser in the field
that should contain the dropdown list. A 'down arrow' does appear to
the right of the cell that should contain the dropdown list. The
cell turns 'white' and has a border around it when I place my curser
in the cell that should contain the dropdown list, but no dropdown