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