Using Custom Formulas in Data Validation in Microsoft Excel

Course: Advanced Excel

Topic: Using Custom Formulas in Data Validation

Course Link: Advanced Excel Course

Demo Link: Register for a Free Course Demo

Website: JustAcademy

1. Introduction to Custom Formulas in Data Validation

Data Validation in Microsoft Excel helps control what users can enter into a cell. Excel provides built-in validation options such as whole numbers, decimal values, dates, times, text length, and predefined lists. However, real-world spreadsheets often require more advanced rules that cannot be handled easily by these standard options.

Custom Formula Data Validation allows users to create their own conditions using Excel formulas. A formula determines whether an entered value is acceptable. If the formula evaluates to TRUE, Excel accepts the entry; if it evaluates to FALSE, Excel can prevent the entry and display an error message.

This feature is useful for enforcing business rules, preventing duplicate entries, validating text patterns, checking relationships between cells, and ensuring that data follows a consistent structure.

2. Learning Objectives

• Understand how custom formulas work in Data Validation.
• Apply formulas to restrict the type of data entered into cells.
• Prevent duplicate values in a column.
• Validate text, numbers, dates, and related cell values.
• Use relative and absolute cell references correctly.
• Create error alerts and input messages.
• Troubleshoot common formula-validation problems.

3. What Is Data Validation?

Data Validation is an Excel feature that controls the values users are allowed to enter into selected cells. It can reduce data-entry errors and improve the reliability of reports, dashboards, and analytical datasets.

4. What Is Custom Formula Data Validation?

Custom Formula Data Validation uses a logical formula to determine whether a cell entry is valid. The formula must return TRUE or FALSE. Excel evaluates the formula when a user enters or changes a value in a validated cell.

For example, suppose cell A2 should contain a positive number. The following formula can be used:

=A2>0

If the entered value is greater than zero, the formula returns TRUE and the entry is accepted. If the value is zero or negative, the formula returns FALSE and Excel rejects the entry when a Stop-style error alert is enabled.

5. How to Apply a Custom Formula in Data Validation

1. Select the cell or range where you want to apply the validation rule.
2. Open the Data tab on the Excel ribbon.
3. Click Data Validation in the Data Tools group.
4. In the Settings tab, open the Allow dropdown.
5. Select Custom.
6. Enter the required formula in the Formula field.
7. Open the Input Message tab if you want to display instructions when a cell is selected.
8. Open the Error Alert tab and enable the alert if invalid entries should be blocked.
9. Choose an error style, such as Stop, Warning, or Information.
10. Click OK and test the rule using valid and invalid values.

Important: When applying a formula to a range, write the formula relative to the first cell in the selected range. Excel adjusts relative references for the remaining cells.

6. Understanding Relative and Absolute References

Correct cell references are essential when creating custom validation formulas. An incorrect reference may cause Excel to validate every row against the same cell instead of checking each row individually.

For example, if you select B2:B100 and use the formula =B2>0, Excel checks B2 for the first cell, B3 for the next, and so on. If you instead use =$B$2>0, every cell is evaluated against B2.

7. Example 1: Allow Only Positive Numbers

Scenario: A worksheet records quantities sold. Each quantity must be greater than zero.

Assume quantities are entered in cells B2:B100.

Formula:

=B2>0

Steps:

1. Select B2:B100.
2. Open Data Validation and select Custom.
3. Enter =B2>0.
4. Set an error alert explaining that the quantity must be greater than zero.
5. Test the rule with 5, 0, and -3.

8. Example 2: Prevent Duplicate Values

Scenario: An employee register contains employee IDs in column A. Each ID must be unique.

Select A2:A100 and enter this custom formula:

=COUNTIF($A$2:$A$100,A2)=1

How it works:

• COUNTIF($A$2:$A$100,A2) counts how many times the value in A2 appears in the specified range.
• =1 checks whether that value appears exactly once.
• If the value is already present elsewhere in the range, the formula returns FALSE for the duplicate entry.

Note: This rule is most effective for controlling new entries. Existing duplicates may remain in the worksheet, and pasting or filling data into validated cells can bypass normal validation prompts. Audit existing data separately when uniqueness is essential.

9. Example 3: Allow Only Text Values

Scenario: A column should contain text entries rather than numeric-only values.

