How to Apply Multiple Data Validation in One Cell in Excel (3 Examples)

In this tutorial, we will demonstrate how to apply multiple data validation in one cell in Excel. If you work regularly on Microsoft Excel it may sound very common to you to apply data validation in an Excel cell. But, what if you need to apply multiple data validation in one cell? So, in this article, to answer this question we will show different examples of applying multiple data validation in one cell in Excel.

3 Examples to Apply Multiple Data Validation in One Cell in Excel

There is no limit to types of applying multiple data validation in one cell. Because we can combine any two or more conditions with data validation. So, to illustrate the process of applying multiple data validation we will demonstrate 3 different examples in this article.

1. Apply Multiple Data Validation in One Cell in Excel with Specific Criteria

In this method, we will set two specific criteria to apply multiple data validation in one cell in Excel. We have the following screenshot of a dataset with two criteria. We will configure a data validation in cell E8 with the two given criteria. The validation will work like the value that we will insert in cell E8 has to be greater than 50 or has to be in the range (B8:B10). Any value other than these two conditions will show a warning that the value we want to enter doesn’t match the data validation.

From the following dataset, we can see the formulas in cells C5 & C9 to meet the criteria that we will use for validation.

So, let’s take a look at the steps to perform this action.

STEPS:

• Firstly, combine the criteria formulas in a nested formula in cell C13. Copy the nested formula from the formula bar but do not paste it anywhere.

• Secondly, select cell E8.
• Thirdly, go to Data > Data Tools > Data Validation > Data Validation.

• The above action will open a new dialogue box named Data Validation.
• Next, select the option Custom from the Allow drop-down menu.

• Then, paste the validation formula in the Formula text box:

• Click on OK.

Here, the COUNTIF function checks if the input value matches with the range values (B8:B10). The AND function combines the two criteria.

• After that, input value 40 in cell E8.

• So, Excel will give us a warning prompt box for the above value as it doesn’t match our ‘Criteria 1’. The value in cell E8 must be greater than 50.

• Lastly, enter the text ‘Apple’ in cell E8. We can see that Excel accepts this value. This value meets the second criterion as it is in the range (B8:B10).

Read More: Apply Custom Data Validation for Multiple Criteria in Excel

2. Allow a Text and a Date between Two Dates by Applying Multiple Data Validation in One Cell

In this example, we will set two criteria for data validation. The criteria will only allow a specific text and a date between two dates in a cell. In the following dataset, we have word Excel in ‘Criteria 1’ and two dates ‘1-03-22’ & ‘31-03-22’ in ‘Criteria 2’. We can see the formulas that give the conditions to meet the above criteria. We will use these formulas to apply multiple data validation in cell E8.

Let’s see how we can do this example.

STEPS:

• First, merge the criteria formulas in a nested formula with the AND function in cell C12.
• Next, copy the nested formula from the formula bar but don’t paste it anywhere.

• Then, open the data validation window.
• Select the option Custom from the Allow drop-down. Insert the validation formula in the Formula text box:

• Click on OK.

Here, the AND function checks if the input date is between ‘1-03-22’ & ‘31-03-22’ or not. The OR function combines the two criteria and gives output if anyone is true.

• After that, enter the text Excel in cell E8. We can see that Excel doesn’t show any warning prompt box. Because it matches the ‘Criteria 1’ of our data validation formula.

• Again, if we input the text Word in cell E8, it will show a warning prompt box. It’s happening because the text value Word doesn’t match any criteria.

• Now, enter a date in cell E8 which from the date range ‘1-03-22’ to ‘31-03-22’. We can see Excel accepts the value as it matches with ‘Criteria 2’.

• Finally, write a date in cell E8 outside the date range of the validation formula. Excel will give us a warning prompt box that the given value doesn’t match any criteria of our validation formula.

Read More: How to Copy Data Validation in Excel

3. Use Multiple Data Validation in One Cell in Excel to Fix Starting Character and Length of a String

In the third example, we will use multiple data validation in one cell to start characters and the length of a string that we will input. We have the following two criteria in the given dataset. We will apply multiple data validation in cell C8. The data validation that we will apply in cell C9 will allow us to input data only that starts with the text ‘ISBN’ and ‘CODE’. The text that starts with text must be 8 characters and the text that starts with ‘CODE’ must be 10 characters. Otherwise, the validation formula will return a warning prompt box.

Let’s see the steps to do this example.

STEPS:

• In the beginning, create the data validation formula in cell D5 that covers all the criteria.

• Next, open the Data Validation dialogue box like the example-1. Select the option Custom from the Allow drop-down menu.
• Then, insert the data validation formula in the Formula text box.

• Click on OK.

Here, the LEFT function extracts 4 characters from the string. The LEN function calculates the length of the string. The OR function combines the two criteria and gives output if anyone is true.

• After that, insert the value ‘ISBN-358’. The value starts with characters ‘ISBN’ and the length of the string is 8. We can see that Excel accepts the value as it matches both criteria.

• In the end, we will add two more characters with the previous input value. Excel shows a warning prompt box though it starts with the text ‘ISBN’ which matches the first criteria. But the text contains 10 characters that don’t match the second criteria.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

In conclusion, this tutorial will give you a brief idea to apply multiple data validation in one cell. To put your skills to the test, utilize the sample worksheet that comes with this article. Please leave a comment below if you have any questions. Our team will try to reply to you as soon as possible. In the future, keep an eye out for more innovative Microsoft Excel solutions.

Related Articles

• How to Remove Blanks from Data Validation List in Excel
• How to Use Custom VLOOKUP Formula in Excel Data Validation
• How to Use IF Statement in Data Validation Formula in Excel
• Data Validation Based on Another Cell in Excel
• How to Remove Data Validation Restrictions in Excel

<< Go Back to Data Validation in Excel | Learn Excel