Skip to: Site menu | Main content

Login

Name: 
Password:
Remember me?
Register

Data validation

written by Kid Van Ouytsel - Last updated Oct 2004

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

As you'll notice only a value equal to 100 is allowed in the validated cell.

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.

Push ok and try to enter a value <> 100 in your validated cell and watch your custom message appear.

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:

  1. 'Whole number' - allows only whole numbers in the range specified to be entered.
  2. 'Decimal' - allows decimal and whole numbers in the range specified to be entered.
  3. 'Date' - allows only dates in the range specified to be entered.
  4. 'Time' - allows only time values in the range specified to be entered.
  5. 'Text leght' - allows only a text or number of given lenght in the range specified to be entered.
  6. 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'.

Lists.

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...

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:

You could then simply change the number of characters allowed in cell A3 by typing words of a different lenght in cell A1 and A2 !

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.