Custom Data Validation in Excel: 12 Formula Rules with Examples

Part of the free Module 6: Data Validation · Lesson 7 of 14 · Full Excel course

Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted.

Custom data validation in Excel lets you write your own rule as a formula. You set Allow to Custom, type a logical formula that returns TRUE or FALSE for the active cell, and Excel accepts an entry only when the result is TRUE. It is the rule type to use when the built-in options (whole number, list, date, text length) cannot express a real business condition.

What custom data validation does and when to use it

The preset rules each test one thing about the cell itself. A custom rule can test anything a worksheet formula can calculate: the same cell, other cells, whole ranges, today’s date or a named value. That makes it the right choice for rules such as no duplicate IDs in a column, an end date that must follow the start date, a code that must begin with a prefix, a column total that may not exceed a budget, or an entry allowed only after the previous row is complete.

Excel evaluates the formula for the active cell and shifts relative references for every other cell in the selection, exactly as conditional formatting does. Understanding that one behaviour is the key to writing rules that work across a range. The feature is identical in Excel 2016, 2019, 2021 and Excel 365, and the formulas below use functions available in all of them.

How the custom formula is evaluated

Three facts decide whether a custom rule behaves the way you expect.

• Only TRUE passes. TRUE (or any non-zero number) accepts the entry. FALSE, zero, text such as “OK” and any error value all reject it.
• The formula is written for the active cell. If you select A2:A100 with A2 active and type =ISNUMBER(A2), cell A50 is tested with =ISNUMBER(A50). Lock references with dollar signs when they must not move.
• The rule runs when the cell is edited. It does not re-check when other cells change, when values are pasted or filled, or when a formula in the cell recalculates to an invalid result. Use Circle Invalid Data to audit those cases.

Step by step: allow numbers only with a custom formula

1. Select the range to validate. Start the selection at the top-left cell so it becomes the active cell (here A1).
2. Go to Data > Data Tools > Data Validation and click Data Validation.

3. On the Settings tab set Allow to Custom.
4. In the Formula box type =ISNUMBER(A1). Write it for the active cell only; Excel adjusts it for every other cell in the selection.
5. Leave Ignore blank ticked if empty cells are acceptable, or untick it to force an entry.
6. Click OK.

7. Test the rule. Type 25 and it is accepted; type “abc” and the error alert appears.

The default alert is a Stop message. To replace it with your own wording, use the Error Alert tab, covered in the error alert lesson linked below.

Ready-to-use custom validation formulas

Each formula assumes the rule is applied to column A starting at A2 (A2 active), unless another cell is named. Adjust the references to your own layout.

Rule you want	Custom formula	What it enforces
Numbers only	=ISNUMBER(A2)	Rejects text; dates count as numbers
Text only	=ISTEXT(A2)	Rejects numbers and dates
No duplicates	=COUNTIF($A$2:$A$500,A2)=1	Each value may appear once in the range
Upper case only	=EXACT(A2,UPPER(A2))	Rejects any lower-case letter
Must start with INV	=LEFT(A2,3)="INV"	Invoice numbers with a fixed prefix
End date after start date (rule on C2)	=C2>=B2	Compares two cells in the same row
Positive whole number	=AND(ISNUMBER(A2),A2>0,A2=INT(A2))	Combines three tests
Weekdays only	=WEEKDAY(A2,2)<6	Rejects Saturday and Sunday
Column total within budget	=SUM($B$2:$B$50)<=$E$1	The whole column may not exceed E1
Fill in order	=A1<>""	Entry allowed only when the cell above is filled
At most two decimals	=A2=ROUND(A2,2)	Prices with pence but no fractions of a penny
Not today or the past	=A2>TODAY()	Future dates only

Wrap two or more tests in AND when all must be true, or in OR when any one is enough. Excel 365 users can also use LET inside the box to name a repeated calculation, but the older functions above work in every version.

Worked example: an expense claim sheet

A claims sheet has Date in column A, Category in B, Amount in C and a budget of 1,000 in cell F1. Three custom rules keep the claims clean.

Range	Formula	Result when tested
A2:A50	=AND(ISNUMBER(A2),A2<=TODAY())	Accepts 03-Sep-2026; rejects a future date and the text “Sep 3”
C2:C50	=AND(C2>0,C2=ROUND(C2,2))	Accepts 49.99; rejects 0, -5 and 12.345
C2:C50 (extended rule)	=AND(C2>0,C2=ROUND(C2,2),SUM($C$2:$C$50)<=$F$1)	Same as above, and accepts entries only until the column total would pass 1,000

