How to Apply Data Validation with Two Conditions in Excel?

• This blog article explains how to apply data validation with two conditions in Excel using formulas.
• Data validation is a feature in Excel that allows you to control what kind of data can be entered in a cell or a range of cells.
• You can use the IF, AND, OR, and COUNTIF functions to create custom data validation rules for multiple criteria in one cell or in a range of cells.
• You can also customize the error messages, display a drop-down list, and remove the data validation.

A step-by-step guide to creating custom data validation rules for multiple criteria in Excel.

Data validation is a feature in Excel that allows you to control what kind of data can be entered in a cell or a range of cells. You can use data validation to restrict the input to a specific type, such as numbers, dates, text, or a list of predefined values. You can also use data validation to prevent invalid or duplicate entries, or to display an error message or a drop-down list when a cell is selected.

One of the advantages of data validation is that you can create custom rules using formulas to check for multiple criteria. For example, you can use data validation to ensure that a cell value is between two numbers, or that it matches one of the values in another range, or that it is unique in a column. In this tutorial, we will show you how to apply data validation with two conditions in Excel using different examples and scenarios.

Example 1: Apply data validation for multiple criteria in one cell

In this example, we have a worksheet that contains a list of products and their prices. We want to apply data validation to cell B5, so that the user can enter either a product name from the list in column D, or a date between February 1, 2022 and March 1, 2022. To do this, we will use the IF, OR, and COUNTIF functions to create a custom data validation formula.

The IF function returns one value if a condition is true, and another value if it is false. The OR function returns TRUE if any of the arguments are true, and FALSE if all of them are false. The COUNTIF function counts the number of cells in a range that meet a given criterion.

The formula we will use is:

=OR(COUNTIF($D$5:$D$10,B5)=1,AND(B5>=E5,B5<=E6))

This formula checks if the value in B5 is either one of the products in the range D5:D10, or a date between E5 (1-Feb-2022) and E6 (1-Mar-2022). If either condition is true, the formula returns TRUE, and the data validation is successful. If neither condition is true, the formula returns FALSE, and the data validation fails.

To apply this formula as a data validation rule, follow these steps:

• Select the cell where you want to apply the data validation (B5 in this case).
• Go to the Data tab and click on Data Validation in the Data Tools group.
• In the Data Validation dialog box, under the Settings tab, select Custom in the Allow field.
• In the Formula field, enter the formula: =OR(COUNTIF($D$5:$D$10,B5)=1,AND(B5>=E5,B5<=E6))
• Click OK to confirm the data validation rule.

Now, if you try to enter a value in B5 that is not a product name from the list, or a date between the specified dates, you will get an error message like this:

You can customize the error message by going to the Error Alert tab in the Data Validation dialog box and changing the Title and the Error message fields.

Example 2: Apply data validation for multiple criteria in a range of cells

In this example, we have a worksheet that contains a list of orders and their details. We want to apply data validation to the range B5:B10, so that the user can enter only numbers between 1 and 20, or between 100,000 and 399,999. To do this, we will use the IF, AND, and OR functions to create a custom data validation formula.

The formula we will use is:

=OR(AND(B5>=1,B5<=20),AND(B5>=100000,B5<=399999))

This formula checks if the value in B5 is either between 1 and 20, or between 100,000 and 399,999. If either condition is true, the formula returns TRUE, and the data validation is successful. If neither condition is true, the formula returns FALSE, and the data validation fails.

To apply this formula as a data validation rule, follow these steps:

1. Select the range of cells where you want to apply the data validation (B5:B10 in this case).
2. Go to the Data tab and click on Data Validation in the Data Tools group.
3. In the Data Validation dialog box, under the Settings tab, select Custom in the Allow field.
4. In the Formula field, enter the formula: =OR(AND(B5>=1,B5<=20),AND(B5>=100000,B5<=399999))
5. Click OK to confirm the data validation rule.

Now, if you try to enter a value in B5:B10 that is not between the specified numbers, you will get an error message like this:

You can customize the error message by going to the Error Alert tab in the Data Validation dialog box and changing the Title and the Error message fields.

Frequently Asked Questions (FAQs)

Question: What is data validation in Excel?

Answer: Data validation is a feature in Excel that allows you to control what kind of data can be entered in a cell or a range of cells. You can use data validation to restrict the input to a specific type, such as numbers, dates, text, or a list of predefined values. You can also use data validation to prevent invalid or duplicate entries, or to display an error message or a drop-down list when a cell is selected.

Question: How do I create a custom data validation rule in Excel?

Answer: To create a custom data validation rule in Excel, follow these steps:

1. Select the cell or the range of cells where you want to apply the data validation.
2. Go to the Data tab and click on Data Validation in the Data Tools group.
3. In the Data Validation dialog box, under the Settings tab, select Custom in the Allow field.
4. In the Formula field, enter a formula that returns TRUE or FALSE based on the criteria you want to check.
5. Click OK to confirm the data validation rule.

Question: How do I remove data validation in Excel?

Answer: To remove data validation in Excel, follow these steps:

1. Select the cell or the range of cells where you want to remove the data validation.
2. Go to the Data tab and click on Data Validation in the Data Tools group.
3. In the Data Validation dialog box, under the Settings tab, click on the Clear All button.
4. Click OK to remove the data validation.

Summary

In this tutorial, we learned how to apply data validation with two conditions in Excel using the IF, AND, OR, and COUNTIF functions. We saw how to create custom data validation rules for multiple criteria in one cell or in a range of cells. We also learned how to customize the error messages and how to remove the data validation.

Data validation is a useful feature in Excel that can help you ensure the accuracy and consistency of your data. By using formulas, you can create complex and flexible data validation rules that suit your specific needs.