Concept of Data Validation

Concept of Data Validation?
Data Validation is a tool provided by MS Excel that allows you to validate data that is entered by a user. The validation happens once the user enters data and hits the return key (enter key).

Why should I use data validation?

Data validation, though a simple tool, can be of lots of help. It’s very core function (of validating data) can be applied to many other tasks. Here are a few things that can be done using data validation tool:

The very basic function of data validation is to validate the data. This tool can be used to get correct data from the users. It can also be used to get desired data, by the way of limiting the cell input using custom input
In large shared spreadsheets, which are updated real-time by more than one user, certain columns can have restricted data entry. This can limit the incorrect entries to the cells
Keeping a check on inconsistencies. This can be achieved by creating a list of certain words, which go in a certain range of cells and these can be suggested to users by giving them an option to select from drop-down menus. These in-cell drop down menus can be created using data validation
Custom error / alert / input messages can be displayed to users after or before a cell entry is made. Error messages are shown when an incorrect entry is made. The input message is shown when a certain cell is selected, where the input is required. On selection a message can be displayed which can show instructions or warnings etc.
In-cell drop down menus. This is one of the most useful and my favorite uses of data validation

What validations can be applied to the cells?
As I told earlier the cells entries can checked for many types of validations. For example, I can restrict a certain range of cells to accept only numeric value; I can also limit the range within which the cell values can be input. I can also restrict the cells to accept only date values or time values, etc.

Here is a detailed explanation of what exactly you can do with this tool:
Validations: The first tab of the data validation tool is where you can define you validation rules. These are the following rules that can be applied on the cell or range of cells –

Whole numbers: You can use this option in case you want the cell to accept only whole numbers as input from the users. You can also define certain limits to the whole numbers also, such as minimum and maximum limits of the range, which is acceptable, other rules such as equal to, more than, less than, not equal to, etc.
Decimal numbers: This option allows you to restrict the cells to accept numeric values, which can also have decimal values. The difference between this option and the previous option is that, in previous option only whole numbers could be input and with this option you can allow decimal numbers also. You can apply same rules on the decimal values as you can apply on whole numbers.
Date / Time: These are 2 different options with which you can restrict the cell entries to be either date or time value. Again, on these options also you can apply equal to, not equal to, more than, etc. rules
Text Length: This option restricts the cells to contain only a limited length of text. The text here includes all valid characters in Excel.
List: This option allows you to validate the cell entry with a pre-defined list. You can define your list, with words or phrases, which you wish should only be entered in the cells. This option allows you to provide the user as in-cell drop down menu with which one can select a particular item from the list. This way you can restrict the data input in the cell together with giving the user a drop down list of required items.

Message Alerts / Error Alerts: This are alerts which displayed to users once either the user clicks on the cell, which has data validation active, or enters invalid data (as per the validation rules). Through error alerts you can either give a ‘Stop’ message or ‘Warning’ message or simply an ‘Information’ message to alert the user about the type of message input.

How to apply data validation?
The following steps will guide how one can apply data validation to a range of cells. The following steps can be applied in Excel 2003 and Excel 2007 as well:

Select the cell or range of cells to which data validation is to be applied
Go to 'Data' menu and then click on 'Validation'. The Data validation wizard will open up
On the validation tab, click on the drop down menu, where currently 'Any Value' is shown. Currently, the cell can accept any value
You can choose from the various validations that are available in the drop down list
Once you select the validation type, another drop down list will show up (depends on which option you selected on previous step) which will list the various rules that can be applied on the validations
In case you would like to activate Alert or Error messages, those can be defined in the next tabs

In the below image, I have applied validation for whole numbers, between the range of 0 to 100.

Note: Though data validation is one of the very useful tools in Excel, it also could not keep itself from some limitations. One of the major limitation (or call it a bug) is that it does not work when some data is pasted using CTRL+C and CTRL+V. When some data is pasted on the cells, which have validation, the cell entries are not validated.

If you are familiar with the concept of Data Validation or have worked with this concept after reading this post, I would like to know about it. Share your experience using data validation tool with us, issues you faced and how did you solve it.

About Dhakkanz

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.

Saturday, July 4, 2009