For a range beginning at A2, one possible formula is:

=ISTEXT(A2)

This formula returns TRUE when the cell contains text and FALSE when it contains a number or is blank. If blank entries should also be allowed, use:

=OR(A2="",ISTEXT(A2))

Use case: This can be useful for name, department, or category fields. Keep in mind that numeric-looking identifiers, such as employee codes, may be stored as text intentionally.

10. Example 4: Require a Specific Text Length

Scenario: Each product code must contain exactly six characters.

Select the required range beginning at A2 and use:

=LEN(A2)=6

The LEN function counts the characters in a cell, including spaces. Therefore, a value such as PRD123 has six characters, while PRD12 has five.

If leading or trailing spaces should not count, use:

=LEN(TRIM(A2))=6

TRIM removes leading and trailing ordinary spaces and reduces repeated internal spaces to a single space. It does not remove every possible special whitespace character.

11. Example 5: Validate an Email-Like Entry

Scenario: A worksheet collects email addresses and should reject entries that obviously lack an at sign or a dot after it.

For a basic check, use:

=AND(ISNUMBER(SEARCH("@",A2)),ISNUMBER(SEARCH(".",A2,SEARCH("@",A2)+2)))

This formula checks for an at sign and a dot at least two positions after it. It is a basic screening rule, not a complete email-address validator. It may accept invalid addresses and reject some legitimate formats, so it should not replace verification or a dedicated validation process when accuracy is important.

12. Example 6: Restrict Dates to a Specific Period

Scenario: Project dates must fall between 1 January 2026 and 31 December 2026.

If users enter dates in A2:A100, select the range and use:

=AND(A2>=DATE(2026,1,1),A2<=DATE(2026,12,31))

The DATE function creates valid Excel date values. The AND function returns TRUE only when both date conditions are satisfied.

To prevent blank entries as well, use:

=AND(A2<>"",A2>=DATE(2026,1,1),A2<=DATE(2026,12,31))

This formula assumes the entry is recognized by Excel as a date or date serial value. Apply a suitable date number format to make the values easier to read.

13. Example 7: Validate One Cell Based on Another Cell

Scenario: A discount percentage in B2 must be between 0% and 20%, and the discount should be entered only when the product status in A2 is Active.

Select B2:B100 and use the following formula if the status for each row is in column A:

=AND(B2>=0,B2<=20%,A2="Active")

The formula checks three conditions: the discount is not negative, it does not exceed 20%, and the corresponding status is Active.

If the discount column is formatted as Percentage, enter values such as 5% or 15%. In Excel, 20% is stored as 0.2, so the formula compares against the correct numeric value.

14. Example 8: Require a Value Only When Another Cell Is Filled

Scenario: If an order number is entered in A2, the order date in B2 must also be supplied. If A2 is blank, B2 may remain blank.

Select B2:B100 and use:

=OR(A2="",B2<>"")

The rule returns TRUE when A2 is blank or B2 contains a value. It returns FALSE when A2 contains an order number but B2 is blank.

For a more specific requirement, such as allowing only a valid date in B2 when A2 is filled, combine the required conditions with AND and OR functions. Ensure that the formula is written relative to the first selected row.

15. Example 9: Allow Only Values from a Reference Range

Scenario: A user should enter a department name that exists in a reference list in D2:D10.

Select A2:A100 and use:

=COUNTIF($D$2:$D$10,A2)>0

The formula checks whether the entered value appears at least once in the department list. It is useful when you want formula-based validation instead of using the built-in List option.

For straightforward dropdown selection, the built-in List validation is generally more user-friendly because it presents the available choices directly.

16. Example 10: Allow Only Values Greater Than a Related Cell

Scenario: The selling price in B2 must be greater than the cost price in A2.

Select B2:B100 and use:

=B2>A2

This rule compares the selling price with the cost price in the same row. If the selling price is not greater than the cost price, the entry is rejected when the Stop error style is configured.

17. Common Excel Functions Used in Custom Validation

18. Creating an Input Message

An input message provides instructions when a user selects a validated cell. It can explain the expected format before the user starts entering data.

1. Select the validated cells.
2. Open Data Validation.
3. Choose the Input Message tab.
4. Enable the option to show an input message when the cell is selected.
5. Enter a short title and a clear instruction.
6. Click OK.

