MS Excel provides a myriad of built-in data validation rules based on custom formula that enables users to control and validate the data entered into cells, ensuring its accuracy, integrity and consistency. With Data Validation, users can define rules and constraints on cell values, helping to prevent errors and inconsistencies in their spreadsheets. This feature offers a wide range of validation options, such as setting data limits, specifying data types, creating drop-down lists, and even creating custom formulas for validation. By implementing Data Validation, Excel users can ensure the reliability and quality of their data – making it an essential feature for precise data management and analysis.
Without further ado, let’s get started. What follow are some pretty basic Data validation rules which you may require on many occasions in your Excel worksheets.
Allow Numbers Only
ISNUMBER() function is used to allow number in the specified cell.
In the image below we’ve set the formula “=ISNUMBER(A1)” in the cell B1 and then copied it in the downward cells. Its obvious that it gives TRUE only for the cells having numeric values.
Allow Text Only
ISTEXT() function is used to allow text/string value in the specified cell. It displays false if the cell contains a numeric value, a picture or a formula that evaluates to a value which is not a text.
Allow UPPERCASE Text Only
To allow a user to enter only uppercase text, you can use data validation with a custom formula based on the combination of UPPER, EXACT, and AND functions.
In the example shown, the data validation applied to cells under J column is:
=AND(ISTEXT(I1), EXACT(I1,UPPER(I1)))
Though not much difficult to understand, let’s explain it. The UPPER function changes text values to uppercase, and the EXACT function performs a case-sensitive comparison. The AND function takes multiple arguments (comma-separated) and returns TRUE only when all arguments evaluate to be TRUE. Hence if the value in the corresponding Ith column is a text value and the entire content is in UPPER case then it will return true as you can see in the image above.
Must Contain Specific Text
To allow only values that contain a specific text, you can use data validation with a custom formula based on the FIND and ISNUMBER functions.
Fill some columns with chemical element names as depicted in the image below. We’re interested in the element that end with “ium”. For instance, Aluminum, Calcium etc.
Now Click Data -> Data Validation -> Data Validation to open the Data Validation dialog box. Provide necessary things along with the formula as shown in the image below for cell A2.
Now what we need is to copy this same formula, of course with its own cell reference, to all cell below in the list. To achieve this click Cell A2 and hit Ctrl+C to just copy. Now select all cells from the list below it, excluding Cell A2. Using Right-click select Past Special option from the context menu. Select Paste Special again from the next box. From the Paste Special box click Validation from Paste list and click Ok. You may not observe any tangible change but in fact it has pasted (implemented) the Data Validation formula from cell A2 to all selected cells.
To highlight cells having values which do not match the data validation criteria, select the entire list. Click Data -> Data Validation -> Circle Invalid Data. You’ll notice that all unmatched cells values being encircled as shown in the image below.
