Data Validationis a Microsoft® Excel® feature that you can use to define restrictions on what data can or should be entered in a cell. In this tip we show you how to use custom data validation to ensure that other people enter the correct type of data in cells, and that they are notified if the data is incorrect. Our example exercise is on ensuring that account codes are captured accurately, that they begin with the standard prefix of ”ACC” and are at least 6 characters long.
Note: Download the sample workbook to practice this exercise.
Applies to: Microsoft Excel 2007, 2010 and 2013
The screen shots below will be used for this example.
1. Select the data range A3:A13.
2. Select the Data tab and then Data Validation as per screen shot below.
3
3. Enter the Validation criteria as shown below.
Note: The formula is =AND(LEFT(A3, 3) =”ACC”,LEN(A3) > 5)
· AND Both conditions should return true.
· LEFT The first three characters from the left must start with “ACC”.
· LEN Returns the number of characters in a text string, greater than five.
5. Select the Error Alert tab and enter the title and error message as shown below.
6. Select OK.
7. Enter AC098 in cell A3 and press Enter. Since the account code “AC098” does not meet the validation rule the error message below will be displayed.
8. Select Retry and enter ACC001 and press Enter. The account code “”ACC001” will be accepted because the prefix is ACC and there are six characters.
For more excel tips, stay tuned to the Equation Technologie Blog!
