Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to use a formula in data validation in Excel and Google Sheets
Excel & Google Sheets
=AND(B2>=10, B2<=20)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Product ID | Quantity |
| 101 | 15 |
| 102 | 25 |
| 103 | 5 |
=AND(B2>=10, B2<=20)→TRUE
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
Step by step
- 1Select the cell or range where you want to apply the validation rule.
- 2Go to the Data tab and click on Data Validation.
- 3In the Criteria tab, choose 'Custom formula is'.
- 4Enter your formula that references the selected cell(s) and returns TRUE for valid entries.
- 5Click OK to apply the rule.
Tips
- Ensure your formula returns TRUE for valid entries and FALSE for invalid ones.
- Test your formula with various inputs to confirm it behaves as expected.
- Remember that Excel and Google Sheets might have slight differences in function syntax, especially for more complex formulas.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Can I use named ranges in my validation formula?
Yes, both Excel and Google Sheets support using named ranges in data validation formulas.
What happens if the formula returns an error?
If the formula returns an error, the validation will fail, and the user will not be able to enter data unless the formula is corrected.
Is there a limit to the complexity of the formula I can use?
While there is no strict limit, overly complex formulas may slow down your spreadsheet performance. It's best to keep them as simple as possible while achieving your validation goals.