Custom Data Validation in Excel : formulas and rules

This tutorial demonstrates how to create custom data validation rules in Excel. We'll explore several examples, including formulas to restrict input to numbers, text, text starting with specific characters, unique entries, and more.

Yesterday's tutorial introduced Excel Data Validation's purpose and basic built-in rules. Today, we delve into advanced custom validation using formulas.

Key Topics:

• Creating custom formula-based validation rules
• Number-only validation
• Text-only validation
• Validation for text starting with specific characters
• Validation for entries containing specific text
• Preventing duplicate entries
• Date and time validation formulas
• Troubleshooting custom validation issues

Creating Custom Data Validation with Formulas

While Excel offers built-in rules, custom formulas provide greater flexibility. Here's how to create them:

1. Select the cells needing validation.
2. Access the Data Validation dialog (Data tab > Data Tools group > Data Validation, or Alt D L).
3. Choose "Custom" from the "Allow" dropdown in the "Settings" tab.
4. Enter your validation formula in the "Formula" box.
5. Click "OK".

You can also add custom input messages and error alerts. Remember: Data validation only checks new data entered after the rule is created. To identify pre-existing invalid data, use the "Circle Invalid Data" feature.

Examples of Custom Validation Formulas:

Number-Only Validation:

Use =ISNUMBER(C2) (where C2 is the first cell in your range). This accepts integers, decimals, dates, and times (which Excel stores as numbers).

Text-Only Validation:

Use =ISTEXT(D2) (where D2 is the first cell).

Text Starting with Specific Characters:

Use COUNTIF with wildcards. For example, =COUNTIF(A2,"AA-*") allows entries starting with "AA-". For multiple prefixes (e.g., "AA-" or "BB-"), use =COUNTIF(A2,"AA-*") COUNTIF(A2,"BB-*"). For case-sensitive validation, use =EXACT(LEFT(A2,3),"AA-").

Entries Containing Specific Text:

Use ISNUMBER(SEARCH("AA",A2)) (case-insensitive) or ISNUMBER(FIND("AA",A2)) (case-sensitive) to allow entries containing "AA".

Unique Entries (No Duplicates):

Use =COUNTIF($A$2:$A$6,A2) (case-insensitive). Note the use of absolute and relative references.

Date and Time Validation:

Use built-in date rules or custom formulas like =AND(C2>=DATE(2017,7,1),C2 for dates within a specific range. For weekdays/weekends, use <code>WEEKDAY(C2,2) (weekdays) or <code>WEEKDAY(C2,2)>5 (weekends). Use TODAY() and NOW() for validation relative to the current date and time.

Troubleshooting:

If your validation isn't working, check:

• Formula correctness: Test the formula separately.
• Blank cells: Ensure your formula handles blank cells correctly.
• Cell references: Use absolute ($A$1) or relative (A1) references appropriately.

Download the practice workbook for further exploration.

The above is the detailed content of Custom Data Validation in Excel : formulas and rules. For more information, please follow other related articles on the PHP Chinese website!