Course / Clean & Check Data

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

  1. 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.

  2. Use it. Click B2, open its list with the arrow (or Alt+Down), and choose Yes.

  3. The statuses are listed in F2:F4. Give C2:C6 a list that reads them: select C2:C6 and set Allow to List with the Source =F2:F4.

  4. A list from a range stays up to date. Change F4 to Returned, then open the list in C2 to see it.

  5. 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.

  6. Enter 12 in D2.

Remember

Previous: Freeze panes: keep the headings in view · Next: Spotting problems with IS functions