Custom Data Validation Rules in Excel

 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

Microsoft Excel

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

How do your Excel skills stack up?   

Test Now  

About the Author:

Magdalena Todor  

With over 20 years experience as a facilitator and university lecturer, Magda is one of our most senior and experienced trainers. With previous practical on the job experience as a project manager she embodies a brilliant balance of training experience and business knowledge. With every event she delivers, Magda takes a wholehearted approach to ensure every course is impactful, relevant and a genuinely positive learning experience for all.

Read full bio