Data Validation

Back from his refreshing trip to Himalaya, Bahadur Singh is back to his routine work, though a bit dissatisfied. Dissatisfied because he only got a few minutes of discussion with Excel, whereas he wanted much more. As soon as he joins office he is assigned an activity for creation of checklist. This checklist has a number of multiple choice questions. The requirement is against each question there should be a drop-down box of answers from which a user can select his reply. Not knowing which software can meet his need he comes to us for a solution. How can we achieve this in Excel?

Select the cell where u want to configure validation and Go to Data >> Validation. The screen as shown below appears.

The box contains three tabs:
1. Settings – The first tab i.e. settings tab gives us the validation criteria. By default the selection is any value. Other options include:
1.1. Whole Number – On selection of this option one can specify the parameter of data like between, equal to, greater than, etc. and also provide for values satisfying the above criteria. On activating this, the cell will accept only those value that satisfy the criteria entered.
1.2. Decimal – This is similar to Whole number except that one can also enter decimal numbers in the criteria.
1.3. List – This is one of the most useful option in validations. Here one can specify any value in the source field. Example the value can be Yes, No, NA or they can be Good, Bad, OK, etc. While activating this option there is a checkbox on the right side of screen for ‘In-cell dropdown’. By keeping this option active i.e. by checking this box the value as provide in the source box will be available as a drop-down to the user. The user will than only need to select from that list. This can be very useful while we prepare some checklist like applicability of list of accounting standards for a particular company. We can configure a validation for Yes, No and NA.
1.4. Date – When one wants to restrict a data entry in form of date with a certain criteria this option is useful.
1.5. Time – This is similar to Date for using Time.
1.6. Text length – This is an option where a length of a particular statement to be entered in a cell can be controlled. This can be useful for remarks column in many of our checklists.

2. Input Message – Here one can provide the statement that is seen by user when a cell is selected. On selecting this tab the following screen will appear.


Select the cell that has a particular validation discussed in first tab and than go to Data >> Validation >> Input Message (Second Tab). Check the box ‘Show input message when cell is selected’. Give a Title and an input message. By doing this whenever a user clicks that particular cell the message as given here will appear.

3. Error Alert – This tab is useful to decide whether an incorrect entry in a cell with validation has to be rejected out-rightly, accepted with a warning or an information message has to be given to the user. On selecting the third tab the following screen will appear:




Here in the style menu one can select Stop, Warning or Information. Selection of Stop would mean an incorrect entry would be rejected. Warning and Information will accept an incorrect entry but will throw up a message. The message in all the above cases can be configured in the space provided for Title and Error Message.

The above function is very useful to prepare audit checklists, AS applicability checklists, feedback forms, etc.

Excel the saviour again…