Excel tip: Using Custom Data Validation to ensure accuracy in data capturing

Using Custom Data Validation to ensure accuracy in data capturing:

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.

describe the image

 

1.     Select the data range A3:A13.

2.     Select the Data tab and then Data Validation as per screen shot below.

describe the image3

 

3.     Enter the Validation criteria as shown below.

describe the image

 

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.

describe the image


5.     Select the Error Alert tab and enter the title and error message as shown below.

describe the image

 

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.

7

 

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.

describe the image

 

For more excel tips, stay tuned to the Equation Technologie Blog!

Need Help Now?

Request a Call