Data Validation in Excel: Drop-Down Lists and Input Rules
Last updated: November 2026 · Works in Excel for Microsoft 365, Excel 2021, Excel 2019 and Excel 2016
Most spreadsheet problems start with typing. One person writes "Dubai", another writes "DXB" and a third writes "Dubia", and suddenly your totals by city are wrong. Data validation stops bad entries at the source. It lets you offer a drop-down list of allowed values, accept only whole numbers within a range, block dates in the past, and show a helpful message when someone selects the cell. This guide shows how to set each rule up and how to find invalid entries that were already in the sheet.
Opening the Data Validation dialog
Select the cells you want to control, for example C2:C500. Go to Data > Data Tools > Data Validation (the keyboard shortcut is Alt, A, V, V). The dialog has three tabs:
- Settings holds the rule itself.
- Input Message shows a tip when the cell is selected.
- Error Alert controls what happens when someone types something that breaks the rule.
Creating a drop-down list
On the Settings tab, set Allow to List. You then have two choices for the Source box:
- Type the items directly, separated by commas:
Dubai,Sharjah,Ajman,Abu Dhabi. This is quick for short lists that rarely change. - Point to a range of cells, for example
=Lists!$A$2:$A$10. This is better for longer lists or lists that change, because you edit the cells rather than the rule.
Keep In-cell dropdown ticked and click OK. A small arrow appears beside each selected cell. Users can pick from the list or type an entry, and anything not on the list is rejected. Pressing Alt + Down Arrow opens the list from the keyboard.
A tidy approach is to keep all your lists on a separate sheet named Lists and hide it once the workbook is finished. That keeps the main sheet clean and stops people from editing the options by accident.
Drop-down lists that grow automatically
If your list of options lives in a fixed range like A2:A10, a new item typed in A11 will not appear in the drop-down. There are two good fixes:
- Use an Excel Table. Convert the list to a table with Ctrl + T, then give the column a name through Formulas > Define Name, referring to something like
=tblBranches[Branch]. Use=BranchList(your name) as the validation source. When you add a row to the table, the list grows. See Excel Tables for more. - Use a spill range (Microsoft 365). If a formula such as
=SORT(UNIQUE(Sales!B2:B500))in Lists!A2 produces the list, enter=Lists!$A$2#as the source. The # sign refers to the whole spilled result, however long it becomes.
Restricting numbers, dates and text length
Other options in the Allow box cover most everyday rules:
- Whole number between 1 and 100, for a quantity column.
- Decimal greater than or equal to 0, so prices can never be negative.
- Date greater than or equal to
=TODAY(), so delivery dates cannot be in the past. - Date between two cells, for example the start and end of a financial year stored in H1 and H2.
- Text length equal to 15, to make sure a product code, invoice number or account reference always has the right number of characters.
Rules can refer to other cells, so you can make a minimum order quantity depend on a value elsewhere in the workbook.
Custom rules with formulas
Choose Custom to write any rule as a formula that returns TRUE for valid entries. Write the formula for the first selected cell; Excel adjusts it for the others.
- No duplicates in a column: select A2:A500 and use
=COUNTIF($A$2:$A$500,A2)=1. - Must start with INV-:
=LEFT(A2,4)="INV-". - Weekdays only:
=WEEKDAY(B2,2)<6blocks Saturdays and Sundays. - Only allow a discount if a manager is named: on the discount cell,
=OR(D2=0,E2<>"").
Custom rules use the same logic as the IF function and the conditions in COUNTIFS, so the skills carry over.
Input messages and error alerts
On the Input Message tab, add a short title and message such as "Enter the delivery date. It cannot be earlier than today." The note appears whenever the cell is selected, which is far more helpful than a rejection after the fact.
On the Error Alert tab, choose a style:
- Stop blocks the entry completely. Use it for codes and categories that must be exact.
- Warning asks "Continue?" and lets the user choose. Use it for unusual but possible values, such as a very large quantity.
- Information simply informs and accepts the entry.
Write your own error text that explains what is allowed, for example "Choose a branch from the list. To add a new branch, ask the accounts team."
Finding invalid data that is already there
Validation only checks new typing. Data that existed before the rule, or that was pasted in, can still break it. To find those cells, go to Data > Data Validation > Circle Invalid Data. Excel draws red circles around every cell that breaks its rule. Fix them, then choose Clear Validation Circles.
To see which cells have validation at all, press F5, click Special and choose Data validation.
Common mistakes to avoid
- Pasting over validated cells. Normal paste replaces the validation rule as well as the value. Use Paste Special > Values instead.
- Relative references in the source. A source of
=A2:A10shifts for every cell; use=$A$2:$A$10. - Spaces in typed lists. "Dubai, Sharjah" adds a leading space to Sharjah. Type the items without spaces after the commas.
- Relying on validation for security. It prevents accidents, not determined users. Protect the sheet if the rules must not be removed.
Summary
- Data validation controls what can be typed into a cell.
- List rules create drop-downs; point them at a table or spill range so they grow.
- Number, date and text-length rules catch most typing errors.
- Custom formulas allow almost any rule, including no duplicates.
- Use Circle Invalid Data to find entries that were there before the rule.
Back to: all Excel tutorials.