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:

Creating a drop-down list

On the Settings tab, set Allow to List. You then have two choices for the Source box:

  1. Type the items directly, separated by commas: Dubai,Sharjah,Ajman,Abu Dhabi. This is quick for short lists that rarely change.
  2. 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:

Restricting numbers, dates and text length

Other options in the Allow box cover most everyday rules:

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.

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:

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

Summary

Back to: all Excel tutorials.