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.
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.
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.
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.
Don’t forget to check out our PDF To Excel Converter. It can save you a lot of precious time and improve your productivity.