Data Validation & Dropdown Lists in Excel: Complete Guide
SheetHub4 min
You send a spreadsheet to your team. Someone types "East" in the Region column. Someone else types "east." Another person types "E."
Now your pivot table shows three different values for the same region.
Data validation solves this. It controls what people can enter into a cell — a dropdown list, a number range, a date limit. If they try to enter something invalid, Excel stops them.
Data validation is not about restrictions. It is about consistency. A dropdown list prevents typos. A date range prevents impossible entries. An error message tells the user exactly what to do instead of leaving them guessing.
Step 1: Select the cell(s) where you want the dropdown.
Step 2: Go to Data → Data Validation → Data Validation.
Step 3: Under Allow, select List.
Step 4: In Source, type your options separated by commas:
Or select a range on the sheet:
Step 5: Click OK. A dropdown arrow appears in the selected cells.
If your list changes over time, use an Excel Table:
A dropdown that changes based on another cell's value:
Category selected: "Fruits" → second dropdown shows: Apple, Banana, Orange
Category selected: "Vegetables" → second dropdown shows: Carrot, Broccoli, Spinach
Use
Where A1 contains the category name, and you have named ranges matching each category (Fruits = Apple, Banana, Orange).
Input Message: Shows a tooltip when the cell is selected:
Error Alert: Shows when someone enters invalid data:
Use Custom validation with formulas. Highlight duplicates, prevent blank entries, or validate based on another column:
Copy validation to other cells. Copy a cell with validation, then Paste Special → Validation.
Circle invalid data. Go to Data → Data Validation → Circle Invalid Data to highlight existing errors.
Dropdown not showing. Check that the cell is not in a merged cell group. Merged cells often break dropdowns.
List source issues. If your source range includes blank cells, the dropdown shows blanks. Use a dynamic range or Table.
Validation not working on paste. Data validation is removed when someone pastes data into the cell. Use VBA or Protect Sheet to prevent this.
Can I create a dropdown in Google Sheets? Yes. Data → Data validation → Add rule → Dropdown.
How do I remove data validation? Select the cells → Data → Data Validation → Clear All.
Can I make a dropdown that searches as I type? Not with basic data validation. Use a ComboBox ActiveX control or Power Apps for searchable dropdowns.
Why This Matters
Creating a Dropdown List
East, West, North, South
=$F$1:$F$4Dynamic Dropdowns with Tables
- Convert your list to a Table (Ctrl+T)
- In Source, reference the Table column:
=Table1[Region] - As you add items to the Table, the dropdown updates automatically
Dependent Dropdowns
INDIRECT to link them:
=INDIRECT(A1)Other Validation Types
| Type | Use Case | Example |
|---|---|---|
| Whole Number | Age, quantity, count | Between 1 and 100 |
| Decimal | Price, percentage | Between 0 and 1 |
| Date | Order date, birth date | After 1/1/2024 |
| Time | Shift start, appointment | Between 9:00 AM and 5:00 PM |
| Text Length | ZIP code, phone number | Exactly 5 characters |
| Custom | Complex conditions | =ISTEXT(A1) |
Input Messages & Error Alerts
Please select a region from the dropdown list.
Style: Stop
Title: Invalid Region
Message: Please select East, West, North, or South.
Pro Tips
=COUNTIF($A$2:$A$100, A2)=1Common Mistakes
Related Functions
- IF, Nested IF, IFS — Build logic around validated data
- VLOOKUP vs XLOOKUP — Look up values from validated dropdown selections
FAQ
Topics
Topics in this article
Explore related topics and continue reading similar content.
Share this article
Discussion
Preparing the comments area...