Drop-down lists and data validation
Stop bad data at the door: give cells a drop-down list, feed a list from a range, and limit a number to a sensible range. Beginner · 6 min · Data validation, Drop-down list, List from a range, Number limits
The functions in this track find problems after they are in the sheet. Data validation keeps them out. A rule on a cell says what may be typed there, and anything else is refused.
The most used rule is a list: the cell gets a drop-down arrow, and people pick a value instead of typing one. Nobody can write Shipped, shipped and Shiped in the same column, so counts and filters stay right.
A list can be typed into the rule (fine for Yes and No) or read from a range of the sheet, which is better when the choices will change: edit the range and every drop-down follows.
In Excel, it's Data → Data Validation. Here, it's Data validation in the toolbar. To open a cell's list, click its arrow or press Alt+Down.
Steps
The Paid column should only ever say Yes or No. Select
B2:B6, click Data validation, choose List under Allow, type Yes, No in Source, and click Apply.Use it. Click
B2, open its list with the arrow (or Alt+Down), and choose Yes.The statuses are listed in
F2:F4. GiveC2:C6a list that reads them: select C2:C6 and set Allow to List with the Source=F2:F4.A list from a range stays up to date. Change
F4to Returned, then open the list inC2to see it.Rules are not only lists. An order holds between 1 and 20 boxes. Select
D2:D6, set Allow to Whole number, Minimum to 1 and Maximum to 20, and click Apply.Enter 12 in
D2.
Remember
- Data validation puts a rule on cells: what may be typed there.
- List gives a drop-down. Type the items (
Yes, No) or point at a range (=F2:F4). - A list from a range follows the range when it changes.
- Whole number and Decimal rules limit a number to a minimum and maximum.
- A rule checks typing. Pasting over a cell replaces the rule.
Previous: Freeze panes: keep the headings in view · Next: Spotting problems with IS functions