Example input message: Title: Product Code. Message: Enter a unique six-character product code without extra spaces.

19. Creating an Error Alert

Error alerts tell users when an entry does not meet the validation rule. They help explain why the value is not accepted.

For critical data rules, use the Stop style and write a clear message that tells the user what to enter instead.

20. Troubleshooting Common Problems

21. Best Practices for Custom Formula Data Validation

• Start by identifying the exact business rule the data must follow.
• Write the formula using the first cell in the selected range.
• Use absolute references for fixed lookup ranges and relative references for row-by-row checks.
• Test both valid and invalid entries before distributing the workbook.
• Include clear input messages and error alerts where appropriate.
• Decide explicitly whether blank cells are permitted.
• Use simple formulas whenever possible so other users can maintain the workbook.
• Check existing data separately because adding validation does not automatically repair earlier invalid entries.
• Remember that pasted or imported values may bypass validation.
• For important business processes, combine validation with data auditing, worksheet protection, or controlled data-entry workflows.

22. Practical Exercise

Create an Excel worksheet for an employee register with the following columns:

Suggested formulas:

Employee ID (A2:A100):
=AND(A2<>"",COUNTIF($A$2:$A$100,A2)=1)

Employee Name (B2:B100):
=ISTEXT(B2)

Age (C2:C100):
=AND(C2>=18,C2<=60)

Joining Date (D2:D100):
=AND(D2>=DATE(2020,1,1),ISNUMBER(D2))

Department (E2:E100):
=COUNTIF($G$2:$G$8,E2)>0

Apply each formula to the corresponding range. Test valid entries, duplicate IDs, ages outside the permitted range, dates before 2020, and department names that are not present in the reference list. Decide whether blank values should be allowed and adjust each formula accordingly.

23. Interview Questions and Answers

Q1. What is Custom Formula Data Validation in Excel?

It is a Data Validation option that uses a logical formula to determine whether an entry meets a defined condition.

Q2. What should a custom validation formula return?

It should evaluate to TRUE for an acceptable entry and FALSE for an unacceptable entry.

Q3. How can you prevent duplicate entries?

Use a formula such as =COUNTIF($A$2:$A$100,A2)=1 for a range beginning at A2. Also check existing records and imported data for duplicates.

Q4. Why are absolute references important?

Absolute references keep a lookup range or fixed cell unchanged when Excel evaluates the formula for different cells.

Q5. What is the difference between AND and OR?

AND returns TRUE only when every condition is TRUE. OR returns TRUE when at least one condition is TRUE.

Q6. Can custom formulas validate dates?

Yes. Functions such as DATE, AND, and comparison operators can restrict entries to a specified date range.

Q7. Does Data Validation automatically correct invalid existing data?

No. It controls normal future data entry but does not automatically repair existing invalid values. Existing records should be audited separately.

Q8. Can users bypass Data Validation?

Yes. Certain paste, fill, or import operations can bypass validation prompts. Critical datasets should be checked with additional auditing methods.

24. Quick Revision Checklist

• Custom validation formulas evaluate to TRUE or FALSE.
• Select Custom in the Data Validation dialog box to enter a formula.
• Write the formula relative to the first cell in the selected range.
• Use COUNTIF to check for duplicates or confirm membership in a list.
• Use AND and OR to combine multiple conditions.
• Use LEN, ISTEXT, and ISNUMBER to validate text and data types.
• Use DATE to create reliable date boundaries.
• Configure input messages and error alerts to guide users.
• Test formulas with both valid and invalid entries.
• Audit existing data and pasted values separately.

25. Conclusion

Custom Formulas in Data Validation provide a flexible way to control data entry in Microsoft Excel. By combining logical operators and functions such as AND, OR, COUNTIF, LEN, ISTEXT, and DATE, users can enforce rules that go beyond Excel's standard validation options.

These techniques help prevent duplicate records, enforce text and number requirements, validate dates, and maintain relationships between fields. Correct cell references, clear error messages, thorough testing, and separate data auditing are essential for creating reliable and maintainable Excel workbooks.

To learn more about advanced spreadsheet techniques, visit JustAcademy, explore the Advanced Excel Course, or register for a course demo.