# 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:
`=AND(E8>\$B\$5,COUNTIF(\$B\$8:\$B\$10,E8)=1)`
• 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).

### 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:
`=OR(E8=\$B\$5,AND(E8>=DATE(2022,03,1),E8<=DATE(2022,03,31)))`
• 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.
`=OR(AND(LEFT(C9,4)="ISBN",LEN(C9)=8),AND(LEFT(C9,4)="CODE",LEN(C9)=10))`
• 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.

## 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

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

Get FREE Advanced Excel Exercises with Solutions!
Mukesh Dipto

Mukesh Dipto is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, HTML, CSS, JavaScript, and WordPress. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF