Data Validation Lists and Their Limits in Excel Sheets
How Excel data validation lists behave and where they stop: the 255 character typed list, range sources, long lists, dependent dropdowns and pasting.
The dropdown arrow in a cell is the most reassuring control in a spreadsheet and the one that people trust furthest. It prevents "Actv" and "ACTIVE" and "active " from ever appearing, as long as the list is short, the user types rather than pastes and nobody reaches the 255-character wall in the dialog. This article lays out how validation lists behave, where they stop and how to test the limits yourself. It is the practical companion to controlling what goes into a spreadsheet, and the lists here feed the checks in checking an incoming workbook against a template by code.
The three ways to supply a list
In Data, Data Validation, choose Allow: List, and give the Source one of three ways.
A typed list. Values separated by commas: Open,In review,Closed. Simple, self-contained and invisible to anyone not looking at the validation dialog. In a regional setting that uses a semicolon as the list separator, the separator in the dialog is the semicolon; written from code, the file stores it with commas.
A range. A reference to cells: =$A$2:$A$20 or =Lists!$A$2:$A$20. The dropdown shows the cells' contents. Change the cells and the dropdown changes.
A named range. =StatusList, defined in Name Manager as a range, a table column or a formula. The most maintainable: the name is documented, reused and can be redefined in one place.
Written from code, the three forms look like this in openpyxl:
from openpyxl.worksheet.datavalidation import DataValidation
typed = DataValidation(type="list", formula1='"Open,In review,Closed"', allow_blank=False)
ranged = DataValidation(type="list", formula1="=Lists!$A$2:$A$20")
named = DataValidation(type="list", formula1="=StatusList")The typed form needs double quotes around the whole comma-separated string. Add each validation to the worksheet and call .add("C2:C1000") for the cells it covers.
The 255 character limit
The typed list has a hard limit: the total length of the Source string, including commas, cannot exceed 255 characters. Excel refuses a longer one with an error in the dialog, and a file written by a library with a longer string can open with a repair prompt or a validation that quietly fails. A list of 40 country names does not fit. A list of ten statuses of ten characters each does (about 109 characters).
The limit pushes you to a range or a named range, which is better practice anyway. If you write the file from code and your typed list approaches the limit, switch to a range. An easy check in the generator: assert len(",".join(values)) <= 255.
Note that a typed list cannot contain a comma in a value, because the comma is the separator. A value like Acme, Inc. must come from a range.
How long can a range list be?
A range source has no small cap. Lists of thousands of rows work. The practical limits are on people. The dropdown shows about eight entries at a time, so a list of 300 means scrolling, and a user who does not know the first letter cannot find a value. Recent Excel versions support type-ahead filtering in dropdowns for validation lists in Microsoft 365, where typing narrows the list. Older versions only jump to the first match as you type. Check which your users have.
Past a few hundred values, a dropdown is the wrong interface. Alternatives:
Split into two dropdowns, such as category then item, using a dependent list.
A lookup by code: the user types a code and a formula shows the name beside it, with validation checking the code exists in the master list (
=COUNTIF(Lists!A:A, A2)>0as a custom rule).A form or application with a search box over the master data.
Performance is rarely the issue. Each cell with validation holds a reference to the list, not a copy, so a thousand cells pointing at one list cost almost nothing in file size.
Where the list lives
Put lists on a dedicated sheet (Lists), one list per column with a header, and define a name for each. Keep the sheet visible; a hidden list sheet is a surprise to the next maintainer, and visible lists invite fixes. Put the owner's name at the top. Protect the sheet from casual edits and let one role change it.
Cross-sheet references in validation work in current Excel versions. Very old versions (2007 and earlier) required a named range for a list on another sheet, and named ranges still work everywhere. Use names and the question disappears.
For lists that grow, base the name on a table column (=tblStatus[Status] as the source via a name) or a dynamic range, so adding a row to the list adds an option:
StatusList =Lists!$A$2:INDEX(Lists!$A:$A, COUNTA(Lists!$A:$A))Dependent dropdowns
A second dropdown whose choices depend on the first (location by site) has two common implementations.
The INDIRECT approach: define one named range per site (North, South) holding its locations, then set the second dropdown's source to =INDIRECT($A2). It works in every version. The limits: the first value must match a name exactly, so values with spaces or punctuation need cleaned names (North_West) and a mapping; names are fragile when sites change; and INDIRECT is volatile.
The dynamic array approach in Microsoft 365: put a FILTER in a helper cell, =FILTER(Locations[Location], Locations[Site]=$A2), and set the validation source to the spill reference, =$H$2#. Because each row needs its own spill, this fits a single entry area better than a long table, or you build the helper per row.
Either works for two levels. For three or more levels the maintenance cost rises quickly. Consider whether the user should pick one thing from a list of codes that already encodes the hierarchy.
What validation does not check
The most important limits are not about lists. They are about what validation ignores.
| Case | Checked? |
|---|---|
| Typing a value | Yes |
| Selecting from the dropdown | Yes |
| Pasting from another cell or workbook | No, and validation may be overwritten |
| Dragging the fill handle | No |
| A value set by a formula | No |
| A value that was there before validation was added | No, until you circle invalid data |
| Filling by Flash Fill | No |
| A macro or script writing a value | No |
Data, Data Validation, Circle Invalid Data highlights existing cells that break the rules, and is worth running before sending a file out. A Warning or Information alert style lets the user override, so only Stop enforces. Lists are case-insensitive for typed entries: open is accepted where the list has Open, and the cell keeps the case as typed, so downstream data has mixed case. Normalize in the loader, or have the dropdown be the only route by hiding the typed entry (which Excel does not support) and training users to pick.
Measuring your own limits
Repeat these tests with your list on your Excel versions before you rely on it.
Length test. Create a typed list and add values until Excel refuses. Note the character count.
Size test. Build range lists of 100, 1,000 and 10,000 entries. Open the dropdown and time how long it takes to find an entry near the end.
Paste test. Copy a cell from another workbook over a validated cell and check whether the rule survives.
Dependent test. Pick a value in the first dropdown, change it and check the second cell: it keeps the old, now-invalid value. Validation does not clear it.
Version test. Open the file in the oldest Excel version, Excel for the web and Google Sheets, and note what changes.
The dependent case in test four is a classic bug. When the parent changes, the child keeps a stale value that is no longer in the allowed list. Add a conditional format on the child (=COUNTIF(INDIRECT($A2), B2)=0) that turns the cell red, and an import check that verifies the pair.
Validation in Google Sheets
Sheets offers dropdowns from a range or a typed list, with chips and colors, and "reject input" or "show warning" modes. The same bypasses apply: pasting skips validation. Behavior differs in details, so check the cases above in Sheets if the file lives there (differences between Sheets and Excel formulas).
When a validation list is not enough
A validation list is not enough when the data matters. It lowers the error rate and cannot guarantee anything, because of the bypasses above. Put the real check in the loader, which reads every value and compares it with the master list. It is also not enough when the list is long, changes daily or depends on other fields in ways that are hard to express; use a form over a database. And it is not enough when users need to add values themselves: a dropdown that blocks new entries trains people to type around it, so provide a route for requests. Microsoft's page on creating a drop-down list covers the dialog, and the openpyxl validation guide covers writing rules from code.
