Custom Validation with Formulas: Data Validation in Advanced Excel

In this tutorial, we will learn how to apply custom data validation in Excel using formulas. Data validation is essential for ensuring that the data entered into a worksheet meets certain criteria. Advanced Excel allows us to use formulas in data validation to create more flexible and powerful rules.

What is Data Validation?

Data Validation in Excel is a feature that allows you to set rules to control the type of data entered into a cell. You can restrict entries, show error messages, and even use formulas to create custom validation rules.

Steps to Apply Custom Data Validation with Formulas

  1. Select the cell or range where you want to apply data validation.
  2. Go to the Data tab on the Ribbon.
  3. Click on the Data Validation button in the 'Data Tools' group.
  4. In the Data Validation dialog box, click the Settings tab.
  5. Under Allow, select Custom from the dropdown list.
  6. In the Formula field, enter a formula that defines the validation rule.
  7. Click OK to apply the validation.

Real-Time Examples of Custom Validation with Formulas

Example 1: Allow Only Values Greater Than 100

We can use a formula to allow only values greater than 100 in a selected cell.

  1. First, select the cell (e.g., A1) where you want to apply the validation.
  2. Go to Data > Data Validation.
  3. In the Data Validation dialog box, select Custom.
  4. In the formula field, enter the following formula: 
    =A1>100
  5. Click OK.

Now, only numbers greater than 100 can be entered in cell A1. If a value less than or equal to 100 is entered, an error message will appear.

Example 2: Restrict Entry to Only Even Numbers

To allow only even numbers in a cell, use the following formula:

  1. Select the cell (e.g., A2) where you want to apply the validation.
  2. Go to Data > Data Validation.
  3. In the Data Validation dialog box, select Custom.
  4. In the formula field, enter the following formula: 
    =MOD(A2,2)=0
  5. Click OK.

This formula uses the MOD function to check if the number is divisible by 2 without a remainder. If it is, the number is even and allowed.

Example 3: Restrict Date Entry to Only Weekdays

To restrict date entries to weekdays (Monday to Friday), use the following formula:

  1. Select the cell (e.g., B1) where you want to apply the validation.
  2. Go to Data > Data Validation.
  3. In the Data Validation dialog box, select Custom.
  4. In the formula field, enter the following formula: 
    =WEEKDAY(B1,2)<6
  5. Click OK.

This formula uses the WEEKDAY function, with the 2 argument to make Monday = 1 and Sunday = 7. If the date entered corresponds to a weekend (Saturday or Sunday), an error message will appear.

Example 4: Allow Only Text With a Minimum Length of 5 Characters

To ensure that the text entered in a cell has at least 5 characters, use the following formula:

  1. Select the cell (e.g., C3) where you want to apply the validation.
  2. Go to Data > Data Validation.
  3. In the Data Validation dialog box, select Custom.
  4. In the formula field, enter the following formula: 
    =LEN(C3)>=5
  5. Click OK.

This formula uses the LEN function to check the length of the text entered in cell C3. If the text is shorter than 5 characters, an error message will appear.

Conclusion

Custom data validation with formulas in Excel allows you to create more complex rules for data entry, ensuring that your data remains accurate and consistent. By using formulas such as MOD, WEEKDAY, LEN, and logical operators, you can customize validation to fit various business and data requirements. Experiment with these formulas to gain full control over your Excel data entry process.




Advertisement