Data Validation Techniques for Complex Business Rules in Excel

In this article, we will explore advanced data validation techniques for complex business rules in Excel with practical examples.

Data Validation Techniques for Complex Business Rules in Excel

Data Validation is a powerful and useful feature to maintain the integrity of business data. It is crucial especially when dealing with complex rules to restrict user input. In this article, we will explore advanced data validation techniques for complex business rules in Excel with practical examples.

Let’s consider an employee sales record to implement complex business rules by using Data Validation techniques in Excel.

1. Restrict Input to a Range of Values

You can restrict input values. Let’s ensure that employee performance scores range between 1 and 10.

  • Select the “Performance Score” column.
  • Go to the Data tab >> from Data Tools >> select Data Validation.
  • From the Data Validation dialog box >> select Settings.
    • Set Allow to Whole Number and specify:
    • Minimum: 1
    • Maximum: 10
    • Click OK.

Data Validation Techniques for Complex Business Rules in Excel

Now you won’t be able to enter a score outside the specified range. If you try entering “15”, an error message appears.

Output:

Data Validation Techniques for Complex Business Rules in Excel

2. Use Dropdown Lists for Standardized Inputs

If you want employees to select product categories from a predefined list, you can set the values in the list.

  • Create a list of valid categories in a separate column. We created the category list in M1 and G1 cells.
  • Select the “Category” column.
  • Go to the Data tab >> from Data Tools >> select Data Validation.
  • From the Data Validation dialog box >> select Settings.
    • Set Allow to List and Source to $M$1:$N$1.
    • Click OK.

Data Validation Techniques for Complex Business Rules in Excel Employees cannot enter any text; they must pick a category from the dropdown list.

Output:

Data Validation Techniques for Complex Business Rules in Excel

3. Dependent Dropdown Lists

Let’s create a dependent drop-down list for products based on the category. The product will be visible based on category, this rule helps to maintain data integrity.

  • First, create Named Ranges for the category:
    • Select M1:N6 cells where the product and category are listed.
    • Go to the Formulas tab >> select Create from Selection >> select Top Rows >> click OK.

Data Validation Techniques for Complex Business Rules in Excel

  • In the “Category” column, already used the first dropdown list technique.
  • Select the “Product” column.
  • Go to the Data tab >> from Data Tools >> select Data Validation.
  • From the Data Validation dialog box >> select Settings.
    • Set Allow to List and insert the formula in the Source box:
      =INDIRECT(D2)
      
    • Click OK.

Data Validation Techniques for Complex Business Rules in Excel

Here, D2 contains the category. If “Electronics” is selected in D2, the dropdown for the Product will show only “Smartphone, Laptop, Tablet, Headphones, Television “.

Output:

Data Validation Techniques for Complex Business Rules in Excel

4. Dynamic Drop-Down List

You also can update your validation list automatically whenever new products are added to the database.

  • Select a dynamic range for the validation source:
    • Insert the following formula in the data validation source:
      =OFFSET($M$2,0,0,COUNTA($M$2:$M$100),1))
      

This formula will create a dynamic dropdown list with unique values from the range $M$2:$M$100.

  • $M$2: The starting cell for the range.
  • 0,0: No row or column offset; stays at $M$2.
  • COUNTA($M$2:$M$100): Counts non-blank cells in the range $M$2:$M$100 to determine the number of rows.
  • 1: Specifies the range is 1 column wide.

This adjusts the range dynamically as data is added or removed in $M$2:$M$100. Use it in data validation or named ranges for dynamic dropdowns.

This ensures the dropdown contains a list of distinct, non-blank entries from column M.

5. Use Custom Formula to Validate Dates with Rules

