Overview
Data validation is a crucial but underused Excel feature that can save you some serious headaches. The more you controls you add to ensure quality data as soon as it’s captured, the better your formulas and other processes work downstream, and the better your decisions will be.
There are several built-in data validation types in Excel – number ranges, date ranges, time ranges, text lengths, and lists – but the real power is in custom validations that are formula-driven. These give you the power to be uber-specific what represents valid data you’re looking to capture. They are especially useful with conditional validations, where the validation logic can change based on the values of other cells.
In this article, we’ll explore 12 common examples of custom validations you can use to learn from or apply in your routine work. Make sure to download the example workbook to see some these examples you can copy from and use.
How to apply custom validation
In the “Allow” list, select Custom, which will expose the “Formula” input. Enter or paste your formula in the formula box, similar to the screenshot below.
Optionally, you can add a prompt that will be displayed to users when the select the cell that has the validation applied. Click the Input Message tab at the top, the give the prompt a title and description, similar to the screenshot below.
You can also optionally display a custom error message when the user tries to enter an invalid value. Click the Error Alert tab at the top, the give the error message a title and description, similar to the screenshot below.
The Examples
Alphanumeric codes or IDs are somewhat common in Excel. Thankfully, it’s easy to validate them. The example below requires one letter and then 6 numbers:
=AND(NOT(ISNUMBER(VALUE(LEFT(A5,1)))),ISNUMBER(VALUE(RIGHT(A5,6))),LEN(A5)=7)
#2 – Contains 2 letters and 3 numbers
Perhaps you don’t need the letters or numbers in a specific order, just a certain number of them. The example below requires 2 letters and 3 numbers, but not in any specific order:
=AND(
SUMPRODUCT(--ISNUMBER(FIND(MID(A5,ROW(INDIRECT("1:"&LEN(A5))),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz")))=2,
SUMPRODUCT(--ISNUMBER(FIND(MID(A5,ROW(INDIRECT("1:"&LEN(A5))),1),"0123456789")))=3
)
#3 – Starts with “INV-” followed by 4 numbers
Entering items like invoice numbers can be tricky – it’s so easy to forget the prefix or mistype it. The example below requires the exact prefix “INV-” and then four numbers:
=AND(LEFT(A5,4)="INV-",ISNUMBER(VALUE(RIGHT(A5,4))),LEN(A5)=8)
#4 – Date that is a Tuesday
Dates are entered all the time in Excel. I often use drop-down lists for them, but you can also use custom formulas like this one, that evaluates whether the date is a Tuesday:
=AND(A5<>"",WEEKDAY(A5,2)=2)
#5 – Date that is a weekday
Perhaps you need a little more flexibility with dates. This one evaluates whether the date is a weekday:
=AND(A5<>"", WEEKDAY(A5,2)<=5)
#6 – Current year date
While you can put in date ranges with hard-coded dates or even reference cell values, sometimes you just want a set-it-and-forget-it approach. This one evaluates whether the date is within the current calendar year (i.e. 1/1/2026 to 12/31/2026):
=AND(A5<>"",A5>=DATE(YEAR(TODAY()),1,1), A5<=DATE(YEAR(TODAY()),12,31))
#7 – Date after a separate field
Often when capturing dates, you’re capturing multiple of them per record, such as a start and an end date. This one evaluates whether a date is greater than a related date:
=AND(A5<>"",A5>A12)
#8 – Percentages between -100% and 100%
Capturing percentages is another good use case, though you could also do this with the regular decimal validation type. This one evaluates whether the value is between -100% and 100% (or -1 to 1):
=AND(A5<>"",A5>=-1,A5<=1)
#9 – Unique values
Duplicates often cause havoc with your data. If you need to capture values that are unique and don’t already exist, use something similar to this one:
=AND(A5<>"",COUNTIF($A$7:$A$26,A5)=0)
#10 – U.S. Zip Code (5 digits)
Zip codes are a great field for validating addresses, filtering locations, etc. This one evaluates whether the value contains 5 numeric characters:
=AND(LEN(A5)=5, ISNUMBER(--A5))
#11 – Valid email address
Email addresses are also often an important field to capture. This one evaluates whether the value is a valid email address:
=LET(
email_address,A5,
pattern,"^[A-Za-z0-9._%+\-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$",
reg_match,REGEXEXTRACT(email_address,pattern,0),
NOT(ISERROR(reg_match))
)
#12 – Number greater than a separate field
Often when capturing numbers, you might want to conditionally compare values, perhaps such as an order limited to the stock available. This one evaluates whether a value is greater than a related value:
=AND(A5<>"",ISNUMBER(A5),A5>0,A5>=A12)
Video
Summary
If you encounter errors in your data, data validation is a great tool to help prevent them – and provide some guidance to your users. Custom validations give you the power to use formulas to apply really specific validations that you couldn’t otherwise use. Use these examples an and AI to keep your data clean and reliable.
What custom data validations have you found helpful? Let us know with a comment below!
Recent Comments