Data validation
In addition to this article there are many examples of using data validation contained in the
Data Validation Examples workbook.
Data Validation, What is it ?
Data validation is a tool that can help you control the input/changes someone can make in a spreadsheet. It can help you or your users to make choices, guide them to make relevant input/changes, or restrict input to a specific type of data or structure. It can help you or your users to save time and to keep formulae working properly.
Where can I find this tool, and how does it work ?
Lets start using it immediately and create a simple example 
- Select one single cell somewhere on a worksheet.
- Then in the worksheet toolbar goto > 'Data' > and click on 'Validation'.
- If not already selected, select the 'Settings' tab in the validation pop-up window.
- In the 'Allow' drop-down list, select 'Whole number'.
- In the 'Data' drop-down list, select 'Equal to', and type 100 in the 'Value' box.
- Now push ok and try to enter a number in the validated cell smaller or greater then 100 and see what happens...
Now we have made our first validated cell, lets take a closer look at some of the other options of the validation tool. With your validated cell selected go back to the validation pop-up window via 'Data' > 'Validation'. Select the 'Input message' tab and type in both the 'Title' and 'Input message' boxes the text you want to display when the validated cell will be selected. Push ok to close the validation window and see your custom message appear.
There is still one other option left we have to check out. Once again, with the validated cell selected, go back to the validation window. This time select the 'Error alert' tab and, as you can see, you can, as in the 'Input message' tab, type a custom title and message which will be only displayed when an invalid input is made.
Type in a title and message and select the 'Style' drop-down. In the 'Style' drop-down you can select the type of warning you'll get to see once an invalid input is made in the validated cell.
- 'Stop' means that it's impossible to enter data other than allowed by your validation settings.
- 'Warning' will warn for an invalid input but you are still able to accept the input if you want.
- 'Info' will as 'Stop' warn for an invalid input but will still accept it.
Now you have finished this example, take some time to check out some of the other possible validation criteria in the 'Allow' drop-down on the 'Settings' tab. Playing with these other criteria you will notice the most of them are self explaining and pretty easy in use.
The available options are:
- 'Whole number' - allows only whole numbers in the range specified to be entered.
- 'Decimal' - allows decimal and whole numbers in the range specified to be entered.
- 'Date' - allows only dates in the range specified to be entered.
- 'Time' - allows only time values in the range specified to be entered.
- 'Text leght' - allows only a text or number of given lenght in the range specified to be entered.
- For 'List' and 'Custom' see below.
'Any value' allows any entry in the range specified to be entered, so we can't really speak of a validation, but we still could make use of the 'Input message' or 'Error alert' to display a custom message when the cell is selected or after an entry is made.
This is so simple, but how about more custom/complex validation criteria?
Well ok, if you folks want to play it hard we can move on to more complex validation criteria.
Anyway we don't want to play it too hard no... 
Ok then, we will look at the 2 more complex validation options 'List' and 'Custom'.
A custom list is a list of predefined and allowed entries and can be stored either within a worksheet or straight in the validation pop-up window.
Select a cell and get back to the validation window and select 'List' in the 'Allow' drop-down on the 'Settings' tab. You can type the list data directly in the 'Source' box or specify the location of the list on the worksheet. As a test, type some words all separated by a comma (dependent on your international settings this may be a semi-colon) in the 'Source' box and click 'Ok'. Now go back to your cell.
You will notice it now has a dropdown in the cell containing all the items you typed into the 'Source' box. In case you prefer to not have a drop-down shown when a validated cell is selected, uncheck 'In-cell dropdown' checkbox on the 'Settings' tab.
If you want to make use of data stored on a sheet then you only have to select that range whilst the source box is activated, or in case you want to make use of data from a named range, type = followed by the name of the named range. Keep in mind the data range can only exist in one single row or column. If you want to make use of data stored on a sheet and the data is not in a named range, then the data needs to be on the same sheet as the validated cell. If you make use of named ranges the data can be stored anywhere in the workbook.
Custom validation.
With custom validation we reach the most flexible and powerful way to validate a cell. Custom validation can make use of formulae and operators just like the standard Excel functions do. A little example will make it clearer than a 2 page explanation...
- Again select a cell and the validation pop-up window, and select in the 'Allow' drop-down 'Custom'.
- Now type in the 'Source' box = followed by the cell address and =100 (i.e. =$A$1=100).
- Push ok to close the validation window and try to enter a number <> 100 in the validated cell.
The above is a very simple and pretty useless example since we just learned we can
achieve the same with the allow 'Whole number' option.
But as you will see in the Data Validation Examples workbook,
you can realize the most complex validations using formulae.
The best and easiest way to build this kind of (complex) validation is to write your formulae straight into the worksheet as you would do when writing or working with the standard functions. Of course, you will need to write the formulae in such a way that it will test the value of another cell. Once you have your formula working properly type a ' in front of it to keep it visible whilst working with the validation pop-up window.
Some things to keep in mind are that the formula always needs to return True or False and that you can't make use of the functions that are part of the Analysis Toolpack.
Note that we also can make use of formulae when validating cells for a numeric, decimal, Date, time and text length input. Again a little example to make things more clear:
- Type in cell A1 a word from 4 characters long, and in cell A2 a word from 8 characters long.
- Select cell A3 and goto the validation pop-up window.
- Select 'Text length' in the 'Allow' drop-down and 'Between' in the 'Data' drop-down.
- In the 'Minimum' box type =LEN($A$1) and in the 'Maximum' box =LEN($A$2).
- Push ok to return to the worksheet an try to enter a word or number in cell A3 from less then 4 characters or more then 8 characters long.
Are there other things I need to know about data validation?
If you want to allow the cell that you've validated, to be blank, or when you use a cell reference or formula that depends on cells that are initially blank, make sure 'Ignore blank' is selected in the 'Settings' tab. If you want to treat these blank cells as if they contain zeros, clear the 'Ignore blank' check box.
Data validation is not foolproof, it can be removed with copy/paste or by Edit > Clear > Clear All. If you copy a cell/range to a cell/range that uses validation, the validation will be deleted!
I hope that I have provided you with enough information and examples to allow you to create powerful data validation in your own workbooks. In addition to the validation examples the workbook contains examples of dynamically created named ranges. For more information on creating these see this (link to follow when finished). If there is anything you would like to see covered in more detail or require clarification please email suggestions@markrowlinson.co.uk.
