Complex Custom Data Validation in Excel

Suitable for users Excel 2019, 2021 and Excel for Microsoft 365. 

Objective

Create a custom data validation rule to control the data input into a cell or cells based on specific criteria. 

Custom data validation explained

One of our main goals when working with spreadsheets is to keep them as error-free as possible. Data Validation can help with this. Using data validation, we can control the data that is input into a cell or cells. We can even provide helpful information to our users to assist them with data input and let them know what needs to go into the cell. 

For example, a finance team might share a worksheet and input invoice details into the log. The invoice number has a very specific format: 3 letters (INV), then 4 numbers (8896). We might want to apply some custom data validation to the invoice number column to prevent anyone in the team entering in an invalid invoice number. 

There are many different types of data validation available in Excel but if we need to get very specific about what should and shouldn’t be entered into a cell, we need to set up custom data validation. 

A big shoutout to Leila Gharani from XELPlus.com for this technique. The link to her video is here. 

Creating Custom data validation

There are many different ways that we can create custom data validation, in our example, we are going to use a formula to ensure that only an invoice number that starts with 3 letters and ends with 4 numbers can be input into the cell. 

This is a reasonably lengthy and complex method, so we are going to break it down step by step. 

Data Validation

Let’s first take a look at Custom Data Validation. 

• From the Data tab, in the Data Tools group, click Data Validation. 
• On the Settings tab, click the drop-down under Allow. 
• Select Custom from the menu.

Notice that we have a Formula field. We could type our formula directly into this field. However, there are disadvantages to this. Firstly, when typing a formula into data validation we don’t get to see the arguments. This makes constructing a complex formula difficult. 

Instead, it’s much easier to construct the formula in the worksheet and then copy and paste it into the Formula field in data validation. 

• Click Cancel. 

The formula that we construct needs to do a number of checks on the value that has been input into the cell to make sure it meets the criteria of 3 letters and 4 numbers. These are essentially logical tests that will result in a TRUE or FALSE result, i.e., are the first three chars, letters? TRUE or FALSE. Are the last 4 characters, numbers? TRUE or FALSE. 

We need to construct our formulas so that each output produces a TRUE or FALSE result. Only when the invoice number meets all criteria, will all formulas be TRUE which indicates the invoice number is correct.  

We are going to perform the following checks on the invoice number:

• The length of the invoice number is equal to 7. 
• The first 3 chars are letters. 
• The last 4 chars are numbers. 

Checking the length of the invoice number

All invoice numbers must be 7 characters in length. So, our first check should be that the length of the invoice number entered into the cell is equal to 7. We can use the LEN function. 

• Type =LEN(A9)
• Press Enter. 

This will tell us the length of the invoice number in cell A9.  

As expected, the result is 7. However, we need this to be a TRUE or FALSE result. How do we make this logical? Well, we can test if the length is equal to 7. 

• Press F2 to edit the formula. 
• Type =LEN(A9)=7

We can test this formula by adding another number to the invoice number. If the formula is correct, it will change to FALSE as it’s no longer 7 characters long. 

Extracting and Checking the first 3 chars are letters

Next, we will extract the first 3 characters of the invoice number and check if they are letters. 

• Type =LEFT(A9,3)

This will extract the first 3 chars from the left of cell A9. Predictably, the result is ‘INV’. However, we need this to be logical and produce a TRUE or FALSE result. 

• Press F2 to edit the formula. 
• Type =ISNUMBER(LEFT(A9,3))

The ISNUMBER function will check if the value is a number and provide a TRUE or FALSE result. 

This is where it has the potential to get confusing. We have a FALSE result. However, the invoice number currently in cell A9 is correct. We are getting a FALSE result because Excel thinks that the invoice number is text not a number. 

We need to edit the formula again and convert it to a value and specify that it is not a number.  

• Press F2 to edit the formula. 
• Type =NOT(ISNUMBER(VALUE(LEFT(A9,3))))

The VALUE function changes text to a value and the NOT function tells Excel to produce the opposite result. 

Extracting and Checking the last 4 chars are numbers

Next, we will extract the last 4 chars of the invoice number and check if they are numbers. This formula is similar to previous formula but we don’t need to add the NOT function. 

• Type =ISNUMBER(VALUE(RIGHT(A9,4)))

All answers to each logical test are TRUE when the invoice number has been input correctly. We can test this by changing the invoice number. 

Testing the logic

If we change the length of the invoice number, the first result is now FALSE so the number is invalid. 

If we change the first 3 characters to numbers, the second result is FALSE. 

If we change the last 4 numbers to letters, the third result is FALSE. 

These formulas are the basis for our data validation formula. 

Combining the formulas with AND

We now have three separate formulas that we need to combine together. As we need this to be a logical formula and each one needs to be TRUE for the invoice number to be valid, we will use the AND function. 

To make life easier, copy all formulas to the clipboard. 

• From the Home tab, in the Clipboard group, click the diagonal arrow in the corner. 
• Select the first cell that contains the LEN formula. 
• Press F2 to edit the cell. 
• Highlight the formula but not the equal’s sign. 
• Press CTRL+C to copy to the clipboard. 
• Repeat this process and copy all formulas to the clipboard. 

• In a blank cell on the worksheet type =AND(
• Paste each formula from the clipboard, separating with a comma. 
• Add an additional parenthesis on the end to close off the AND formula. 

Now, we can copy this formula into Data Validation. 

Copying the formula into custom data validation

• Click on the cell that contains the formula and press CTRL+C to copy it. 
• Click on the invoice number cell. 
• From the Data tab, in the Data Tools group, click Data Validation. 
• On the Settings tab, click the drop-down arrow next to Allow. 
• Select Custom from the list. 
• Click in the Formula field and press CTRL+V to paste the formula. 

• Click OK. 

We can now delete all of the logical formulas we created. 

Testing the Custom validation

Test the custom data validation by making a mistake in the invoice number. If it is working correctly, we should see an error message. 

We can customize the error message users see if they enter in an invalid invoice number. 

• From the Data tab, in the Data Tools group, click Data Validation. 
• Go to the Error Alert tab. 
• Choose a Style. 
• Enter a Title and an Error message. 

Copying custom validation to other cells

Chances are, we are going to be inputting lots of invoice numbers so we want the custom validation to apply to all cells in the column. We can copy the validation and use paste special. 

• Select the cell that contains the validation. 
• Press CTRL+C. 
• Highlight the cells to copy the validation to. 
• Right-click and select Paste Special. 
• Choose Validation. 

• Click OK.