Custom formulas can enforce business-specific rules. Set a rule to ensure that the Delivery Date is within 1 to 7 days after the Order Date

  • Select the “Delivery Date” column.
  • Go to the Data tab >> from Data Tools >> select Data Validation.
  • From the Data Validation dialog box >> select Settings.
    • Set Allow to Custom and insert the following formula in the Formula box:
      =AND(I2>H2, I2<=H2+7)
      
    • I2>H2: It ensures the delivery date is after the order date.
    • I2<=H2+7: It ensures it is no more than 7 days after the order date.
    • Click OK.

Data Validation Techniques for Complex Business Rules in Excel If the order date is 11/04/2024 in I2, the earliest acceptable delivery date in H2 should be any date from 11/05/2024 to 11/11/2024.

Output:

Data Validation Techniques for Complex Business Rules in Excel

If you want to ensure Sales Amounts do not exceed the Target Sales, you can use a custom formula in data validation.

  • Select the Sales Amount column.
  • Go to the Data tab >> from Data Tools >> select Data Validation.
  • From the Data Validation dialog box >> select Settings.
    • Set Allow to Custom and insert the following formula in the Formula box.
    • =F2<=G2
      
    • Click OK.

6. Validate Text Patterns

If you want you can ensure product codes follow the pattern “ABC” followed by three digits (e.g., ABC123).

  • Select the “Product Code” column.
  • Go to the Data tab >> from Data Tools >> select Data Validation.
  • From the Data Validation dialog box >> select Settings.
    • Set Allow to Custom and enter the formula in the Formula box.
    • =AND(LEFT(J2,3)="ABC",ISNUMBER(MID(J2,4,3)*1))
      
    • Click OK.

Data Validation Techniques for Complex Business Rules in Excel

This formula checks if a value in cell J2 meets a certain format and then returns TRUE if both conditions are met.

  • (LEFT(J2,3)=”ABC”): It checks if the cell value starts with “ABC” or not.
  • (ISNUMBER(MID(J2,4,3)*1)): Verifies the cell has a number in the next 3 characters

Entering “DEF123” or “ABC12” will trigger an error, as they do not match the required format.

Output:

Data Validation Techniques for Complex Business Rules in Excel

7. Error Alerts and Input Message

It’s important to guide users along with the data validation rules so that users can insert accurate values based on error messages. Insert clear messages if they violate rules.

Input Message:

  • Go to the Data tab >> from Data Tools >> select Data Validation.
  • From the Data Validation dialog box >> select Input Message.
    • Add a Title and Input message.
    • Insert the title as “Category” and a message, “Select category from the list”.

Data Validation Techniques for Complex Business Rules in Excel

Error Alerts:

  • From the Data Validation dialog box >> select Error Alert.
    • Select Style: Stop.
    • Add a Title and Error Message.
    • Enter the title as “Invalid Entry”, and a message, “Please insert score between 1 and 10”.

Data Validation Techniques for Complex Business Rules in Excel

Tips for Effective Data Validation

  • Named Ranges: Use name manager efficiently to make formulas cleaner and easier to manage.
  • Set Dynamic Rules: If you are using updated Excel versions try to use dynamic functions and formulas to update the data validation automatically.
  • Combine Rules: You can use multiple layers of validation for robust input control.
  • Test Thoroughly: Ensure all scenarios are covered, especially edge cases.

Conclusion

These practical examples show how data validation techniques can handle complex business rules in Excel. You can use dynamic rules, drop-down lists, and custom formulas to enforce data integrity and streamline workflows. These tools not only reduce errors but also enhance the efficiency of your workflows. Implement these techniques in your business to ensure error-free data entry.

Get FREE Advanced Excel Exercises with Solutions!

Shamima Sultana
Shamima Sultana

Shamima Sultana, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the ExcelDemy project for 3+ years. She has written and reviewed 1000+ articles for ExcelDemy. She has also led several teams with Excel VBA and Content Development works. Currently, she is working as the Project Manager and oversees the day-to-day work, leads the services team, allocates resources to the right area, etc. Her work and learning interests vary from Microsoft Office Suites, and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo