Excel 用自定義公式做資料驗證

Tested in: The TRUE/FALSE and COUNTIF checks below were evaluated in Google Sheets on 12 November 2026. Excel 365 menu names are the current desktop labels; I did not click through Data Validation in Excel this session.

A plain list is covered in How to Add a Drop-Down List in Excel. This page is about the Custom option.

Write the formula for the first input cell

If you selected B2:B20, the formula is evaluated as if it were entered in B2, then filled down. Relative references should point at B2, not at B1 or at a locked $B$2 unless every row must test the same cell.

A number between 1 and 10

=AND(B2>=1,B2<=10)

In the test workbook on 12 November 2026, =AND(5>=1,5<=10) was TRUE, and the same test for 0 and for 11 was FALSE.

A real number, not text

=ISNUMBER(B2)

=ISNUMBER(5) was TRUE. =ISNUMBER("n/a") was FALSE. Numbers stored as text fail this test and should be converted first; see convert text to numbers.

A value that already appears in a list (without using the List type):

=COUNTIF(Lists!A2:A20,B2)=1

COUNTIF of "East" against {East;West;North} returned 1, and "South" returned 0. A 0 is rejected. This is useful when you do not want a drop-down but still want the cell limited to known items. For an actual drop-down pointing at another sheet, use a List rule as in list from another sheet.

No duplicates in the input column

=COUNTIF($B$2:$B$20,B2)=1

The range is locked; the cell being tested is not. The first time a value appears, the count is 1 and the entry is allowed. A second paste of the same value becomes 2 and is rejected.

In Google Sheets the custom formula goes in the Custom formula is criterion of Data validation, with the same references.

Test it on the sheet before you open the dialog

Put the formula in an empty column on row 2, fill down, and confirm you see TRUE where an entry should be allowed and FALSE where it should be blocked. If the helper column is already wrong, the validation rule will be wrong too.

The formula must start with =. A result that is not TRUE or FALSE — a #VALUE!, a number, or a blank — is treated as a failed check, which feels like a rule that rejects everything.

Turn Ignore blank off if an empty cell should be rejected. Leave it on if the input is optional. Pasted values are also checked, which is the usual way a rule “suddenly” fires after a copy from another sheet.

If the arrow is missing, you are probably on a Custom rule, not a List rule. That is expected. If a List rule is the one that is misbehaving, use drop-down list not working instead of changing it to Custom.