This is a guest post by Yoav Ezer
If you create spreadsheets for other people to use and input information into, you will know how problematic it can be when those people make mistakes and introduce bad data into your systems. Luckily we can reduce the amount of errors by using the Microsoft Excel validation feature.
What is Validation?
Validation was introduced by Microsoft in Excel 97. Wikipedia informs us that validation can be described as...
"In common usage, validation is the process of checking if something satisfies a certain criterion. Examples would include checking if a statement is true (validity), if an appliance works as intended, if a computer system is secure, or if computer data are compliant with an open standard."
Essentially by using validation we can get the spreadsheet to check the user input against some criteria that we select, and either allow it through or raise an error message asking the user to correct that input.
Using Validation in Excel
First you need to highlight the cells you wish to have validation applied to. Next find the validation options. Go to Data, Validation as shown below.
Next you need to set the validation rules that you wish to apply to those cells. So for example if we were creating a simple invoicing system then you would need to ensure only valid dates were entered in the invoice date column, and so on.
There are all the validation types that you would expect, from the types of values a user can enter, through to the length of the text you are allowed. In addition there is a "custom" criteria which allows you to enter your own rule.
As an example, if we wanted to prevent duplicate entries in our invoicing spreadsheet, we could use the custom option and a rule such as the following.
This roughly translates as "error if this entry appears twice".
To let the user correct their mistake we need to provide a meaningful error message. An alert tab is provided for us to enter this.
Summary
Unfortunately while it can help stop some inputting errors, Excel can only check against the criteria we think of setting.
In addition to this "human limitation", the user can wipe out our validation rules by copying and pasting whatever they like into the cells.
Those issues aside, if we can ask our users to avoid pasting, it is a remarkably useful addition to the package. Hopefully this article will give you the inspiration to use this feature in your own projects.
About the author
Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to Excel conversion software.
For more Excel tips from Yoav, join him on Facebook or Twitter