To build the first rule: select A2:A50 with A2 active, open Data Validation, choose Custom, paste =AND(ISNUMBER(A2),A2<=TODAY()) and click OK. Type 03/09/2026 in A2 and it is accepted. Type 01/01/2030 and the Stop alert appears. Type the word September and it is rejected too, because text is not a number. The mixed reference in the budget rule matters: $C$2:$C$50 and $F$1 stay fixed for every cell, so the total is always the whole column.

Tips and common mistakes

• Test the formula in a spare cell first. The Data Validation dialog gives no error message for a broken formula; it just rejects everything.
• Select from the top-left. If the active cell is A10 and you write =ISNUMBER(A2), every cell tests a cell eight rows above it.
• Lock what must not move. Fixed limits and full ranges take $ signs; the cell being tested stays relative.
• Ignore blank still applies. With the box ticked, an empty entry passes even when the formula would return FALSE. Untick it to require a value.
• Dates and times are numbers. ISNUMBER accepts them, so add a range test such as A2>=DATE(2026,1,1) when you need a real date rule.
• Whole-column references are slow. COUNTIF($A:$A,A2) works but recalculates a million rows on every entry; a bounded range is faster.
• Other workbooks are off limits. A custom rule cannot reference a closed or external workbook. Use a named range on another sheet instead.

Errors and how to fix them

Symptom	Cause	Fix
Every entry is rejected	Formula returns text, an error or FALSE for the active cell	Copy the formula into a cell and check that it shows TRUE for a valid value
Rule works in the first cell only	Absolute reference on the tested cell, e.g. =ISNUMBER($A$2)	Make the tested cell relative: =ISNUMBER(A2)
Rule fires on the wrong row	Selection was not made from the top-left cell	Reselect the range starting at its first cell and re-enter the rule
Duplicates still get in	Values were pasted or filled, which bypasses validation	Run Data Validation > Circle Invalid Data to find them
“You may not use references to other worksheets” message	Direct sheet reference in Excel 2007 or earlier	Define a named range for the other-sheet cells and use the name
Blank cells are accepted although the formula says no	Ignore blank is ticked	Untick Ignore blank on the Settings tab

Practice exercise

1. In a new sheet, type ten employee IDs in A2:A11. Apply =COUNTIF($A$2:$A$11,A2)=1 and try to enter an ID that already exists.
2. Put a start date in B2 and apply a rule to C2 that only accepts a date on or after B2.
3. Apply =EXACT(D2,UPPER(D2)) to D2:D11 and test “abc”, “ABC” and “AbC”.
4. Put 500 in F1 and stop column E from totalling more than F1. Enter values until the rule blocks you.
5. Combine two tests with AND so column G accepts only whole numbers from 1 to 12.

Key takeaways

• Custom validation accepts an entry only when your formula returns TRUE for the active cell.
• Write the formula for the top-left cell of the selection; relative references shift for the rest.
• Use absolute references for fixed limits and full ranges, relative references for the cell under test.
• Combine conditions with AND and OR; check the formula in a spare cell before using it.
• Validation runs only on typed entries, so audit pasted data with Circle Invalid Data.

Related lessons

• Data Validation course hub
• Error alert: Stop, Warning and Information
• Circle invalid data
• Data validation formulas: prevent duplicates, enforce formats, named ranges
• COUNTIF function and conditional formatting with a formula
• Microsoft Support: Apply data validation to cells

Visit our YouTube channel for step-by-step video tutorials.

Frequently asked questions

Why does my custom validation formula block every entry?

The formula returns FALSE, text or an error for the active cell. Common causes are a typo in a function name, a reference to the wrong row, or a result such as “OK” instead of TRUE. Paste the formula into an empty cell next to a valid value; it must show TRUE. Fix it there, then paste it back into the Formula box.

Can I combine two conditions in one data validation rule?

Yes. Wrap the tests in AND when all must be true, for example =AND(ISNUMBER(A2),A2>0) for positive numbers, or in OR when any one is enough. There is no practical limit on nesting, but keep rules short enough to read, and test each part separately before combining them.

Can custom data validation stop duplicate entries?

Yes. Apply =COUNTIF($A$2:$A$500,A2)=1 to the range. Excel rejects a value that already appears. The rule only checks typed entries, so duplicates that were pasted in or existed before the rule are not caught; use Circle Invalid Data or Remove Duplicates for those.

Does a custom rule reference cells on another sheet?

In Excel 2010 and later you can type a direct reference such as =A2<=Limits!$B$1. Excel 2007 and earlier show an error, and the safe method in every version is to define a named range for the cells on the other sheet and use the name in the formula.

What is the difference between custom validation and conditional formatting?

Both use a TRUE or FALSE formula written for the active cell. Validation stops an invalid entry at the moment of typing; conditional formatting only colours a cell after the value is in. Use validation to prevent errors and conditional formatting to highlight results the validation cannot check, such as pasted data.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.