Have you ever came across information that should not be there in your Excel sheet? For example, let’s say you have an Excel sheet that you have passed to another person to fill up and then pass back to you. What the other person fills up is not in your control. You just trust that he or she knows what to fill up. When he or she is done and passes back the sheet to you, you open the sheet, only to find out that the information entered is not exactly like how you want it to be.
For example, if you have an Excel form, and you need a Gender information only to be indicated as M or F, and the other person puts it as Male or Female, it can frustrate you. It may not be a big issue to the person who fills up as it is understandable that M represents Male and F represents Female, so putting the information either way is still valid. But for you, it can be an issue, as you may use this information to be compiled with other data already has M or F as the intended Gender information. Imagine compiling Gender information that has varied entries such as M, F, Male, Female, or even in other languages like Lelaki (Male in Malay), Perempuan (Female in Malay), L, P, or even half-spelt or wrong spellings like Fem, Mela, etc. You will have a headache of cleaning up the data and making sure that the data is how it should’ve been; M or F.
So what to do if you want to make sure that the other person who fills up your Excel sheet will put in the right values; M or F? To address this, Excel has a built-in feature called DATA VALIDATION. If you enforce data validation in your Excel sheet, it will make sure that the date entered in cell is according to the rules that you set, e.g. only number can be entered for age, select only from the list of department available, your Identification Card number must only have a maximum of 12 characters, etc.
To know how to use it, let’s try sort out the problem that have been highlighted in the previous paragraph. Create something like below in your Excel sheet:
The data Male is purposely entered so that you can see that even though it has been indicated that you only need to enter M or F, different info with the same meaning can be entered. So to enforce Data Validation, first select the cell that you will write M or F. Go to the Data tab, and click on Data Validation, and then on the sub-menu Data Validation.
In the Settings tab, for Allow Value box, choose List. For Source Value, write down M, P. and click OK.
Back in your sheet, remove the data from the cell. When that particular cell is clicked on, you will see an arrow beside the cell indicating there is a dropdown data list, and clicking on it will reveal the M and F. Choose either one.
Try deleting the data from that cell and enter the words Male. You will see that Excel stops you.
So you have successfully restricted the person to just choose the data from the dropdown list. You can make this process more informative by using the other 2 tabs in the Data Validation screen: Input Message and Error Alert.
Make sure you have clicked on the target cell. Go back to the Data Validation screen. In the Input Message tab, you can enter a message that guides or warns the user on what info to enter in the cell, like shown below:
Click on Error Alert tab, and here you can specify what message to display to the person if he or she enters the wrong data.
Now back to your sheet, click on the target cell and you will see the guide popping out:
Enter the info Female into the target cell, and you will see that you will again be stopped by Excel but now with a proper message:
So this is a simple way of implementing Data Validation in your Excel. There are many more scenarios that you can validate using Data Validation feature for the info entered in the sheet. It will be very helpful in controlling what info other people can enter in your Excel sheet, making sure that the data entered is the info that is according to what you want. It will make your life very much easier.
Authored by Subashkaran Perumalu, IT Trainer
Edited by Dr Iswera Lallan, Top 100 Author Worldwide
Malaysia Book of Records Holder
Dr Lallan's E-Magazine 