Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the Validation error
Excel & Google SheetsWhat Validation means
A custom data validation formula must return TRUE to allow the entry and FALSE to reject it. When the formula is misconfigured — wrong reference, unsupported function, or incorrect logic — it either blocks all entries, allows all entries, or shows no error indicator at all while silently accepting bad data.
Common causes
- The formula references an absolute cell ($A$1) instead of the relative cell that shifts per row, so every row validates against the same fixed cell.
- The formula returns a number, text, or #NAME? error instead of TRUE/FALSE, causing Excel to treat the result inconsistently.
- Functions that require array evaluation (COUNTIFS over a non-adjacent range, UNIQUE) are not supported in data validation in all versions.
- The validation was set on a range but the formula anchor does not match the top row of that range, inverting the logic for all rows below.
- An INDIRECT or volatile function is used and it resolves to an error on load, which Excel interprets as FALSE and blocks all input.
Example fix
Broken
=COUNTIF($ValidList$A$1:$A$20,A1)>0Fixed
=COUNTIF(ValidList!$A$1:$A$20,A1)>0Fixed the malformed range reference so COUNTIF correctly reads the named validation list from its sheet, allowing the formula to return TRUE or FALSE.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1Write the formula as if you are typing it in the first cell of the validated range. Use relative row references (A1, not $A$1) so it shifts per row.
- 2Test the formula in a regular cell first. If it does not return TRUE or FALSE cleanly, fix it before adding it to validation.
- 3Use ISNUMBER, ISTEXT, or COUNTIF-based formulas for list membership checks: =COUNTIF(ValidList,A1)>0.
- 4For date range validation use: =AND(A1>=DATE(2024,1,1),A1<=DATE(2024,12,31)).
- 5In Google Sheets, avoid referencing cells on other sheets with INDIRECT; use the direct range reference SheetName!A1:A100 instead.
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every Validation and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
My data validation formula works in a cell but fails when added to the validation dialog. Why?
Validation formulas do not support all functions (e.g., some array functions) and evaluate differently. Also check that the formula is anchored to the first cell of the validated range, not the cell you tested it in.
How do I validate that a cell contains a value from a dynamic list that changes in size?
Define a Named Range pointing to the full column (e.g., ValidItems = Sheet2!$A:$A) and use =COUNTIF(ValidItems,A1)>0 as the validation formula.
Can data validation prevent pasted values from bypassing the rule?
Paste-special (values only) can bypass validation in Excel. In the Data tab > Data Validation, check the 'Error Alert' tab is enabled, and educate users to paste with Ctrl+Shift+V or use protected sheets.