Post actualizado el día octubre 24, 2026 by DeiviSanzPlay
A dropdown list in Excel is a data validation that restricts what the user can type into a cell. Instead of typing freely, the user chooses from a menu. It sounds simple, but 90% of the problems with dropdown lists appear afterward: the list that doesn’t update, the range that shifts, or the annoying warning that Excel is blocking the entry.
The quick way: a list typed by hand in the cell itself
For a short, fixed list (for example, Yes/No/Pending) you don’t need another sheet. Select the cell, go to Data → Data Validation → Allow: List and type the items separated by semicolons: Yes;No;Pending. Depending on your regional settings the separator will be the comma, so if Excel rejects the formula, try the other one.
This option is perfect for menus that will never change. If you add a new item, you’ll have to go back and edit the validation in every cell that uses it.
The scalable way: a hidden sheet with the catalog
When the list has 10, 50, or 200 values, write them on a separate sheet (you can call it Lists and hide it). Then, in Data Validation, select the range with the mouse or type its reference:
- Create the Lists sheet and put the values in column A, from A1 downward.
- Select A1:A200 and convert it into a Table (Ctrl+T). This is key: by converting the range into a table, any value you add at the end automatically enters the dropdown list.
- On the worksheet, select the target cells and apply Validation → List → =Table1[Column1].
That table detail is what separates a list that works for years from one that breaks on the first update. Many tutorials explain the fixed range $A$1:$A$20, and that forces you to edit the validation every time the catalog grows.
Three mistakes you’ll see as soon as you start
1. The list range is on another sheet and Excel won’t accept it
Modern versions of Excel allow references to other sheets in validation, but if you share the file with someone using an old version or open it in LibreOffice, the validation may fail. The most portable solution is to define a name in the Name Manager (Formulas → Name Manager) that points to the range and use that name in the validation. Names do travel well between versions.
2. The list depends on another cell and doesn’t recalculate
Cascading lists (province → municipality) require formulas with INDIRECT or OFFSET. INDIRECT is fragile: if you rename the sheet or the range, the validation breaks without warning. And it doesn’t work if the workbook is closed in another file. For large catalogs it’s better to use VLOOKUP or FILTER with dynamic ranges, although FILTER is only available in Excel 365 and Excel 2021 onward.
3. The user pastes data and bypasses the validation
Here’s a limitation worth knowing from the start: data validation does not prevent pasting disallowed values. If someone copies a cell with a value outside the list and pastes it on top, Excel accepts it. Validation only acts on manual entry. If you really need to block it, you’ll have to resort to macros or protecting the sheet.
When NOT to use a dropdown list
If the field accepts dozens of possible values and the user knows them, a dropdown menu is slower than typing. It’s also a bad idea in columns where the value changes often for external reasons (prices, dates) or in forms that will be printed, because the dropdown doesn’t show on paper.
For lists of more than 300 items, the menu becomes awkward to scroll through; in that case, a combo box with search or a validation with approximate matching works better.
A maintenance trick
If you want to know which cells have validation on a large sheet, use Home → Find & Select → Data Validation → All. Excel selects all cells with rules and you can check whether any points to an outdated range before sharing the file. According to Microsoft’s official documentation, validation is saved in the workbook itself, so when you copy cells between files the rule travels with them (and sometimes breaks the destination’s list).
Practical summary to get started today
- Short, fixed list: type the values separated by semicolons in the validation itself.
- List that grows: table on another sheet and reference =Table1[Column].
- List across versions: define a name and use it in the validation.
- Remember that pasting values bypasses validation and that INDIRECT breaks when you rename.
With these four ideas you cover 95% of real-world dropdown list cases in Excel without relying on macros or add-ins.
