How to use Custom data validation in Excel 2007
To understand the power of data validation, you should use the ‘custom’ setting.
The following video describes the use of the ‘custom’ setting. Let’s assume for example that you wish to ensure that the cells c3 to c10 under the heading ‘price’ contain only numerical data. We will now use the ‘ISNUMBER’ function to solve this problem. The ‘ISNUMBER’ function returns the Boolean value ‘TRUE’ if the function refers to an Excel cell that contains numeric data and it returns ‘FALSE’ if the function refers to a cell that has a non-numeric value.
Now we select the cells C3:C10, click on the ‘data’ tab in the ribbon, then click data validation in the Data Tools group and finally fill in the ‘Settings’ tab – under ‘Allow’ we fill in ‘custom’ and under ‘Formula’ we fill in ‘=ISNUMBER(C3). We click ‘OK’ to finalize our work. Now if we try to enter non-numerical data into the cell c3:c10 we receive an error prompt.
Data Validation Examples Custom Criteria