Excel Drop Down List & Data Validation Helper
Create drop down lists, number ranges, and custom validation rules for Excel. Get step-by-step instructions to apply in your spreadsheet.
Validation Type
Settings
Error Message (Optional)
Validation Settings
Yes, No, MaybeStep-by-Step Instructions
1. Select the cells you want to validate
2. Go to Data tab > Data Validation
3. In the Settings tab:
- Allow: "List"
- Source: Yes, No, Maybe
4. In the Error Alert tab:
- Title: Invalid Entry
- Error message: Please select a valid option from the list.
5. Click OK
Pro Tips
- • For dropdown lists, you can also reference a range like =Sheet2!$A$1:$A$10
- • Use named ranges for cleaner formulas: =Products
- • Enable "Ignore blank" if empty cells should be allowed
- • Use the Input Message tab to show hints when cells are selected
Excel data validation controls what people can type into a cell - dropdown lists, number and date ranges, text-length limits, and custom-formula rules. Configure the rule above and this helper gives you the exact source value and the Data > Data Validation steps to apply it. For dropdowns that must grow over time, point the List source at a named range instead of typing fixed comma-separated values; custom validation formulas must return TRUE for an accepted entry.
How to Use Data Validation
- Choose validation type - Select dropdown list, number range, date range, text length, or custom formula.
- Configure settings - Enter your validation criteria.
- Copy instructions - Follow the step-by-step guide in Excel.
- Apply to cells - Select your cells and apply the validation.
How to Create a Drop-Down List in Excel
Drop-down lists are built with Data Validation. The same dialog also limits entries to whole numbers, dates in a range, a maximum text length, or anything a formula can test. The generator above writes the rule into a workbook for you; this is how to do it by hand.
Method 1: A drop-down from a typed list
Best for: A short list that rarely changes, such as Yes and No.
- Select the cells that should get the drop-down.
- Go to Data > Data Tools > Data Validation.
- On the Settings tab, set Allow to List.
- In Source, type the items separated by commas:
Open,In progress,Done. - Keep In-cell dropdown ticked and click OK.
Method 2: A drop-down from a range that can grow
Best for: Longer lists, or lists other people maintain.
- Put the items in a column, select them and press Ctrl+T to make them a Table, so new items are included automatically.
- Select the target cells and open Data > Data Tools > Data Validation, Allow List.
- In Source, select the item cells in the table (not the header). Excel writes a range such as
=$E$2:$E$9and extends it as rows are added to the table. - In Microsoft 365 you can also point Source at a spilled formula, for example
=$G$2#next to=SORT(UNIQUE(E2:E200)), for a sorted list without repeats.
Method 3: Other rules - numbers, dates, text and formulas
Best for: Stopping bad data rather than offering choices.
- Allow Whole number or Decimal with between to limit a quantity, for example 1 to 100.
- Allow Date with greater than or equal to and
=TODAY()to block past dates. - Allow Text length, less than or equal to 10, for fixed-length codes.
- Allow Custom with
=COUNTIF($A:$A,A2)=1to block duplicate entries in a column. - Use the Error Alert tab to set the message, and choose Warning instead of Stop if people may override it.
In Google Sheets
Select the cells, choose Data > Data validation > Add rule, then pick Dropdown or Dropdown (from a range).
Common mistakes
- Existing data is not checked. A rule only applies to new entries. Use Data > Data Validation > Circle Invalid Data to find values that already break it.
- Pasting removes the rule. Pasting over a validated cell replaces its validation too. Use Paste Special > Values to keep it.
- Finding and clearing rules: Home > Find & Select > Data Validation selects every validated cell; Clear All in the dialog removes the rule.
Common Use Cases
- Dropdown lists - Force users to select from predefined options
- Number ranges - Ensure quantities or percentages are within valid ranges
- Date ranges - Restrict dates to a specific period
- Text length - Limit input to a certain number of characters
- Custom formulas - Create complex validation rules
Frequently Asked Questions
Data validation restricts what users can enter in cells. You can create dropdown lists, number ranges, date ranges, and custom rules to ensure data integrity.
Yes, our data validation helper is completely free. No hidden fees, no premium tiers, and no signup required.
Select your cells, go to Data > Data Validation, choose the validation type, and enter the criteria. Our tool generates the exact settings you need.
Yes! Select 'Dropdown List' and enter your options. You can use comma-separated values or reference a cell range.
Choose 'Dropdown List' above, enter your options, then in Excel select the cells, go to Data > Data Validation, set Allow to 'List', and paste your source. Our helper writes the exact source string for you.
Yes. Use the Number Range or Date Range type to limit entries to a minimum and maximum value, then follow the generated Data Validation steps in Excel.