Nov 30, 2015

Data Validation is a feature of Excel that can greatly improve the quality of data that is entered into cells by limiting what a user can type. You can create simple rules using the Data Validation dialog box’s user-friendly menu options, but what if they aren’t’ enough?

Custom Data Validation rules involve using a formula to determine what is and isn't valid. For example, what if you want to limit cell A1 to just 4 digit numbers (eg a postcode cell)? You would need to ensure that:

1. The cell only allows 4 characters
2. The characters must be digits

The formula would read: =AND(LEN(A1)=4,ISNUMBER(A1))

To break it down:

• LEN(A1) refers to the length of the entry so LEN(A1)=4 says the length must be 4 characters
• ISNUMBER(A1) says that the contents of A1 are a value (ie a number)
• The AND() function simply means that the above two points must be true

To create the rule, follow the steps outlined below:

1. Select the cell or cells you want to apply the rule to
2. Select the Data tab on the Ribbon
3. Click the Data Validation button
4. From the Allow drop-down list, choose Custom
5. Enter the formula
6. Click OK

For more on Excel’s features, see New Horizons' Microsoft Excel training courses.