Data validation and drop-down lists
Stop bad data at the door with drop-down lists, number and date rules, helpful messages and custom formulas, and learn the limits of validation.
In this lesson you will
- Create a drop-down list
- Set number, date and custom rules
- Write helpful messages
- Know the limits of validation
About 15 minutes
Stopping bad data before it starts
Beatriz shares a sales sheet with five colleagues. Within a week the Region column has North, north, Nort and N. A PivotTable then shows four regions that should be one, and the monthly total is wrong. Instead of cleaning up afterwards, she can stop the mistakes at the door with data validation: rules that decide what a cell will accept. It is one of the most useful features for shared sheets and forms.
A drop-down list in five steps
- Select the cells that need the list, such as A2:A100.
- Go to Data, Data Validation.
- Under Allow, choose List.
- In Source, type the choices separated by commas (
North, South, East, West), or click the arrow and select a range of cells that holds them. - On the Error Alert tab, write a helpful message, then click OK.
A small arrow appears beside each cell; people pick instead of typing. Keeping the list in a range of cells is better than typing it inside the rule, because you can add a new region later by adding a cell. Make the list range a Table or a named range so it grows by itself.
Other kinds of rules
| Allow | Use it for |
|---|---|
| Whole number | Units, ages, quantities between limits |
| Decimal | Prices, percentages, weights |
| Date | A date after a start or within a range |
| Text length | Codes that must be exactly 6 characters |
| Custom | Any formula that returns TRUE for acceptable entries |
Three messages
- Input Message: a small tip that appears when the cell is selected, such as ‘Whole number from 1 to 500’.
- Error Alert: what the person sees when they type something not allowed. Choose Stop to refuse it, Warning to let them choose, or Information to simply tell them.
- Write messages in plain words that say what to do, not ‘Invalid entry’.
A real rule set
The program below builds a workbook with three rules: a list for Region, whole numbers from 1 to 500 for Units, and dates from 1 January 2026 on for the sale date. It saves the file, reloads it and prints the rules stored in it. The output is real:
import openpyxl
from openpyxl.worksheet.datavalidation import DataValidation
wb = openpyxl.Workbook(); ws = wb.active
ws["A1"], ws["B1"], ws["C1"] = "Region", "Units", "Sale date"
dv_list = DataValidation(type="list", formula1='"North,South,East,West"', allow_blank=False)
dv_list.errorTitle = "Pick from the list"; dv_list.error = "Choose North, South, East or West."
dv_num = DataValidation(type="whole", operator="between", formula1="1", formula2="500")
dv_num.promptTitle = "Units"; dv_num.prompt = "Whole number from 1 to 500"
dv_date = DataValidation(type="date", operator="greaterThanOrEqual", formula1="46023") # 1 Jan 2026
for dv, rng in [(dv_list, "A2:A100"), (dv_num, "B2:B100"), (dv_date, "C2:C100")]:
ws.add_data_validation(dv); dv.add(rng)
wb.save("rules.xlsx")
wb2 = openpyxl.load_workbook("rules.xlsx")
for dv in wb2.active.data_validations.dataValidation:
print(dv.type, "|", dv.sqref, "|", dv.operator or "-", "|", dv.formula1, dv.formula2 or "")
list | A2:A100 | - | "North,South,East,West"
whole | B2:B100 | between | 1 500
date | C2:C100 | greaterThanOrEqual | 46023
This shows the rules are saved with the file. The sandbox cannot click into a cell and type, so it did not test how Excel shows the drop-down or the alert on screen. Try it in your own copy.
Custom rules
Choose Custom and type a formula that must be TRUE. For example, to refuse duplicate codes in column A, select A2:A100 and use =
Dependent drop-downs, briefly
Sometimes the second list depends on the first: choose a country, then see only its cities. This uses named ranges and the INDIRECT function, and is a good next step once you are comfortable. Google Sheets and newer Excel also offer other approaches, so search for ‘dependent drop-down’ in your version when you are ready.
Limits you must know
- Validation does not stop pasting. Pasting over a cell replaces the rule too, and invalid values can sneak in.
- Existing data is not checked when you add a rule. Use Data Validation, Circle Invalid Data to find old entries that break it.
- Anyone who can unprotect the sheet can change the rules.
- A list from a range on another sheet works in modern Excel, and older versions need a named range.
Pair it with protection
Lock all cells except the input cells (Format Cells, Protection) and use Review, Protect Sheet. Then people can only type in the cells you chose, and the cells they type in only accept valid values. For shared data entry this combination is the closest thing to a form without writing code.
Practice
Build a simple order-entry sheet with a Product drop-down, a Quantity rule (whole numbers 1 to 100) and a Delivery date rule (not before today’s date). Add a helpful input message to each. Try typing a wrong value in each one and read the alert you wrote.
For a date no earlier than today, use Allow: Date, Data: greater than or equal to, Start date: =TODAY(). If the drop-down arrow does not show, make sure the In-cell dropdown box is ticked in the rule.
Assuming validation protects the sheet completely. Pasted values bypass it, so check key columns with a formula or Circle Invalid Data before reporting.
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
What does a drop-down list prevent?
- Saving
- Printing
- Sorting
- Typing variations such as north and Nort
People pick from fixed choices.
Which validation type suits ‘between 1 and 500’?
- Whole number
- List
- Date
- Custom only
Choose the between rule.
What does Circle Invalid Data do?
- Deletes them
- Hides them
- Marks existing entries that break the rules
- Sorts them
Rules are not applied to old data automatically.
Which action can bypass a validation rule?
- Saving the file
- Typing a valid value
- Choosing from the list
- Pasting a value into the cell
Pasting replaces the cell’s rule.
Why keep list choices in cells instead of typing them in the rule?
- It looks nicer
- You can add choices later
- Excel requires it
- It protects the sheet
Edit the cells and the list updates.
Summary
- Validation stops bad entries before they happen.
- Drop-down lists keep names consistent.
- Write clear input and error messages.
- Pasting bypasses rules; check with Circle Invalid Data.
Questions
Can a drop-down allow typing as well?
Yes. Set the Error Alert style to Warning or Information and people can type something not in the list after a prompt.
How do I remove a validation rule?
Select the cells, open Data Validation and click Clear All.
Do drop-down lists work in Google Sheets?
Yes. Use Data, Data validation and choose a dropdown, with the same ideas.
