Data Validation - The Concept

Hello All!! Today we will be discussing about one more very useful feature of Excel which is Data Validation. I am planning to cover this topic in series of posts (around 5). I planned to write posts starting from the technical side of this concept slowly moving to practical usage side (from boring side to more interesting side, if you put it that way). I am sure most of you are already using this awesome feature but you may find couple of points useful in forthcoming posts. So let’s rock.

Let's assume a situation where in you need to gather certain data from your counterparts across the globe. You created a data template in Excel to send to them. You want;

• to restrict the users to enter anything they please, but allow them to select in few possible options you have
• all of the users to use a standard wording so that consolidation and reporting would be easier for you 
• to provide instructions on how to fill the data 
• to alert them if they are not following what you intended.

You can add an instruction sheet containing all the details required and inform them over a call or mail that they have to read the instructions first and fill the data accordingly. But it is tedious and you cannot even guarantee that your users will follow that. There is this excellent feature of Excel to your rescue i.e. Data Validation.

What is it? 

Data Validation is a method in Excel to control type of data or the values that users enter into a cell. For example you can restrict the users so that they enter only whole numbers or you may want to limit user choices by a list etc. Basically, you can perform following 3 functions using this option.

• Control what user can enter
• Show instructions while the cell is selected 
• Warn or Stop if un-intended data is entered

How to access?

If you are using Excel 2007 or higher, you can access data validation by following; Data Ribbon > Data Tools Group > Data Validation. Short cut key to access this function is Alt+a+v+v. As a compatibility option, Excel supports the previous versions key combination which is Alt+d+l.

Other version of Excel contain this function in Data Tab > Validation. Shortcut key combination is Alt+d+l.

What options are available?

If you are sharing the file for collection of information and want users to fill a consistent and accurate data, Data Validation is your friend. Let’s see in detail what all Excel supports through Data Validation.

When you click on Data Validation, the following screen appears.

You can set the validation criteria in the Settings Tab above. Select appropriate option under “Allow:” field.

If the check box ‘Ignore Blank’ is checked (by default it is), user can edit the cells and can press enter with nothing entered in the cell, it can be blank. Excel will not complain even if ‘blank’ is not permitted as per validation set. If this check box is not checked, a blank entry is also validated and results into error.

Note: If you are selecting 'Custom', you can enter a formula under the 'Formula' field. Excel evaluates the result of this formula with the value entered in the validation cell, if the formula result evaluates to 'True' then Excel accepts the value entered by user, if the result is 'False' then validation fails and Excel throws up the Data Validation messages set below.

Data Validation Messages

Alright, you have set up the validation criteria. But users do not know what values the validation allows and what it doesn't. You can provide the required fill up instruction in the ‘Input Message’ tab in Data Validation dialogue box so that when the cell is selected, instruction is displayed. See the picture below. 

I gave a title and message for validation in Cell A1. When user selects cell A1, this is how it appears.

So that users will have clear instruction on what to enter.

But what if they still do not follow it and enter something which was not intended? You have another tab in Data Validation window i.e. ‘Error Alert’.

I set up validation in cell A1 to allow values 1 to 10 and set up an error message if user enters any value outside that range. See what happens if I enter 11.

My error message is displayed to user. Entered value will not be accepted and user can retry. This is when I select ‘Stop’ under ‘Style’ in ‘Error Alert’ tab. There are 2 more options available under Style field. Look below for explanation for those.

Seemingly fit but Data Validation suffers from a rather serious flaw. It only works if user directly enters value in to the cells. Validation does not work if the cell gets value from a formula, or user copy and paste the values from somewhere else or a macro places the invalid values in the cells.

Help in Excel is fantastically created to explain about Data Validations along with examples. Press F1 and check it for yourself. In the next post, we shall see few examples on how to use Data Validation in real life. See you there!!