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**).

**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:

`=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.

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