ExcelTool.io

How to Use Data Validation in Excel

Data validation controls what people can type into a cell - dropdown lists, number and date ranges, and custom rules. Here is how to set it up.

Use the online tool

The fastest option is available in your browser with no upload to a server.

Build a Validation Rule

Steps

  1. Select the cells to validate - Highlight the range where the rule should apply - for example a column where users enter a status or quantity.
  2. Open Data > Data Validation - On the Data tab, click Data Validation to open the Settings dialog.
  3. Choose an Allow type - Pick List for a dropdown, Whole number or Decimal for number ranges, Date for date limits, Text length for size limits, or Custom for a formula.
  4. Enter the criteria - For a dropdown, type comma-separated options or reference a range like =Sheet2!$A$1:$A$10. For ranges, set the minimum and maximum.
  5. Add an input or error message - Use the Input Message tab to show a hint and the Error Alert tab to block or warn on invalid entries, then click OK.

Before You Start

  • Reference a range or named range for dropdown sources so the list updates when you add options.
  • Enable 'Ignore blank' if empty cells should be allowed.
  • Custom-formula rules must return TRUE for valid entries, e.g. =A1>0.

Alternative Methods

  • Generate the rule online - Use the Data Validation helper to configure a dropdown, number, date, text-length, or custom rule and copy the exact source string and steps into Excel.
  • Dependent dropdown lists - Combine named ranges with INDIRECT to make one dropdown's options depend on another cell's selection.

Frequently Asked Questions