How many were the times I was called to see a “stick” that was giving in some spreadsheet, to analyze, I conclude easily that the problem was a particular cell that hoped a date and was filled with a single value, or text or a date in crazy format? Or a value field that, instead of typing a zero, you put the letter “o” or “-“? Numerous! Countless! How much money seen companies losing time employees and consulting hours, for in the end, come to the conclusion that if people take more care with data entries, no problem would have occurred? You can not know.
I will present below a very simple feature that is observed and implemented, can reduce, and the chances of failure in spreadsheets. Let us see then how the data validation feature. For this article, I created a simple spreadsheet that with few fields and exemplify the use of it:
Our first step will be to limit the range of values that can be used for the code field. To perform this task, go to the Data menu and then validation data.
Accessing data validation
You will see the validation definition screen. All cells accept, by definition, any value in their fill.
data validation box
To begin to define validation , we must first change the value of the field to allow integer, and define maximum and minimum to 1 and 100 respectively. Thus, any value outside this range will be rejected .
Setting the range of valid values for the code field
the setting screen data validation has three “tabs ” . The first is the rule definition , the second is the incoming message , as illustrated below and the third is the error message .
Defining field input message
In the screen above , we have defined what the incoming message displayed when the user is editing the cell code.
The third TAB is defining what the EXCEL behavior in case of invalid value for the field . there are three possible behaviors :
STOP – stop values outside the set . Total protection .
WARNING – warning about invalid value and gives the user the option to accept the value anyway.
INFORMATION – only informs that the rule was broken, but accepts input .
Behaviors available for error handling .
For this exercise we use stops to guarantee the range of values of 1 to 100. If the value entered is outside the established, the following message appears and the value rejected .
Setting error message in case of invalid data
Notice in the screenshot below that with the selected cell code, an incoming message is displayed , guiding the user as to whether :
CODE field , already with active validation. Note the input message
If however , the value is wrong informed the error message that we set will be displayed and this will be rejected.
error message when an invalid data was entered.
Below , we define the rules for the validation date of birth fields. We choose the type to date and define an acceptable range .
Setting rules for the date of birth field
Now , we define the acceptable values for the gender field. Because it is a list with two options , put directly in the setting box , separating the male and female options by a semicolon (;). If it were a greater number of options , we could put them in cells in the spreadsheet and inform the range. For example , ” J2 = : J6 ,” where five items would be included in the list .
Defining list of valid values for the gender field
See below the worksheet already showing an object that enables the choice of the cell fill. In case user enter directly into the cell without passing through the switch , the validation is performed in the same manner and the rejected value , if so defined .
Spreadsheet displaying the sex list to fill the cell
Here we define an acceptable salary range.
Defining valid range for the salary field
Done! We define data entry rules for our fields. In this way , we will always make sure that the data reported are within what we define initially when the sheet planning.
end form with all implemented rules
I managed to show you the importance of activating the validation of a spreadsheet data entry and also how easy it is to make it all work .